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 December 28th, 2005, 11:19 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 DataReader brings back No data for Rows/Columns

I have a slight problem.

Please refer to below code

Code:
        drPatsNextKin = cmdPatsNextKin.ExecuteReader(CommandBehavior.SingleRow)
        drPatsNextKin.Close()
        lbxTitleSts.DataSource = drPatsNextKin
        lbxTitleSts.DataTextField = ("NKTITLE")
        lbxTitleSts.DataBind()
        lblTitleSts.Text = drPatsNextKin("NKTITLE")
        lblTitleSts.DataBind()
        drPatsNextKin.Close()
Now this work great providing that there is some data for the databinder to bind too, but when there is no data it produces an error stating there is no data for row/column. Now this is fine I know why I am receiving this error but I want to try and catch this in and if statement so i can tell my programme to do something else rather than display the error page.

Example: If there is no data to bind the lblTitleSts or lbxTitleSts then create a label or notification on the web form explaining there is no NKTITLE to display.

If anyone has any ideas how to sort this out I would be very grateful; providing code examples as well if possible.

Thanks
Reply With Quote
  #2 (permalink)  
Old December 28th, 2005, 11:36 AM
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,

If you're using .NET 1.1 or later, use the HasRows property of the reader to see if there are any rows returned:

http://msdn.microsoft.com/library/de...srowstopic.asp

and

http://imar.spaanjaars.com/QuickDocId.aspx?QUICKDOC=356

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #3 (permalink)  
Old December 28th, 2005, 11:37 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

Yeah sorry about that, I am using Visual Studio .NET 2003 (Visual Basic).

Reply With Quote
  #4 (permalink)  
Old December 28th, 2005, 11:45 AM
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

Sorry about what?? ;)

Anyway, VS.NET 2003 uses the .NET Framework 1.1 so you're good to go.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #5 (permalink)  
Old December 28th, 2005, 12:03 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

The problem is as soon as it executes line "drPatsNextKin = cmdPatsNextKin.ExecuteReader(CommandBehavior.Singl eRow)" thats where it is falling over. So i cant put this into an if statement.
Reply With Quote
  #6 (permalink)  
Old December 28th, 2005, 12:17 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

What exception do you get? And how does the rest of your code look like?

AFAIK, using CommandBehavior.SingleRow with a SQL statement that returns no rows should give you back an empty DataReader, so you should be able to check HasRows...

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #7 (permalink)  
Old December 29th, 2005, 04:07 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

Hi,

This is the whole class.

Code:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strPatNum As String = Request.QueryString("PatNum")

        cnBarwickPas.Open()
        Dim cmdPatsNextKin As New Odbc.OdbcCommand("SELECT ASSETCOM.MASTER5.MSMROL, ASSETCOM.NEXTKIN.NKMROLL, ASSETCOM.NEXTKIN.NKTITLE, ASSETCOM.NEXTKIN.NKFOREN, ASSETCOM.NEXTKIN.NKSURNM, ASSETCOM.NEXTKIN.NKADDR1, ASSETCOM.NEXTKIN.NKADDR2, ASSETCOM.NEXTKIN.NKADDR3, ASSETCOM.NEXTKIN.NKADDR4, ASSETCOM.NEXTKIN.NKPOST7, ASSETCOM.NEXTKIN.NKPHONE, ASSETCOM.NEXTKIN.NKRELAT FROM ASSETCOM.MASTER5, ASSETCOM.NEXTKIN WHERE ASSETCOM.MASTER5.MSMROL = ASSETCOM.NEXTKIN.NKMROLL AND ASSETCOM.MASTER5.MSMROL =" & strPatNum, cnBarwickPas)

        Dim drPatsNextKin As Odbc.OdbcDataReader

        '####################
        '## Displays Title ##
        '####################
        drPatsNextKin = cmdPatsNextKin.ExecuteReader(CommandBehavior.SingleRow)
        drPatsNextKin.Close()
        lbxTitleSts.DataSource = drPatsNextKin
        lbxTitleSts.DataTextField = ("NKTITLE")
        lbxTitleSts.DataBind()
        lblTitleSts.Text = drPatsNextKin("NKTITLE")
        lblTitleSts.DataBind()
        drPatsNextKin.Close()

        '########################
        '## Displays Forenames ##
        '########################
        drPatsNextKin = cmdPatsNextKin.ExecuteReader(CommandBehavior.SingleRow)
        lbxForenamesSts.DataSource = drPatsNextKin
        lbxForenamesSts.DataTextField = ("NKFOREN")
        lbxForenamesSts.DataBind()
        lblForenamesSts.Text = drPatsNextKin("NKFOREN")
        lblForenamesSts.DataBind()
        drPatsNextKin.Close()

        '######################
        '## Displays Surname ##
        '######################
        drPatsNextKin = cmdPatsNextKin.ExecuteReader(CommandBehavior.SingleRow)
        lbxSurnameSts.DataSource = drPatsNextKin
        lbxSurnameSts.DataTextField = ("NKSURNM")
        lbxSurnameSts.DataBind()
        lblSurnameSts.Text = drPatsNextKin("NKSURNM")
        lblSurnameSts.DataBind()
        drPatsNextKin.Close()

        '#############################
        '## Displays Address Line 1 ##
        '#############################
        drPatsNextKin = cmdPatsNextKin.ExecuteReader(CommandBehavior.SingleRow)
        lbxAddress1Sts.DataSource = drPatsNextKin
        lbxAddress1Sts.DataTextField = ("NKADDR1")
        lbxAddress1Sts.DataBind()
        lblAddress1Sts.Text = drPatsNextKin("NKADDR1")
        lblAddress1Sts.DataBind()
        drPatsNextKin.Close()

        '#############################
        '## Displays Address Line 2 ##
        '#############################
        drPatsNextKin = cmdPatsNextKin.ExecuteReader(CommandBehavior.SingleRow)
        lbxAddress2Sts.DataSource = drPatsNextKin
        lbxAddress2Sts.DataTextField = ("NKADDR2")
        lbxAddress2Sts.DataBind()
        lblAddress2Sts.Text = drPatsNextKin("NKADDR2")
        lblAddress2Sts.DataBind()
        drPatsNextKin.Close()

        '#############################
        '## Displays Address Line 3 ##
        '#############################
        drPatsNextKin = cmdPatsNextKin.ExecuteReader(CommandBehavior.SingleRow)
        lbxAddress3Sts.DataSource = drPatsNextKin
        lbxAddress3Sts.DataTextField = ("NKADDR3")
        lbxAddress3Sts.DataBind()
        lblAddress3Sts.Text = drPatsNextKin("NKADDR3")
        lblAddress3Sts.DataBind()
        drPatsNextKin.Close()

        '#############################
        '## Displays Address Line 4 ##
        '#############################
        drPatsNextKin = cmdPatsNextKin.ExecuteReader(CommandBehavior.SingleRow)
        lbxAddress4Sts.DataSource = drPatsNextKin
        lbxAddress4Sts.DataTextField = ("NKADDR4")
        lbxAddress4Sts.DataBind()
        lblAddress4Sts.Text = drPatsNextKin("NKADDR4")
        lblAddress4Sts.DataBind()
        drPatsNextKin.Close()

        '########################
        '## Displays Post Code ##
        '########################
        drPatsNextKin = cmdPatsNextKin.ExecuteReader(CommandBehavior.SingleRow)
        lbxPostCodeSts.DataSource = drPatsNextKin
        lbxPostCodeSts.DataTextField = ("NKPOST7")
        lbxPostCodeSts.DataBind()
        lblPostCodeSts.Text = drPatsNextKin("NKPOST7")
        lblPostCodeSts.DataBind()
        drPatsNextKin.Close()

        '###########################
        '## Displays Phone Number ##
        '###########################
        drPatsNextKin = cmdPatsNextKin.ExecuteReader(CommandBehavior.SingleRow)
        lbxTelephoneNumSts.DataSource = drPatsNextKin
        lbxTelephoneNumSts.DataTextField = ("NKPHONE")
        lbxTelephoneNumSts.DataBind()
        lblTelephoneNumSts.Text = drPatsNextKin("NKPHONE")
        lblTelephoneNumSts.DataBind()
        drPatsNextKin.Close()

        '#######################
        '## Displays Relation ##
        '#######################
        drPatsNextKin = cmdPatsNextKin.ExecuteReader(CommandBehavior.SingleRow)
        lbxRelationSts.DataSource = drPatsNextKin
        lbxRelationSts.DataTextField = ("NKRELAT")
        lbxRelationSts.DataBind()
        lblRelationSts.Text = drPatsNextKin("NKRELAT")
        lblRelationSts.DataBind()
        drPatsNextKin.Close()

        '#######################
        '## Closes Connection ##
        '#######################
        cnBarwickPas.Close()
    End Sub

End Class
And this is the error i get.

No data exists for the row/column.
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.InvalidOperationException: No data exists for the row/column.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: No data exists for the row/column.]
   System.Data.Odbc.OdbcDataReader.GetValue(Int32 i) +102
   System.Data.Odbc.OdbcDataReader.get_Item(String value) +20
   DDHPAS.WebForm6.Page_Load(Object sender, EventArgs e) +228
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +731

Hope this helps.

Reply With Quote
  #8 (permalink)  
Old December 29th, 2005, 05:32 AM
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

Hmmmm, maybe CommandBehavior.SingleRow doesn't work for an Odbc connection? Since CommandBehavior.SingleRow is used for optimization only, why not just drop the statement and see if HasRows then works?

Regarding the code you posted: why do you execute so many DataReaders? Why not execute once and then retrieve all of the columns, and then using one of the Get* methods retrieve the data from a single row?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #9 (permalink)  
Old December 29th, 2005, 05:57 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

The reason why I am using so many datareaders is because I cannot for some reason beyond me data bind straight to a label's text prop. If I could just bind the following textboxes then that would solve alot of my problems.

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

Dim strPatNum As String = Request.QueryString("PatNum")

cnBarwickPas.Open()

Dim cmdPatsNextKin As New Odbc.OdbcCommand("SELECT ASSETCOM.MASTER5.MSMROL, ASSETCOM.NEXTKIN.NKMROLL, ASSETCOM.NEXTKIN.NKTITLE, ASSETCOM.NEXTKIN.NKFOREN, ASSETCOM.NEXTKIN.NKSURNM, ASSETCOM.NEXTKIN.NKADDR1, ASSETCOM.NEXTKIN.NKADDR2, ASSETCOM.NEXTKIN.NKADDR3, ASSETCOM.NEXTKIN.NKADDR4, ASSETCOM.NEXTKIN.NKPOST7, ASSETCOM.NEXTKIN.NKPHONE, ASSETCOM.NEXTKIN.NKRELAT FROM ASSETCOM.MASTER5, ASSETCOM.NEXTKIN WHERE ASSETCOM.MASTER5.MSMROL = ASSETCOM.NEXTKIN.NKMROLL AND ASSETCOM.MASTER5.MSMROL =" & strPatNum, cnBarwickPas)

Dim drPatsNextKin As Odbc.OdbcDataReader
drPatsNextKin = cmdPatsNextKin.ExecuteReader(CommandBehavior.SingleRow)

lblTitleSts.Text = drPatsNextKin("NKTITLE")
lblForenamesSts.Text = drPatsNextKin("NKFOREN")
lblSurnameSts.Text = drPatsNextKin("NKSURNM")
lblAddress1Sts.Text = drPatsNextKin("NKADDR1")
lblAddress2Sts.Text = drPatsNextKin("NKADDR2")
lblAddress3Sts.Text = drPatsNextKin("NKADDR3")
lblAddress4Sts.Text = drPatsNextKin("NKADDR4")
lblPostCodeSts.Text = drPatsNextKin("NKPOST7")
lblTelephoneNumSts.Text = drPatsNextKin("NKPHONE")
lblRelationSts.Text = drPatsNextKin("NKRELAT")

drPatsNextKin.Close()

cnBarwickPas.Close()

End Sub
Now if i could do something like that everything would be fine, but it wont allow me to databind straight to a label without databinding the datareader to a ListBox or a DropDownBox first.


Reply With Quote
  #10 (permalink)  
Old December 29th, 2005, 06:36 AM
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're almost there. However, drPatsNextKin("NKSURNM") etc is not a valid value for the text property.

You need something like GetString to get the column's value as a String:

http://msdn.microsoft.com/library/de...asp?frame=true

Since GetString expects a zero based column index instead of a column's name, you can use GetOrdinal to translate the column's name into it's column index. So, something like this should works:

SomeLabel.Text = drPatsNextKin.GetString(drPatsNextKin.GetOrdinal(" NKSURNM"))

You may also need to check for NULL values:

If Not drPatsNextKin.GetValue(drPatsNextKin.GetOrdinal("N KSURNM")) Is DBNull.Value Then
  SomeLabel.Text = drPatsNextKin.GetString(drPatsNextKin.GetOrdinal(" NKSURNM"))
End If

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
Rows 2 Columns kilika Oracle 0 October 14th, 2005 01:16 PM
Columns To Rows alyeng2000 SQL Language 2 March 11th, 2005 03:08 PM
Data Report: Columns Against Rows bemular VB How-To 0 January 6th, 2005 12:52 AM
Rows into columns shamsad Oracle 0 April 7th, 2004 04:38 AM



All times are GMT -4. The time now is 02:17 PM.


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