filling a string with a null value

[michelleHEC]

I need to insert into sql either a string or a null value based on an if statement before the insert statement.
here is my code.
If AcctNo(intC) = "18410" Then
TrExpCode = "VMISC"
Else
TrExpCode = DBNull.value (here is the problem)
End If
***cut to the insert statement*****
sqlchk = "Insert Into PRTran (TRExpCode) values('" & TrExpCode & "')"

please any help would be much appreciated thanks
michelle

 

[chiph]

Strings are value types, and under .net v1.1, cannot hold a database null value (.net v2.0 fixes this somewhat).

What you do is write two conversion methods -- one to convert an empty string to a dbnull, and another to go the opposite way (dbnull to String.Empty).

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[michelleHEC]

chip, I guess I am a little slow, could you explain that in code? I tried setting the string to empty but still couldnt set it to dbnull. i am lost.

 

[ThatRickGuy]

public function ReturnNull(value as string) as object
  dim objReturn as object
  if value = string.empty then 
    objReturn =dbnull.value
  else
    objReturn = value
  end if

  return objReturn
end if

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chiph]

A slight optimization:

Public Function StringToNull(value As string) As object
  Return IIf(value.Trim().Length = 0, DBNull.value, value)
End Function

and the opposite:

Public Function NullToString(value As object) As String
  Return IIf(value = DBNull.value, String.Empty, value.ToString())
End Function

Comparing against the length property of a string is more efficient that comparing the contents for equality.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first