Datareader Query

rsm42 · January 8th, 2007, 02:11 PM

Hi,

Within asp.net 1.1 / vbnet I have created a function and used a datareader to grab some data from a database as per the code below :

Code:

 Function GetProdID() As System.Data.IDataReader

        Dim strConnString As String = ConfigurationSettings.AppSettings.Get("ConnectionString")
        strConnString = String.Format(strConnString, Server.MapPath("\db\nwguitars.mdb"))
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(strConnString)

        Dim queryString As String = "SELECT tblCartItems.intCartID, tblProducts.strProductName, "& _
        "tblCartItems.intProductID, tblType.strTypeDetails, tblCartItems.intQuantityOrder, "& _
        "tblProducts.intQuantityInStock FROM tblType INNER JOIN (tblProducts INNER JOIN tblCartItems ON tblProducts.intProductID = tblCartItems.intProductID) "& _
        "ON tblType.intTypeID = tblProducts.intTypeID WHERE [tblCartItems].[intCartID] = " & Session("CartID") & " ; "

        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

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

        Return dataReader
    End Function

I want to be able to call one of the values (intProductID) from another function. What's the best way to do this from the datareader code - any examples would help, thanks ?

dparsons · January 8th, 2007, 05:08 PM

Unfortunately, to be able to use the DataReader object you have to have an open connection to a data store, once its closed you lose the ability to read through the DataReader and in that regard it is pointless to pass a Datareader object around.

My advice would be to use a Datatable or Dataset as those are memory resident and will contain data after the connection ot the database is closed.

So what i might do is something like:

Function GetProdID() as DataTable
//Your code
Return dt
End Function

Function foo(ByVal dt as DataTable) as [datatype]
Dim iID as Integer = dt.rows[n].item["intProductID"]
Return [something]
End Function

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature