Datacolumn casting with null problem

[cpopham]

I have an if statement to check to see if a value in my datacolumn is null. If it is null then I set my variable equal to "", if the value in my datacolumn is not null then I assign its value to my string. There is only one problem when I attempt this. If the value is null, I get either an error message stating that null can not be "" or if I use the word null instead of "", I get the message that null is no longer used and to use System.DBNull, but when I try System.DbNull, it tells me that I can not use it this way. I have tried everything that I can think of. Does anyone have any ideas of how to check the datacolumn for a null?

 

Here is my code:

                   If drUpd("CustomerID") = "" Then
                       strCID = ""
                   Else
                       strCID = CStr(drUpd("CustomerID"))
                   End If

 

Thanks, Chester

[jccorner]

Here is my code:

                   If drUpd("CustomerID") = "" Then
                       strCID = ""
                   Else
                       strCID = CStr(drUpd("CustomerID"))
                   End If

 

 

Have you tried this??

 

   if IsDBNull(drUpd("CustomerID")) or drUPD("CustomerID").ToString.Length = 0 Then
         strCID = ""
   else
         strCID = drUPD("CustomerID").ToString
   endif

[cpopham]

Okay thank, that works. Now that leads to my next problem. How do I set a parameter to dbnull? This is my next code:

 

If strCID = "" Then

    prmUpdID.Value = dbnull 

Else

    prmUpdID.Value = strCID

End IF

[/Code]

 

However, as you probably already know, I can not use dbnull in this fashion. How can I set the Parameter Value to Null?

 

Thanks,

 

Chester

[jccorner]

I'm not sure about setting a value to null, have you tried:

 

prmUPDID.Value = Null

 

Otherwise I would just clear the value.

 

Also one thing, when you check strCID = "" I've found that strCID.Length = 0 works better for those instances when strCID = " " (includes a space).

[cpopham]

yeah I tired that, but it still give me an error.

[kejpa]

prmUPDID.Value = DBNull.Value

 

HTH

/Kejpa

[cpopham]

Okay that works, but as with everything else it has led to another problem. Now I am getting a message that says the field can not be a zero length. My database alllows nulls. I set the parameter to allow nulls also with this statement:

 

      prmUpdReg = New OleDbParameter("@Region", Data.OleDb.OleDbType.Char, 15, "Region")
       prmUpdReg.IsNullable = True

 

Now I do not see why I am getting that message. everything should be set to allow nulls. Anyone else have any ideas?

 

Chester

[kejpa]

Now I am getting a message that says the field can not be a zero length.

Sounds like you're using an Access database. For some mystrious and obscure reason text fields in Access have two properties that (in my sense) have conflicting behaviour. AllowNull which defaults to true and AllowZeroLength which defaults to false....

I've always thought that null values have zero length, but I guess the makers of Access disagree.

Anyway, if you have access to the database, set AllowZeroLength to true for the indicated textfield, there is a way of doing it codewise too but I dare not to type how since I've only done it once or twice a couple of employers ago.

 

HTH

Kejpa