 |
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
December 28th, 2005, 11:19 AM
|
|
Authorized User
|
|
Join Date: Dec 2005
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
December 28th, 2005, 11:37 AM
|
|
Authorized User
|
|
Join Date: Dec 2005
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Yeah sorry about that, I am using Visual Studio .NET 2003 (Visual Basic).
|
|
December 28th, 2005, 11:45 AM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
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.
|
|
December 28th, 2005, 12:03 PM
|
|
Authorized User
|
|
Join Date: Dec 2005
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
|
December 28th, 2005, 12:17 PM
|
|
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
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.
|
|
December 29th, 2005, 04:07 AM
|
|
Authorized User
|
|
Join Date: Dec 2005
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
|
December 29th, 2005, 05:32 AM
|
|
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
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.
|
|
December 29th, 2005, 05:57 AM
|
|
Authorized User
|
|
Join Date: Dec 2005
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
|
December 29th, 2005, 06:36 AM
|
|
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
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.
|
|
|
