SqlDataReader

testsubject · March 2nd, 2006, 12:26 PM

Hi folks,

I have a little problem which I have got no idea why this is happening...

Code:

cnPASAccess.Open()
Dim cmdPASAccess As New SqlClient.SqlCommand("SELECT PathRes, Ward_Admin, GP_Pract_Info, Pat_Search, Search_Pat_By_Cons, Waiting_L_Office FROM tblPASAccess WHERE PAS_ID =" & "'" & Session("UserNAM") & "'", cnPASAccess)

Dim drPASAccess As SqlClient.SqlDataReader
drPASAccess = cmdPASAccess.ExecuteReader()
dgdetails.DataSource = drPASAccess
dgdetails.DataBind()
Dim Path_Res As String

If drPASAccess.HasRows Then
Response.Write("Yes")
If Not drPASAccess.GetValue(drPASAccess.GetOrdinal("PathRes")) Is DBNull.Value Then

Label1.Text = drPASAccess.GetString(drPASAccess.GetOrdinal("PathRes"))
            End If
        Else
        Response.Write("no")
        End If
        drPASAccess.Close()

        cnPASAccess.Close()

Now there is data in the table basically a "y" or a "n". The data type is char and the really really strange thing is...the DataGrid binds to the datareader without a problem!!

Any ideas??

Thanks

Imar · March 2nd, 2006, 12:31 PM

Hi there,

After you bind the reader to the Grid and call DataBind, the Reader reads all the records and they are added to the grid.

After that, the reader is empty and HasRows returns false. You can see the reader as a read-only forward only cursor: you can only read from it once.

Check out this FAQ for more information: http://imar.spaanjaars.com/QuickDocId.aspx?QUICKDOC=356

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

testsubject · March 6th, 2006, 06:52 AM

Thanks Imar. I have checked this out and I am still having the same problem.

Code:

cnPASAccess.Open()
Dim cmdPASAccess As New SqlClient.SqlCommand("SELECT PathRes, Ward_Admin, GP_Pract_Info, Pat_Search, Search_Pat_By_Cons, Waiting_L_Office FROM tblPASAccess WHERE PAS_ID =" & "'" & Session("UserNAM") & "'", cnPASAccess)
Dim drPASAccess As SqlClient.SqlDataReader
drPASAccess = cmdPASAccess.ExecuteReader()
Dim Path_Res As String
Label1.Text = drPASAccess.GetString(drPASAccess.GetOrdinal("PathRes"))
drPASAccess.Close()
cnPASAccess.Close()

Now the above code still provides an Invalid attempt to read when no data is present.

But get this if a run the below code:

Code:

cnPASAccess.Open()
Dim cmdPASAccess As New SqlClient.SqlCommand("SELECT PathRes, Ward_Admin, GP_Pract_Info, Pat_Search, Search_Pat_By_Cons, Waiting_L_Office FROM tblPASAccess WHERE PAS_ID =" & "'" & Session("UserNAM") & "'", cnPASAccess)
Dim drPASAccess As SqlClient.SqlDataReader
drPASAccess = cmdPASAccess.ExecuteReader()
dgdetails.DataSource = drPASAccess
dgdetails.DataBind()drPASAccess.Close()
cnPASAccess.Close()

If happy binds to the datagrid and produces the data.

Any ideas?

Imar · March 6th, 2006, 05:13 PM

Quote:

quote:Now the above code still provides an Invalid attempt to read when no data is present.

I don't understand what you're saying, exactly.

You have two identical queries for both examples. Are you saying the first example gives an error and the second runs fine?

Or are you saying the first one gives an error when there is no data in the reader? If that's true, why are you not using HasRows?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

testsubject · March 7th, 2006, 03:54 AM

If I used the below code, it works without a problem at all. It simply pulls back one row of data and binds it to the datagrid.

Code:

cnPASAccess.Open()
Dim cmdPASAccess As New SqlClient.SqlCommand("SELECT PathRes, Ward_Admin, GP_Pract_Info, Pat_Search, Search_Pat_By_Cons, Waiting_L_Office FROM tblPASAccess WHERE PAS_ID =" & "'" & Session("UserNAM") & "'", cnPASAccess)
Dim drPASAccess As SqlClient.SqlDataReader
drPASAccess = cmdPASAccess.ExecuteReader()
dgdetails.DataSource = drPASAccess
dgdetails.DataBind()
drPASAccess.Close()
cnPASAccess.Close()

If I use the code below, it fails with an Invalid attempt to read when no data is present.

Code:

cnPASAccess.Open()
Dim cmdPASAccess As New SqlClient.SqlCommand("SELECT PathRes, Ward_Admin, GP_Pract_Info, Pat_Search, Search_Pat_By_Cons, Waiting_L_Office FROM tblPASAccess WHERE PAS_ID =" & "'" & Session("UserNAM") & "'", cnPASAccess)
Dim drPASAccess As SqlClient.SqlDataReader
drPASAccess = cmdPASAccess.ExecuteReader()
Dim Path_Res As String
Label1.Text = drPASAccess.GetString(drPASAccess.GetOrdinal("PathRes"))
drPASAccess.Close()
cnPASAccess.Close()

Basically what I am trying to do is this, (please see code below)

Code:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Try
   cnBarwickPas.Open()
   cnBarwickPas.Close()
Catch exp As Odbc.OdbcException
   Session("ActiveSession") = False
   Response.Redirect("error.aspx")
End Try

cnPASAccess.Open()
Dim cmdPASAccess As New SqlClient.SqlCommand("SELECT PathRes, Ward_Admin, GP_Pract_Info, Pat_Search, Search_Pat_By_Cons, Waiting_L_Office FROM tblPASAccess WHERE PASID =" & "'" & Session("UserNAM") & "'", cnPASAccess)
Dim drPASAccess As SqlClient.SqlDataReader
drPASAccess = cmdPASAccess.ExecuteReader()
If drPASAccess.HasRows Then
    Dim strPathRes As String
    strPathRes = drPASAccess.GetString(drPASAccess.GetOrdinal("PathRes"))
   If strPathRes = "y" Then
     lblPath_Res.Enabled = True
   End If
Else
    Response.Write("No Data")
End If

drPASAccess.Close()

cnPASAccess.Close()
End Sub

But its coming back with an error of Invalid attempt to read when no data is present. The same as the second code exmaple. Now I have used this method to get information into a label without a problem using an ODBC connection, but as soon as I try using a SQL Connection thats when i have started with problem.

Hope this is a little clearer.

Thanks

Imar · March 7th, 2006, 02:59 PM

You'll need to call Read first before you can read.

Usually, you can simply call Read to see if there are any rows in a Reader. However, if you want to determine whether there are any row sand then use the reader for, say, a DataGrid, you need to use HasRows or otherwise you'll miss a row in the grid.

So, this should work:

If drPASAccess.Read() Then
'Data
Else
Response.Write("No Data")
End If

But you haven't actually answered my previous question, merely posted the same code again:

Quote:

quote:You have two identical queries for both examples. Are you saying the first example gives an error and the second runs fine?

Or are you saying the first one gives an error when there is no data in the reader? If that's true, why are you not using HasRows?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

testsubject · March 7th, 2006, 05:42 PM

Hi imar,

The first example below runs without a problem at all.

Code:

cnPASAccess.Open()
Dim cmdPASAccess As New SqlClient.SqlCommand("SELECT PathRes, Ward_Admin, GP_Pract_Info, Pat_Search, Search_Pat_By_Cons, Waiting_L_Office FROM tblPASAccess WHERE PAS_ID =" & "'" & Session("UserNAM") & "'", cnPASAccess)
Dim drPASAccess As SqlClient.SqlDataReader
drPASAccess = cmdPASAccess.ExecuteReader()
dgdetails.DataSource = drPASAccess
dgdetails.DataBind()
drPASAccess.Close()
cnPASAccess.Close()

The second example fails with an Invalid attempt to read when no data is present message.

Code:

cnPASAccess.Open()
Dim cmdPASAccess As New SqlClient.SqlCommand("SELECT PathRes, Ward_Admin, GP_Pract_Info, Pat_Search, Search_Pat_By_Cons, Waiting_L_Office FROM tblPASAccess WHERE PAS_ID =" & "'" & Session("UserNAM") & "'", cnPASAccess)
Dim drPASAccess As SqlClient.SqlDataReader
drPASAccess = cmdPASAccess.ExecuteReader()
Dim Path_Res As String
Label1.Text = drPASAccess.GetString(drPASAccess.GetOrdinal("PathRes"))
drPASAccess.Close()
cnPASAccess.Close()

Now if I use this example:

Code:

cnPASAccess.Open()
Dim cmdPASAccess As New SqlClient.SqlCommand("SELECT PathRes, Ward_Admin, GP_Pract_Info, Pat_Search, Search_Pat_By_Cons, Waiting_L_Office FROM tblPASAccess WHERE PAS_ID =" & "'" & Session("UserNAM") & "'", cnPASAccess)
Dim drPASAccess As SqlClient.SqlDataReader
drPASAccess = cmdPASAccess.ExecuteReader()
dgdetails.DataSource = drPASAccess
dgdetails.DataBind()

If drPASAccess.HasRows Then
     Response.Write("Yes")
     If Not drPASAccess.GetValue(drPASAccess.GetOrdinal("PathRes")) Is DBNull.Value Then
           Label1.Text = drPASAccess.GetString(drPASAccess.GetOrdinal("PathRes"))
     End If
Else
     Response.Write("no")
End If

drPASAccess.Close()
cnPASAccess.Close()

It binds the datareader to the datagrid without a problem but fails and displays an Invalid attempt to read when no data is present error.

Now the part I don't understand at all is, if I use the above example using a ODBC connection to a different database with the same tables etc is works fine, but as soon as I change it to a SQL connection as the data is in a SQL database thats when it all stops working. So I don't really understand why I needed to use the drPASAccess.Read()

Thanks

Imar · March 7th, 2006, 05:48 PM

The article I pointed you to earlier explains this.

After you have data bound the grid, you can no longer read from the data reader. You can see the reader as a forward only cursor, although technically, I think it's not a true cursor. Anyway, this means you can't read from the reader anymore, and you'll need to use the tricks I showed in the article.

That is, either use HasRows before you bind, or query the Count property of the Items collection.

Did you try any of that?

Not sure why it works on ODBC though....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

testsubject · March 7th, 2006, 06:01 PM

Exactly thats what I couldn't get my head around.

I havent tried using the HasRows before I bind but to be honest in this case I only used the datagrid to prove to myself that there was actual data in the datareader.

So going back to this example.

Code:

cnPASAccess.Open()
Dim cmdPASAccess As New SqlClient.SqlCommand("SELECT PathRes, Ward_Admin, GP_Pract_Info, Pat_Search, Search_Pat_By_Cons, Waiting_L_Office FROM tblPASAccess WHERE PAS_ID =" & "'" & Session("UserNAM") & "'", cnPASAccess)
Dim drPASAccess As SqlClient.SqlDataReader
drPASAccess = cmdPASAccess.ExecuteReader()
If drPASAccess.HasRows Then
    If Not drPASAccess.GetValue(drPASAccess.GetOrdinal("PathRes")) Is DBNull.Value Then
        Dim Path_Res As String
        Path_Res = drPASAccess.GetString(drPASAccess.GetOrdinal("PathRes"))
        If Path_Res = "y" then
           label1.visible = True
        Else
           label1.visible = False
    End If
End If
drPASAccess.Close()
cnPASAccess.Close()

What would I need to do exactly?? Code example would be great. Sorry if I am sounding stupid.

Imar · March 7th, 2006, 06:08 PM

You have to Read() (pun intended) ;)

If drPASAccess.Read() Then
  If Not drPASAccess.GetValue(drPASAccess.GetOrdinal("PathR es")) Is DBNull.Value Then
    Dim Path_Res As String
    Path_Res = drPASAccess.GetString(drPASAccess.GetOrdinal("Path Res"))
    If Path_Res = "y" then
      label1.visible = True
    Else
      label1.visible = False
    End If
  End If
End If

You need to call Read before you can access the first row (if there is any).

See the DataReader help for more info: http://msdn2.microsoft.com/en-us/library/haa3afyz.aspx

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.