Session Variable question

Hi everyone,

I am trying to extract data out of a SQL Server database (data looks like this:http://svrxblcyadnet02/crimp/CRIMP_Report_Frame.asp?) and plug it into a session variable. Below is the code I've come up with so far. My question is this: How do you get the data from the Dataset to the session variable? Thanks

privatevoid GetData()

{

string ConnectionString = "server=MyServer;uid=Sphynx;pwd=letmein;database=MyDatabase";

string CommandText = "select LOOKUP_DS from CRIMP_COMMON_LOOKUP where LOOKUP_NM = 'CRIMP_strName'";

SqlConnection myConnection =new SqlConnection(ConnectionString);

SqlDataAdapter myCommand =new SqlDataAdapter(CommandText, myConnection);

DataSet ds =new DataSet();

myCommand.Fill(ds);

Session["varString"] = "??"; //? What goes here?

myConnection.Close();

}

Ds.Tables(0).Rows(0)("ordnum")

Session can hold any object. You can store your dataset this way!

Session["myData"] = ds;

When you try to access the data, you need to do this

if (Session["myData"] != null)
DataSet ds = (DataSet) Session["myData"];

If you want store the values of the dataset, you use

ds.Tables[0].Rows[0][columnno]

HTH

Well almost any object. Depends if the Session is InProc or stored in the state server or SQL Server. In which case the the object would have to be serializable. It is also worth mentioning that DataSets can be stored in Session while DataRows can't.

batman++:

Depends if the Session is InProc or stored in the state server or SQL Server. In which case the the object would have to be serializable.

Thanks batman! I missed that :)

I get the following error then I run it:

Object reference not set to an instance of an object.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 41: Line 42:Line 43: Session["varString"] = ds.Tables["CRIMP_Common_Lookup"].Rows[0][2].ToString();Line 44: myConnection.Close();Line 45:

The error indicates that there is nothing in the 3rd column of the first row of the table CRIMP_Common_Lookup. Without the code used to populate the DataSet, all I can tell you is that either the table doesn't exist OR there are no rows in that table OR the value in the column is null. If you are using vs2005 start debugging, set a break point on a line just before the error and use the DataSet visualizer to check the contents. Otherwise the you can just add an if clause to check for null before adding it to Session.

Sorry, I see your SQL now.

select LOOKUP_DS from CRIMP_COMMON_LOOKUP where LOOKUP_NM = 'CRIMP_strName'";

Make sure that LOOKUP_DS is the 3rd column in the table. You can also use Rows[0]["LOOKUP_DS"].ToString().

Also you might want to set the table name in the Fill() method.

myCommand.Fill(ds, "CRIMP_common_lookup");

C# is case sensative even though sql usually is not.

Thanks guys,

I used batman++ last reply and it fixed the problem.

Fix ==> Also you might want to set the table name in the Fill() method.

myCommand.Fill(ds, "CRIMP_common_lookup");

Are you sure, that this statement is working fine?

select LOOKUP_DS from CRIMP_COMMON_LOOKUP where LOOKUP_NM = 'CRIMP_strName'";

I guess CRIMP_strName is a variable, but you have included it in the string. It should be like this..

select LOOKUP_DS from CRIMP_COMMON_LOOKUP where LOOKUP_NM ='" + CRIMP_strName + "'";

If you are going to store only one table in the dataset, then its not madatory to give the table name!. C# doesnt look for SQL case sensitiveness!

Thanks

Thanks e_screw,

Although my SQL definition works: (

select LOOKUP_DS from CRIMP_COMMON_LOOKUP where LOOKUP_NM = 'CRIMP_strName'";

), yours lookes more appropriate: (select LOOKUP_DS from CRIMP_COMMON_LOOKUP where LOOKUP_NM ='" + CRIMP_strName + "'";)

On another note, how would I write the code to use a DataReader instead of a DataSet? How would you rewrite the following code to reflect the DataReader?

private void GetData()

{

string ConnectionString = "server=MyServer;uid=Sphynx;pwd=letmein;database=MyDatabase";

string CommandText = "select LOOKUP_DS from CRIMP_COMMON_LOOKUP where LOOKUP_NM = '" + CRIMP_strName + "'";

SqlConnection myConnection = new SqlConnection(ConnectionString);

SqlDataAdapter myCommand = new SqlDataAdapter(CommandText, myConnection);

DataSet ds = new DataSet();

myCommand.Fill(ds, "CRIMP_common_lookup");

Session["varString"] = ds.Tables["CRIMP_Common_Lookup"].Rows[0][2].ToString();
myConnection.Close();

}

Thanks!

This will be the code to use SqlDataReader instead of DataSet. When you set the command text to the adapter, by default it will be SelectCommand. So we call the ExecuteReader on the selectCommand of the sqlDataAdapter. If you want to close the connection, you need to use the CommandBehavior.CloseConnection in ExecuteReader method.

1private void GetData() {2string ConnectionString ="server=MyServer;uid=Sphynx;pwd=letmein;database=MyDatabase";3string CommandText ="select LOOKUP_DS from CRIMP_COMMON_LOOKUP where LOOKUP_NM = '" + CRIMP_strName +"'";45 SqlConnection myConnection =new SqlConnection(ConnectionString);6 SqlDataAdapter myCommand =new SqlDataAdapter(CommandText, myConnection);7 SqlDataReader myReader = myCommand.SelectCommand.ExecuteReader();8while (myReader.Read()) {9 Session["varString"] = myReader["CRIMP_Common_Lookup"].ToString();10 }11// myConnection.Close(); or CommandBehavior.CloseConnection12}13

HTH

How do you specify which row and column to read the data from?

e_screw,

what did you do when you copy and paste code with line number? I haven't figure out how to include the line number.

If you can give me a hint how to do that I will really appreciate!

Sue

e_screw:

This will be the code to use SqlDataReader instead of DataSet. When you set the command text to the adapter, by default it will be SelectCommand. So we call the ExecuteReader on the selectCommand of the sqlDataAdapter. If you want to close the connection, you need to use the CommandBehavior.CloseConnection in ExecuteReader method.

1private void GetData() {2string ConnectionString ="server=MyServer;uid=Sphynx;pwd=letmein;database=MyDatabase";3string CommandText ="select LOOKUP_DS from CRIMP_COMMON_LOOKUP where LOOKUP_NM = '" + CRIMP_strName +"'";45 SqlConnection myConnection =new SqlConnection(ConnectionString);6 SqlDataAdapter myCommand =new SqlDataAdapter(CommandText, myConnection);7 SqlDataReader myReader = myCommand.SelectCommand.ExecuteReader();8while (myReader.Read()) {9 Session["varString"] = myReader["CRIMP_Common_Lookup"].ToString();10 }11// myConnection.Close(); or CommandBehavior.CloseConnection12}13

HTH

When you are using SqlDataReader, we use the .Read() method, to read the rows returned.myReader.Read() reads all the rows in the reader. In a particular row, you can directly access the column by its name or ordinal.myReader["fieldName"] or myReader[0] gets you the required column.

while(myReader.Read()) {
string name = myReader["name"].ToString();
}

This will read the fieldname "name" for each row returned.

Thanks