NCSO
asked on
Adding NULL value to SQL from vb.net...
Hi,
I have this service that executes the code below, and it's all good if all the fields have a value... However, some of the Uniqueidentifier fields does not always have a value and needs to be left blank in the Database. When I launch the insert I get this error:
"tblOIReport - Conversion failed when converting from a character string to uniqueidentifier"
I understand why I get the error, but how do I get NULL into it?
I know I could build the string up dynamically, but this is just one of 16 inserts, some of them with over 200 fields in it. There's got to be a better way...
I tried inserting dbnull.value.tostring, I tried dr(x).tostring and without .tostring....
tnx
~j
strSQL = HandleApps("INSERT INTO tblOIReport (UniqueID, ReportID, Incident_ID, ReportName, Original, Offense1, Offense2, VW1, VW2, MPSUS, MP, Drug1, Drug2, Drug3, Veh, Condition1, Condition2, Condition3, Property, Document, Evidence, ReportPath, ACrit, WrittenRemotely, DateAdded) VALUES ('" & dr(0).ToString & "','" & dr(1).ToString & "','" & dr(2).ToString & "','" & dr(3).ToString & "','" & dr(4).ToString & "','" & dr(5).ToString & "','" & dr(6).ToString & "','" & dr(7).ToString & "','" & dr(8).ToString & "','" & dr(9).ToString & "','" & dr(10).ToString & "','" & dr(11).ToString & "','" & dr(12).ToString & "','" & dr(13).ToString & "','" & dr(14).ToString & "','" & dr(15).ToString & "','" & dr(16).ToString & "','" & dr(17).ToString & "','" & dr(18).ToString & "','" & dr(19).ToString & "','" & dr(20).ToString & "','" & dr(21).ToString & "','" & dr(22).ToString & "','" & dr(23).ToString & "','" & dr(24).ToString & "')")
myRCommand = New SqlCommand(strSQL, myRConnection)
I have this service that executes the code below, and it's all good if all the fields have a value... However, some of the Uniqueidentifier fields does not always have a value and needs to be left blank in the Database. When I launch the insert I get this error:
"tblOIReport - Conversion failed when converting from a character string to uniqueidentifier"
I understand why I get the error, but how do I get NULL into it?
I know I could build the string up dynamically, but this is just one of 16 inserts, some of them with over 200 fields in it. There's got to be a better way...
I tried inserting dbnull.value.tostring, I tried dr(x).tostring and without .tostring....
tnx
~j
strSQL = HandleApps("INSERT INTO tblOIReport (UniqueID, ReportID, Incident_ID, ReportName, Original, Offense1, Offense2, VW1, VW2, MPSUS, MP, Drug1, Drug2, Drug3, Veh, Condition1, Condition2, Condition3, Property, Document, Evidence, ReportPath, ACrit, WrittenRemotely, DateAdded) VALUES ('" & dr(0).ToString & "','" & dr(1).ToString & "','" & dr(2).ToString & "','" & dr(3).ToString & "','" & dr(4).ToString & "','" & dr(5).ToString & "','" & dr(6).ToString & "','" & dr(7).ToString & "','" & dr(8).ToString & "','" & dr(9).ToString & "','" & dr(10).ToString & "','" & dr(11).ToString & "','" & dr(12).ToString & "','" & dr(13).ToString & "','" & dr(14).ToString & "','" & dr(15).ToString & "','" & dr(16).ToString & "','" & dr(17).ToString & "','" & dr(18).ToString & "','" & dr(19).ToString & "','" & dr(20).ToString & "','" & dr(21).ToString & "','" & dr(22).ToString & "','" & dr(23).ToString & "','" & dr(24).ToString & "')")
myRCommand = New SqlCommand(strSQL, myRConnection)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Nick,
I had already tried the function with this:
Private Function DetectField(ByVal myStr As String) As String
If myStr = "" Then
DetectField = Nothing
Else
DetectField = "'" & myStr & "'"
End If
End Function
I tried setting it to Nothing (as above), NULL (which will actually just try to insert the string NULL :)
no luck with any of them...
Do you have an example of how to use the dataadapters?
tnx
~j
I had already tried the function with this:
Private Function DetectField(ByVal myStr As String) As String
If myStr = "" Then
DetectField = Nothing
Else
DetectField = "'" & myStr & "'"
End If
End Function
I tried setting it to Nothing (as above), NULL (which will actually just try to insert the string NULL :)
no luck with any of them...
Do you have an example of how to use the dataadapters?
tnx
~j
ASKER
Hi Carmodyk,
Your code produces this Insert Statement:
INSERT INTO tblOIReport (UniqueID, ReportID, Incident_ID, ReportName, Original, Offense1, Offense2, VW1, VW2, MPSUS, MP, Drug1, Drug2, Drug3, Veh, Condition1, Condition2, Condition3, Property, Document, Evidence, ReportPath, ACrit, WrittenRemotely, DateAdded) VALUES ('d12ed55b-1159-429e-ad57-
Having the word NULL still gives the same error as before...
Am I missing something?
tnx
~j
Your code produces this Insert Statement:
INSERT INTO tblOIReport (UniqueID, ReportID, Incident_ID, ReportName, Original, Offense1, Offense2, VW1, VW2, MPSUS, MP, Drug1, Drug2, Drug3, Veh, Condition1, Condition2, Condition3, Property, Document, Evidence, ReportPath, ACrit, WrittenRemotely, DateAdded) VALUES ('d12ed55b-1159-429e-ad57-
Having the word NULL still gives the same error as before...
Am I missing something?
tnx
~j
Hi,
So long as you dont end up with quotes around the NULL, the insert statement should not insert the string NULL into the database, it should go in as an empty field in the database.
So it should end up looking something like:
INsert into ...
values( '1', 'abc', NULL, 'abc' ...
Be aware that setting the value to nothing will only set the local variable to nothing - it will not affect the value going into the database being null or not.
Cheers
Nick
So long as you dont end up with quotes around the NULL, the insert statement should not insert the string NULL into the database, it should go in as an empty field in the database.
So it should end up looking something like:
INsert into ...
values( '1', 'abc', NULL, 'abc' ...
Be aware that setting the value to nothing will only set the local variable to nothing - it will not affect the value going into the database being null or not.
Cheers
Nick
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
argh, yeah... quotes....
please both of you suggest an answer so I can split the points between the two of you...
and thanks...
~j
please both of you suggest an answer so I can split the points between the two of you...
and thanks...
~j
try DBNull.value
The only thing that is really different between the both of our answers is that mine checks for quotes in your strings and repaces them with ones that SQL will accept. For example, if you store an error message like: Value 'ValueA' not found, my function will make sure that the string is converted to: Value ''ValueA'' not found. so that SQL doesn't think the string is three strings, instead of one.
Public Function PrepareStr(ByVal strValue As String) As String
If Not strValue = Nothing Then
strValue = strValue.Replace("'", "''")
End If
If (strValue = Nothing) Or (strValue.Trim() = "") Then
Return "NULL"
Else
Return "'" & strValue.Trim() & "'"
End If
End Function
strSQL = HandleApps("INSERT INTO tblOIReport (UniqueID, ReportID, Incident_ID, ReportName, Original, Offense1, Offense2, VW1, VW2, MPSUS, MP, Drug1, Drug2, Drug3, Veh, Condition1, Condition2, Condition3, Property, Document, Evidence, ReportPath, ACrit, WrittenRemotely, DateAdded) VALUES (" & PrepareStr(dr(0).ToString)
myRCommand = New SqlCommand(strSQL, myRConnection)