Using DataReader if DB column is blank ! help

[Guest]

Hi there

I have an app with various pages running a DataReader in the Page _Load event pulling data from a database and filling text boxes.

But of coruse, if a column in the DB is blank, then the application fails and says it 'Invalid attempt to read when no data is present.'

I've tried, where possible, to implement client-side validation to gain as much information in the columns to ease this issue but I cannot implement in all instances and there will be some columns blank which will cause this error.

There is a suggestion I use a 'IsDbNull' - but I am not sure how to use that in this context; also a DB column could be simple 'blank' and not NULL.


Example code is as follows:

Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim rdr As SqlDataReader
cn = New SqlConnection("xx")

cn.Open()
cmd = New SqlCommand("SELECT * FROM memberDetails WHERE Email=@Email", cn)
cmd.parameters.add("@Email", EmailVariable)
rdr = cmd.ExecuteReader()
rdr.Read()
txtFName.Text = rdr("FName")
txtSName.Text = rdr("SName")
txtDoB.Text = rdr("DoB")
rdr.Close()

I'm looking for a solution please but ideally an example of some code I can use to solve the issue.
Thanks in adnace; I'll be continually checking back into the forum for updates.

 

[Patrice]

What do you mean by 'blank' ?

For now it looks like to me there is no match for your select statement. The
Read statements returns a boolean that indicates if the row was present. Try
to check this return value...

--

Hi there

I have an app with various pages running a DataReader in the Page _Load

event pulling data from a database and filling text boxes.

But of coruse, if a column in the DB is blank, then the application fails

and says it 'Invalid attempt to read when no data is present.'

I've tried, where possible, to implement client-side validation to gain as

much information in the columns to ease this issue but I cannot implement in
all instances and there will be some columns blank which will cause this
error.

There is a suggestion I use a 'IsDbNull' - but I am not sure how to use

that in this context; also a DB column could be simple 'blank' and not NULL.

Example code is as follows:

Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim rdr As SqlDataReader
cn = New SqlConnection("xx")

cn.Open()
cmd = New SqlCommand("SELECT * FROM memberDetails WHERE Email=@Email", cn)
cmd.parameters.add("@Email", EmailVariable)
rdr = cmd.ExecuteReader()
rdr.Read()
txtFName.Text = rdr("FName")
txtSName.Text = rdr("SName")
txtDoB.Text = rdr("DoB")
rdr.Close()

I'm looking for a solution please but ideally an example of some code I can use to solve the issue.
Thanks in adnace; I'll be continually checking back into the forum for

updates.

 

[Guest]

Hi.

Well, it does work (well, on my other pages at least) - if the DB has data in the column, then it fills the txt boxes with the value ' txtFName.Text = rdr("FName") ' for example.

Trouble is that the ' rdr ' runs but if there isn't anything in the column it fails, so I cannot even do a IF statement after seeing if it is blank/null/nothing.

Now, if there is a more suitable solution to reading the data, then please let me know.

I've had a go last 30mins at using a 'IsDBNull' but un sure how to use it and you can see from my inexperience from my following attempt at using the statement !:

Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim rdr As SqlDataReader
cn = New SqlConnection("xx")
cn.Open()
cmd = New SqlCommand("SELECT * FROM memberDetails WHERE
Email=@Email", cn)
cmd.parameters.add("@Email", EmailVariable)

Dim read1 As String
rdr = cmd.ExecuteReader()
rdr.Read()
read1 = "FName"
rdr.IsDBNull(read1)

If read1 = Nothing Then
Label1.Text = "nothing in DB"
Else
Label1.Text = rdr("FName")
End If

cn.Close()

Now, if you think that a dataAdapter or something is more suitable (becasuse in this page, the Page_Load event fills the text boxes, but then my clients are allowed to update their details with an UPDATE button.

Please help me.

 

[Patrice]

Try :

If IsDbNull(rdr("FName")) Then
....

Unless you are sure that there is a match, you could also test the Read
retunr value.
You could also use the ToString method that eeturns an empty string if the
file is NULL. I would also suggest not to allow NULL values unless you
rezally need (ie. NULL=unkonwn value, you could also just dissavlow NULL and
use an empty string instead).

Patrice

--

Hi.

Well, it does work (well, on my other pages at least) - if the DB has data

in the column, then it fills the txt boxes with the value ' txtFName.Text =
rdr("FName") ' for example.

Trouble is that the ' rdr ' runs but if there isn't anything in the column

it fails, so I cannot even do a IF statement after seeing if it is
blank/null/nothing.

Now, if there is a more suitable solution to reading the data, then please let me know.

I've had a go last 30mins at using a 'IsDBNull' but un sure how to use it

and you can see from my inexperience from my following attempt at using the
statement !:

Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim rdr As SqlDataReader
cn = New SqlConnection("xx")
cn.Open()
cmd = New SqlCommand("SELECT * FROM memberDetails WHERE
Email=@Email", cn)
cmd.parameters.add("@Email", EmailVariable)

Dim read1 As String
rdr = cmd.ExecuteReader()
rdr.Read()
read1 = "FName"
rdr.IsDBNull(read1)

If read1 = Nothing Then
Label1.Text = "nothing in DB"
Else
Label1.Text = rdr("FName")
End If

cn.Close()

Now, if you think that a dataAdapter or something is more suitable

(becasuse in this page, the Page_Load event fills the text boxes, but then
my clients are allowed to update their details with an UPDATE button.

 

[Guest]

Thank you Patrice

Though I'm having to implement NULLs into the specific columns, I think the problem is now solved thanks to you.

$1million in the post

 

[William Ryan eMVP]

Hi Dazzalondon:

Two things:

1) Turn on Option Strict. Even though that's not the cause of your
problem, it invariably will be at some point and implicit type conversion
errors are one of the more insidious types b/c they appear to work ok, it's
just that they cause Logic errors. You'll save yourself a ton of future
headaches by turning on Option Strict
2) You may want to make a function (or create a Shared/Static method so
that you can reuse this in other instances, it will come up again)
http://www.knowdotnet.com/articles/handlingnullvalues.html
3) If you are just using it for a DataReader, you can always use the IsNull
function (if your DB Supports it) to make sure no nulls are sent back.


--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

Thank you Patrice

Though I'm having to implement NULLs into the specific columns, I think

the problem is now solved thanks to you.