dbnull error with mysql....

[wink martindale]

Hello,

I am writing a vb.net app.Using MySql db with the corelabs connector.

I am getting the following error when attempting to read values from a
datareader:
Cast from type 'DBNull' to type 'String' is not valid.

This error occurs on a field that DOES have data in it, but I can't
figure out why! Here is a sample of the code I am using:

strSql = ("Select * from pt_info where MRN='" & Form3.intNewMRN & "'")
myCmd = New MySqlCommand(st rSql, MyCon)
myReader = myCmd.ExecuteRe ader()
While myReader.Read()
txtFname.Text = myReader("fname ")
txtLname.Text = myReader("lname ")
txtMiddle.Text = myReader("middl e")
txtAddress.Text = myReader("addre ss")
txtAddress2.Tex t = myReader("addre ss2")
txtCity.Text = myReader("cityc ode")
txtZip.Text = myReader("zip") <--DBnull error here
.....more of the same
....
end while
Looking at the db, there data present in the zip field, but I cannot
seem to pull the info out.
Can anyone help with this problem????

TIA,


Daniel

[Adael]

Re: dbnull error with mysql....

Hi, You can use the MySQL ifnull() function.

more doc about ifnull()
http://dev.mysql.com/doc/mysql/en/co...functions.html

example:

strSQL = "SELECT
ifnull(fname, ""),
ifnull(lname, ""),
ifnull(address, ""),
ifnull(address2 , "") .....
from pt_info where MRN='" & Form3.intNewMRN

hope that helps.

[wink martindale]

Re: dbnull error with mysql....

Thanks for the response.

Yes, I do what to handle null values, but my problem is that the
field(s) in question are NOT null. Somehow, for some reason beyond my
feeble brain, I can't get the data out. Just keep getting the dbnull
error. If I comment out the line of code that gets the error, I then
get the dbnull error on the one before it. I am totally lost. Using a
datareader. Maybe it has something to do with forward only reading or
some odd thing like that??!!?

Any Help would be greatly apprectiated,

Daniel

On 25 Aug 2005 12:26:46 -0700, "Adael" <adaelxp@gmail. com> wrote:
[color=blue]
>Hi, You can use the MySQL ifnull() function.
>
>more doc about ifnull()
>http://dev.mysql.com/doc/mysql/en/co...functions.html
>
>example:
>
>strSQL = "SELECT
> ifnull(fname, ""),
> ifnull(lname, ""),
> ifnull(address, ""),
> ifnull(address2 , "") .....
>from pt_info where MRN='" & Form3.intNewMRN
>
>hope that helps.[/color]

[Dennis]

Re: dbnull error with mysql....

Perhaps your first record being read contains all DBNull's. You might try
using a DataSet and see if the first record is in fact all Nulls.
--
Dennis in Houston


"wink martindale" wrote:
[color=blue]
> Thanks for the response.
>
> Yes, I do what to handle null values, but my problem is that the
> field(s) in question are NOT null. Somehow, for some reason beyond my
> feeble brain, I can't get the data out. Just keep getting the dbnull
> error. If I comment out the line of code that gets the error, I then
> get the dbnull error on the one before it. I am totally lost. Using a
> datareader. Maybe it has something to do with forward only reading or
> some odd thing like that??!!?
>
> Any Help would be greatly apprectiated,
>
> Daniel
>
> On 25 Aug 2005 12:26:46 -0700, "Adael" <adaelxp@gmail. com> wrote:
>[color=green]
> >Hi, You can use the MySQL ifnull() function.
> >
> >more doc about ifnull()
> >http://dev.mysql.com/doc/mysql/en/co...functions.html
> >
> >example:
> >
> >strSQL = "SELECT
> > ifnull(fname, ""),
> > ifnull(lname, ""),
> > ifnull(address, ""),
> > ifnull(address2 , "") .....
> >from pt_info where MRN='" & Form3.intNewMRN
> >
> >hope that helps.[/color]
>
>[/color]

[Cor Ligthert [MVP]]

Re: dbnull error with mysql....

Hi Daniel,

Are you sure that the cases of the datanames are completly right, in
windowforms these names are case sensitive.

I hope this helps,

Cor

[wink martindale]

Re: dbnull error with mysql....

Dennis,

Thanks for the response.
I am only returning one record with this query. I am able to get some
values, usually the first three or four, from the reader, but then I
start getting dbnull errors. I am certain the fields contain data. I
even ran the query in the mySQL query analyzer, and it fails there as
well. Help!!

On Thu, 25 Aug 2005 17:04:03 -0700, Dennis
<Dennis@discuss ions.microsoft. com> wrote:
[color=blue]
>Perhaps your first record being read contains all DBNull's. You might try
>using a DataSet and see if the first record is in fact all Nulls.[/color]

[wink martindale]

Re: dbnull error with mysql....

Cor,

Thanks for the repy. Yes, I am certain the case used is correct. Still
cannot figure out why I am getting the "cast from DBNull to String"
error. There is data in the fields! Help!

On Fri, 26 Aug 2005 08:02:02 +0200, "Cor Ligthert [MVP]"
<notmyfirstname @planet.nl> wrote:
[color=blue]
>Hi Daniel,
>
>Are you sure that the cases of the datanames are completly right, in
>windowforms these names are case sensitive.
>
>I hope this helps,
>
>Cor
>[/color]

[wink martindale]

Re: dbnull error with mysql....

Okay,

I am no longer getting dbnull errors (for some odd reason)
But now, I have a new problem:

Here is a code snippet:

strSql = ("Select * from pt_info where MRN ='" & Form3.intNewMRN &
"'")
myCmd = New MySqlCommand(st rSql, myCon3)
myReader3 = myCmd.ExecuteRe ader
Do While myReader3.Read( )
txtTest.Text &= myReader3("fnam e")
txtTest.Text &= myReader3("lnam e")
txtTest.Text &= myReader3("midd le")
txtTest.Text &= myReader3("addr ess") <---stops here
txtTest.Text &= myReader3("addr ess2")
txtTest.Text &= myReader3("city code")
txtTest.Text &= myReader3("zipc ode")
txtTest.Text &= myReader3("zip4 ")
txtTest.Text &= myReader3("stat e_code")
Loop

Now, I cannot get any value from the db after address!?! These fields
contain data, but I can't get them out! I have deleted and rebuilt the
table to no avail. I am completely baffeled!

Help, oh wise ones!!


Wink

[Cor Ligthert [MVP]]

Re: dbnull error with mysql....

Wink,

Did you try it with a
\\\
If Not x Is Nothing then
move
Else
y = ""
End if
//

In your program,

Cor
"wink martindale" <wink@martindal e.com> schreef in bericht
news:hvfug1hv8h 3jh8gk7o7720b3t uvuk39vdj@4ax.c om...[color=blue]
> Cor,
>
> Thanks for the repy. Yes, I am certain the case used is correct. Still
> cannot figure out why I am getting the "cast from DBNull to String"
> error. There is data in the fields! Help!
>
> On Fri, 26 Aug 2005 08:02:02 +0200, "Cor Ligthert [MVP]"
> <notmyfirstname @planet.nl> wrote:
>[color=green]
>>Hi Daniel,
>>
>>Are you sure that the cases of the datanames are completly right, in
>>windowforms these names are case sensitive.
>>
>>I hope this helps,
>>
>>Cor
>>[/color]
>[/color]