Matching NULL with ""

[MikeMV]

Hello,

I am new to VB 2008, I am trying to practice on a small application to update a table containing sales person information, I am trying to setup a check to make sure no one updated the record in the interim before sending the update, I save the current values in a set of oldXXX local variables and populate the changes to a set of newXXX local variables and then check to make sure the current data in the table still matches the data in the old set. This works well, except that sometimes the field in the table is NULL and VB stores this as "", when the comparizon is done based on the code below there is a missmatch. I am wondering what is the best way to return a "match" when the DB is NULL and the VB variable is ""?

Or is there a better way to handle checking for changes?

I will greatly appreciate your feedback.

Dim updateStatement As String _
= "UPDATE SalSalesperson SET " _
& "Name = @NewName, " _
& "Address1 = @NewAddress1, " _
& "Address2 = @NewAddress2, " _
& "City = @NewCity, " _
& "State = @NewState, " _
& "ZipCode = @NewZipCode, " _
& "Phone = @NewPhone, " _
& "Fax = @NewFax " _
& "WHERE Salesperson = @OldSalesperson " _
& "AND Name = @OldName " _
& "AND Address1 = @OldAddress1 " _
& "AND City = @OldCity " _
& "AND State = @OldState " _
& "AND ZipCode = @OldZipCode " _
& "AND Phone = @OldPhone " _
& "AND Fax = @OldFax "

 

[RiverGuy]

The values in a DataRow are returned as Objects. If the value is Null, the Object will be set to System.DBNull.

However, if you are creating variables in your program with data types to try to match the data types in your database, you won't be able to set them to DBNull. You are setting them to an empty string.

So what you should do is declare your variables as Objects instead. For example

Dim Name As Object
Dim Address1 As Object
'Etc

Then, when you set your parameters from these variables, if you had a value of DBNull, it will still be DBNull and the database will recognize this as NULL.

If you need to use these variable objects in other parts of your program, you can still use them, just cast them to a new set of properly typed variables, or cast them on the fly when using them.

 

[MikeMV]

Thanks,

Makes sense, I'll give that a try this morning.

 

[DaveInIowa]

You also may want to look into using nullable data types. I don't know how they interact with database fields, but I thought I'd mention them.

Dim something As Nullable(Of Integer)

'Since you're using VB 2008 this may work for you:
Dim something As Integer?