Capturing a variable in ASP.NET

Hi all,

I am new to .NET, after many years with classic ASP I am struggling a little with something that I am sure is really easy to do.

Basically what I want to do, is execute an SQL statement, that will return a single value. I then need to store this value as a variable, so that I can pass it into another query later.

It seems easy to output the record, but how do I store it as a variable !!

This is my code to connect and run the SQL... all I need to do as retreive the value, and put it aganist a variable...

Function higher_manager_ein() As System.Data.IDataReader

Dim connectionString As String = "server='myserver'; user id='userid'; password='pwd'; database='DB'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT distinct MEASURE FROM [CCC_MEASURE] where ein = '" & request("man_ein2") & "'"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader

End FunctionSince it sounds like all you are doing is returning one value, you might want to look at the dbCommand.ExecuteScalar method instead. That is made to return one value.

Now to explain the way you are currently doing it, you can think of the DataReader as an old ADO recordset that you used in ASP. So when this function returns the datareader, you then need to get the value out of the recordset. The firsts difference is that the DataReader object doesn't not start out at the first record. It starts right before it. So you need to call the DataReader.Read() method. That will return true or false depending on if it has read the next record. To get the value, you need to invoke the appropriate method depending on what type of value it is. So DataReader.GetString will return the value as a string. You can pass either the ordinal position of the column in the datareader or the column name.

Since what you are saying above though, I would go with the ExecuteScalar method off of the datareader instead. So again, if this is returning a string value, then you would do the following in place of the Dim dataReader As System.Data.IDataReader command above:

Dim myStringValue As String = CType( dbCommand.ExecuteScalar(), String )

You need to change the type to a string since the ExecuteScalar method returns the type Object.|||and use parameterized queries.

hth