Storing NULL value ?????

[keithinuk]

Hello
I'm trying to store a null value in a column defined as Int32 with nulls allowed. I'm using SQL 2000 and VB.net.

NULL is not valid in .net so I've tried the following in my SQL
- System.DBNull.Value
- NullValue where NullValue is defined as
Public NullValue As System.DBNull = DBNull.Value

The SQL statement is constructed as follows in a public function with all datarefs passed to it.

Dim qs As New System.Text.StringBuilder("&quot
qs.Append("INSERT INTO opmember &quot
qs.Append("(opmember_operation, opmember_workgroup, opmember_batched, &quot
qs.Append("opmember_double_entry,opmember_confirm, opmember_stop_access, &quot
qs.Append("opmember_account_access, opmember_isin_access, opmember_cheque_access) &quot
qs.Append("VALUES(" & dataRef1)
qs.Append(", " & dataRef2)
qs.Append(", " & Quote & dataRef3 & Quote)
qs.Append(", " & Quote & dataRef4 & Quote)
qs.Append(", " & Quote & dataRef5 & Quote)
If dataRef6 = "" Then
qs.Append(", " & NullValue)
Else
qs.Append(", " & dataRef6)
End If
If dataRef7 = "" Then
qs.Append(", " & NullValue)
Else
qs.Append(", " & dataRef7)
End If
If dataRef8 = "" Then
qs.Append(", " & NullValue)
Else
qs.Append(", " & dataRef8)
End If
If dataRef9 = "" Then
qs.Append(", " & NullValue)
Else
qs.Append(", " & dataRef9)
End If

Any ideas please ?????????

Thanks
Keith In case I forget to say "Thank you" I'll say it now - thank you for your help !!!

 

[JamesLean]

Use the SQL null statement eg:

If dataRef6 = "" Then
   qs.Append(", NULL")
Else
   qs.Append(", " & dataRef6)
End If

--James