Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 2nd, 2006, 12:26 PM
Authorized User
Points: 285, Level: 5
Points: 285, Level: 5 Points: 285, Level: 5 Points: 285, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2005
Location: , , .
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default SqlDataReader

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

Reply With Quote
  #2 (permalink)  
Old March 2nd, 2006, 12:31 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old March 6th, 2006, 06:52 AM
Authorized User
Points: 285, Level: 5
Points: 285, Level: 5 Points: 285, Level: 5 Points: 285, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2005
Location: , , .
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
Reply With Quote
  #4 (permalink)  
Old March 6th, 2006, 05:13 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
  #5 (permalink)  
Old March 7th, 2006, 03:54 AM
Authorized User
Points: 285, Level: 5
Points: 285, Level: 5 Points: 285, Level: 5 Points: 285, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2005
Location: , , .
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old March 7th, 2006, 02:59 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
  #7 (permalink)  
Old March 7th, 2006, 05:42 PM
Authorized User
Points: 285, Level: 5
Points: 285, Level: 5 Points: 285, Level: 5 Points: 285, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2005
Location: , , .
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old March 7th, 2006, 05:48 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
  #9 (permalink)  
Old March 7th, 2006, 06:01 PM
Authorized User
Points: 285, Level: 5
Points: 285, Level: 5 Points: 285, Level: 5 Points: 285, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2005
Location: , , .
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #10 (permalink)  
Old March 7th, 2006, 06:08 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inheriting SqlDataReader and new KeyWord software_developer_kk C# 2 June 28th, 2007 08:17 AM
looping through columns in sqldatareader rlull ASP.NET 1.0 and 1.1 Basics 1 November 5th, 2005 02:32 AM
Urgent getting Error while using SqlDataReader hums ADO.NET 2 September 29th, 2004 10:03 AM
SelectedValue property using SqlDataReader shiju ADO.NET 0 September 28th, 2003 03:27 AM



All times are GMT -4. The time now is 12:24 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.