Why doesn't IsDBNull detect DBNull?

[Guest]

This line of code returns error 13, cast from 'DBNull' to type 'String' is not valid

If IsDBNull(Clinics.Clinics.Item(A).Workphone) The

<other code
End I

Clinics.Clinics is a dataset that was loaded using a left join from two MS SQL database tables. The workphone element is null because there was no corresponding record from the right side of the join

The generated code for retrieving the value of 'Workphone' as a dataset property is detecting the DBNull and giving the cast error before the VB application code line can do its check

I have to be missing something - having a function 'IsDBNull' that is not able to check for a DBNull is inconsisten

Ji

 

[William Ryan eMVP]

Anyway, I don't think it's the IsDbNull causing your problem and it can
definitely check for null values. I think the problem is in your Property.
I'd need to see the accessor b/c it won't let you explicitly set a string
value to DbNull, but depending on the property it could end up trying to do
just that resulting in the cast exception. Looking at the usage too and
that exception, all you are doing is an evaluation so the conversion is
being done inside the parens. As such, it's much more likely that it's
causing the exception rather than the use of IsDbNull.

can you first this statement without an exception
MessageBox.Show(Clinics.Clinics.Item(A).WorkPhone) 'Assuming workphone is a
string?

Anyway, it'd help if I could see the accessor, but I'm 99% sure this is the
problem...whatever is being used to set a property of type string is DbNull
and that won't work.

I use IsDbNull all the time to check for fields that are Varchar in the DB,
hence map to System.String for the datacolumn type and have never had a
problem. I really think it's the accessor. If the messagebox command
throws an exception , then we've pretty much ruled out the IsDbNull as the
cause.

This line of code returns error 13, cast from 'DBNull' to type 'String' is not valid.

If IsDBNull(Clinics.Clinics.Item(A).Workphone) Then

<other code>
End If


Clinics.Clinics is a dataset that was loaded using a left join from two MS

SQL database tables. The workphone element is null because there was no
corresponding record from the right side of the join.

The generated code for retrieving the value of 'Workphone' as a dataset

property is detecting the DBNull and giving the cast error before the VB
application code line can do its check.

I have to be missing something - having a function 'IsDBNull' that is not

able to check for a DBNull is inconsistent

 

[Guest]

William

Thank you for the reply

I tried the message box and got the same error. WorkPhone is a string.

What specifically do you mean by the 'accessor'? Are you referring to the SQL statement that retrieves the data or the xml code for the dataset or the MS generated .vb code that gets the values as properties from the dataset or something else that I do not know about? I am including below the xml, the sql statement for the fill, and a snippet from the code that retrieves the data from the dataset. The error 13 is generated furhter down in the code in the same subroutine that does the fill

We may have a very basic misunderstanding of the proper usage of the development environment to define datasets

We use an SQLDataAdapter and reconfigure the GenAdapter selectcommand.commandtext depending on the data we are retrieving. The dataset has been defined by setting the adapter's select command, then generating the dataset. At execution time we do a fill of the appropriate dataset. Here is a copy of the xml from one of the datasets that we are having trouble with

<?xml version="1.0" standalone="yes" ?><xs:schema id="Clinics" targetNamespace="http://www.tempuri.org/Clinics.xsd" xmlns:mstns="http://www.tempuri.org/Clinics.xsd
xmlns="http://www.tempuri.org/Clinics.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified"><xs:element name="Clinics" msdata:IsDataSet="true"><xs:complexType><xs:choice maxOccurs="unbounded"><xs:element name="Clinics"><xs:complexType><xs:sequence><xs:element name="ClinicNum" type="xs:short" /><xs:element name="ClinicID" type="xs:string" /><xs:element name="ShortName" type="xs:string" /><xs:element name="LongName" type="xs:string" /><xs:element name="Root" type="xs:short" /><xs:element name="Group" type="xs:string" /><xs:element name="Discount" type="xs:string" /><xs:element name="License" type="xs:string" /><xs:element name="Doctor" type="xs:string" /><xs:element name="DirectorName" type="xs:string" /><xs:element name="DirectorTitle" type="xs:string" /><xs:element name="webURL" type="xs:string" /><xs:element name="AltContact" type="xs:string" /><xs:element name="AltContactTitle" type="xs:string" /><xs:element name="Billing" type="xs:string" /><xs:element name="Expr1" type="xs:string" /><xs:element name="Expr2" type="xs:dateTime" /><xs:element name="Expr3" type="xs:short" /><xs:element name="Expr4" msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:int" /><xs:element name="Expr5" type="xs:int" /><xs:element name="BtBID" type="xs:int" /><xs:element name="branch" type="xs:string" /><xs:element name="addresstype" type="xs:string" /><xs:element name="addressline1" type="xs:string" /><xs:element name="streetnum" type="xs:string" /><xs:element name="Streetname" type="xs:string" /><xs:element name="Apt" type="xs:string" /><xs:element name="City" type="xs:string" /><xs:element name="State" type="xs:string" /><xs:element name="Postalcode" type="xs:string" /><xs:element name="Latitude" type="xs:string" /><xs:element name="Longitude" type="xs:string" /><xs:element name="County" type="xs:string" /><xs:element name="Homephone" type="xs:string" /><xs:element name="Workphone" type="xs:string" /><xs:element name="FAX" type="xs:string" /><xs:element name="Pager" type="xs:string" /><xs:element name="email" type="xs:string" /><xs:element name="altphone" type="xs:string" /><xs:element name="AltFAX" type="xs:string" /><xs:element name="AltPager" type="xs:string" /><xs:element name="altemail" type="xs:string" /><xs:element name="Startdate" type="xs:dateTime" /><xs:element name="Enddate" type="xs:dateTime" /><xs:element name="Entrydate" type="xs:dateTime" /><xs:element name="EnteredBy" type="xs:short" /></xs:sequence></xs:complexType></xs:element></xs:choice></xs:complexType><xs:unique name="Constraint1" msdatarimaryKey="true"><xs:selector xpath=".//mstns:Clinics" /><xs:field xpath="mstns:Expr5" /><xs:field xpath="mstns:addresstype" /></xs:unique></xs:element></xs:schema

And here is the code that retrieves the data from the dataset This is a snippet from Clinics.v

Public Property Workphone As String
Get
Return CType(Me(Me.tableClinics.WorkphoneColumn),String)
End Get
Set
Me(Me.tableClinics.WorkphoneColumn) = value
End Set
End Property

Finally, here is the sql string and subsequent code used to fill the dataset.

SQLString = "SELECT * FROM clinics "
SQLString = SQLString + " left Join Address on Address.BtBiD=Clinics.BtbID"
SQLString = SQLString + " And AddressType=" + Chr(39) + "CLN" + Chr(39)
SQLString = SQLString + " where Clinics.Branch = " + Chr(39) + Branch + Chr(39)
SQLString = SQLString + " order by shortname"

GenAdapter.SelectCommand.CommandText = SQLString
Clinics.Clinics.Clear()

Try
GenAdapter.Fill(Clinics.Clinics)
Catch e1 As Exception
MsgBox("Error filling ClinicsDataSet" + Chr(10) + e1.Message, MsgBoxStyle.OKOnly)
End Try

 

[Cor Ligthert]

Hi Jim,

Return CType(Me(Me.tableClinics.WorkphoneColumn),String)

A column gives mostly information about the column and not the Item.

I think you want somehting as
\\\
dim my Rownumber as integer = 0
Return me.tableClincs(myRownumber).Workphone
///
However maybe I am wrong?
Cor

 

[Guest]

Hi Cor

This code is regenerated by MS every time there is a change to a dataset. It would be a huge pain to have to make this kind of a change to this code for every field in the dataset every time the dataset is modified

Ji

 

[Cor Ligthert]

Hi Jim,

A dataset is mostly binded using a datasource or with mostly simple controls
direct to a property.

A dataset is basicly this
dataset.tables(x).rows(x).item(x)

What will say that a dataset contains tables which contains rows which
contains items, better said a dataset has references to tables, which have
references to rows, which has references to items.

When you do a dataadapter fill, that means that you fill a datatable (not a
dataset however it can be a table in a dataset).

So when you do not bind it, you will have to tell in my opinion wich row it
is in the datatable when you want a particulary item in a dataset. (And for
that you can use a lot of things by instance the bindingmanager.position)

A little bit confusing I did find it also when I started with the dataset is
that there is talked as well about columns, however a column describes the
type of the items.

I hope this helps?

Cor

 

[William Ryan eMVP]

Your Get/Set statement is what I"m referring to. If the value it's
'getting' from the dataset returns DbNull, and you try returning it, it'll
blow up because the property is of Type String and it can't be converted.
Instead, you might want to do the IsDbNull check inside the Get statement
and if it is DbNull, return sting.Empty for instance.

William,

Thank you for the reply.

I tried the message box and got the same error. WorkPhone is a string.

What specifically do you mean by the 'accessor'? Are you referring to the

SQL statement that retrieves the data or the xml code for the dataset or the
MS generated .vb code that gets the values as properties from the dataset or
something else that I do not know about? I am including below the xml, the
sql statement for the fill, and a snippet from the code that retrieves the
data from the dataset. The error 13 is generated furhter down in the code
in the same subroutine that does the fill.

We may have a very basic misunderstanding of the proper usage of the

development environment to define datasets.

We use an SQLDataAdapter and reconfigure the GenAdapter

selectcommand.commandtext depending on the data we are retrieving. The
dataset has been defined by setting the adapter's select command, then
generating the dataset. At execution time we do a fill of the appropriate
dataset. Here is a copy of the xml from one of the datasets that we are
having trouble with.

<?xml version="1.0" standalone="yes" ?><xs:schema id="Clinics" targetNamespace="http://www.tempuri.org/Clinics.xsd"
xmlns:mstns="http://www.tempuri.org/Clinics.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"

attributeFormDefault="qualified" elementFormDefault="qualified"><xs:element
name="Clinics" msdata:IsDataSet="true"><xs:complexType><xs:choice
maxOccurs="unbounded"><xs:element
name="Clinics"><xs:complexType><xs:sequence><xs:element name="ClinicNum"
type="xs:short" /><xs:element name="ClinicID" type="xs:string" /><xs:element
name="ShortName" type="xs:string" /><xs:element name="LongName"
type="xs:string" /><xs:element name="Root" type="xs:short" /><xs:element
name="Group" type="xs:string" /><xs:element name="Discount" type="xs:string"
/><xs:element name="License" type="xs:string" /><xs:element name="Doctor"
type="xs:string" /><xs:element name="DirectorName" type="xs:string"
/><xs:element name="DirectorTitle" type="xs:string" /><xs:element
name="webURL" type="xs:string" /><xs:element name="AltContact"
type="xs:string" /><xs:element name="AltContactTitle" type="xs:string"
/><xs:element name="Billing" type="xs:string" /><xs:element name="Expr1"
type="xs:string" /><xs:element name="Expr2" type="xs:dateTime" /><xs:element
name="Expr3" type="xs:short" /><xs:element name="Expr4"
msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:int"
/><xs:element name="Expr5" type="xs:int" /><xs:element name="BtBID"
type="xs:int" /><xs:element name="branch" type="xs:string" /><xs:element
name="addresstype" type="xs:string" /><xs:element name="addressline1"
type="xs:string" /><xs:element name="streetnum" type="xs:string"
/><xs:element name="Streetname" type="xs:string" /><xs:element name="Apt"
type="xs:string" /><xs:element name="City" type="xs:string" /><xs:element
name="State" type="xs:string" /><xs:element name="Postalcode"
type="xs:string" /><xs:element name="Latitude" type="xs:string"
/><xs:element name="Longitude" type="xs:string" /><xs:element name="County"
type="xs:string" /><xs:element name="Homephone" type="xs:string"
/><xs:element name="Workphone" type="xs:string" /><xs:element name="FAX"
type="xs:string" /><xs:element name="Pager" type="xs:string" /><xs:element
name="email" type="xs:string" /><xs:element name="altphone" type="xs:string"
/><xs:element name="AltFAX" type="xs:string" /><xs:element name="AltPager"
type="xs:string" /><xs:element name="altemail" type="xs:string"
/><xs:element name="Startdate" type="xs:dateTime" /><xs:element
name="Enddate" type="xs:dateTime" /><xs:element name="Entrydate"
type="xs:dateTime" /><xs:element name="EnteredBy" type="xs:short"
/></xs:sequence></xs:complexType></xs:element></xs:choice></xs:complexType><
xs:unique name="Constraint1" msdatarimaryKey="true"><xs:selector

 

[Guest]

Cor

Sorry but that did not help

Ji

 

[Cor Ligthert]

Hi Jim,

Strange, I did try this (with some different names, for that can be typos_
\\\
Me.SqlDataAdapter1.Fill(DataSet12)
If IsDBNull(DataSet12.Tables(0).Rows(0)("ident2")) Then
MessageBox.Show("First")
End If
Dim myfield1 As String = DataSet12.Tables(0).Rows(0).ToString
Try
Dim myfield2 As String = DataSet12.pers(0).ident2
Catch ex As System.Data.StrongTypingException
MessageBox.Show("Second")
End Try
///

It did show in the messagebox first First and then Second

I do not understand why this does not work for you?

Cor