Hey there

I know there's a simple answer to this Quesiton because I've asked it before (but I have not yet used it and have now forgotten what the answer was!) so..

I have a simple Datareader returning the value of a field from a SQL Server but if the field is blank, the application returns an error.

A piece of the code is as follows:

cmd = New SqlCommand("SELECT * FROM thisTable WHERE Email = '" & memberVariable & "'", cn)
rdr = cmd.ExecuteReader()
rdr.Read()
textField1.Text = rdr("column1")
textField2.Text = rdr("column2")


I remember the solution was something like
textField2.Text = rdr("column2") & ""

but this returns the same error.

Help!

You can check for IsDbNull and that should do it for you.

However, a few misc comments. 1) You are using Dynamic Sql - change it to
paramaterized queries http://www.knowdotnet.com/articles/dynamisql.html .
The newest version of Les' refactoring tool
http://www.knowdotnet.com/articles/n...oducthome.html will even do
all the heavy lifting for you. If someone entered ' or 1=1 ; Drop Table
thisTable for the value of memberValue, you'd be in for a bad day ;-)
2) Instead of using an excecutereader statement, you may want to consider
using either ExcecuteScalar or an OutPut parameter. Not a big deal but
probably more efficient. If you check out www.betav.com ->Articles -> MSDN,
Bill Vaughn has probably the best discussion of it I've come across in
Retrieving the Gozoutas (or bringing home the Gozoutas). By using an output
param you can just check the value of the parameter. Another thing you can
do is use COUNT(*) and check the value since it can't ever be db null, it'll
be 0 if nothing's found

Also, it won't matter much in this instance but in general, use a numeric
index instead of the column name. Better yet though is calling GetOrdinal
of the column so you can use a Variable name that gives you the clarity of
referencing the columnname but the performance of using an index based
lookup. Or you can use Vaughn's method of using an enum, givng you the best
possible performance and readability.

HTH,

Bill

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
"dazzalondon" <(E-Mail Removed)> wrote in message
news:4B123080-CE1F-4DD8-A62C-(E-Mail Removed)...
> Hey there
>
> I know there's a simple answer to this Quesiton because I've asked it
before (but I have not yet used it and have now forgotten what the answer
was!) so..
>
> I have a simple Datareader returning the value of a field from a SQL
Server but if the field is blank, the application returns an error.
>
> A piece of the code is as follows:
>
> cmd = New SqlCommand("SELECT * FROM thisTable WHERE Email = '" &
memberVariable & "'", cn)
> rdr = cmd.ExecuteReader()
> rdr.Read()
> textField1.Text = rdr("column1")
> textField2.Text = rdr("column2")
>
>
> I remember the solution was something like
> textField2.Text = rdr("column2") & ""
>
> but this returns the same error.
>
> Help!
>
>

The first thing you need, is to turn option strict on. This forces you to
think about things like that and not rely on late binding (which makes code
less efficient and less readable).

Once you do that, you will see that you have to turn this into a string,
because what gets returned is an object. If this is a char or varchar type
column in the database, that could be a String being returned or a DBNull.
That means you have to handle both cases.

This could mean having an if statement that checks for DBNull. It could
also mean calling .ToString() on what is being returned, as the ToString
method of DBNull return the empty string, which is probably what you want in
this scenario.

"dazzalondon" <(E-Mail Removed)> wrote in message
news:4B123080-CE1F-4DD8-A62C-(E-Mail Removed)...
> Hey there
>
> I know there's a simple answer to this Quesiton because I've asked it
before (but I have not yet used it and have now forgotten what the answer
was!) so..
>
> I have a simple Datareader returning the value of a field from a SQL
Server but if the field is blank, the application returns an error.
>
> A piece of the code is as follows:
>
> cmd = New SqlCommand("SELECT * FROM thisTable WHERE Email = '" &
memberVariable & "'", cn)
> rdr = cmd.ExecuteReader()
> rdr.Read()
> textField1.Text = rdr("column1")
> textField2.Text = rdr("column2")
>
>
> I remember the solution was something like
> textField2.Text = rdr("column2") & ""
>
> but this returns the same error.
>
> Help!
>
>

thanks both

But I really need a quick solution.

..... or could you example your suggested routes, as I am unclea (aka a bit thick!).

Many thanks