• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

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)
0
NCSO
Asked:
NCSO
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
nickhoggardCommented:
Hi,

If you want to use the above approach then you could add a function in to check the value of each parameter as you go.  Note that this function would also need to add the quote marks, otherwise the NULL would end up within the quotes.

private function CheckValue(inputValue as string) as string
  if len(inputValue) = 0 then return "NULL"
  return "'" + inputValue + "'"
end function

INSERT INTO ...
VALUES (CheckValue(dr(0).ToString, CheckValue(dr(1).ToString ...)

Another approach to look into would be using a DataAdapter
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vboriDataAdapters.asp (Data Adapters)

Hope this helps

Cheers

Nick
0
 
carmodykCommented:
Use this.  This function has helped me out greatly.

    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) & "," & PrepareStr(dr(1).ToString) & "," & PrepareStr(dr(2).ToString) & "," & PrepareStr(dr(3).ToString) & "," & PrepareStr(dr(4).ToString) & "," & PrepareStr(dr(5).ToString) & "," & PrepareStr(dr(6).ToString) & "," & PrepareStr(dr(7).ToString) & "," & PrepareStr(dr(8).ToString) & "," & PrepareStr(dr(9).ToString) & "," & PrepareStr(dr(10).ToString) & "," & PrepareStr(dr(11).ToString) & "," & PrepareStr(dr(12).ToString) & "," & PrepareStr(dr(13).ToString) & "," & PrepareStr(dr(14).ToString) & "," & PrepareStr(dr(15).ToString) & "," & PrepareStr(dr(16).ToString) & "," & PrepareStr(dr(17).ToString) & "," & PrepareStr(dr(18).ToString) & "," & PrepareStr(dr(19).ToString) & "," & PrepareStr(dr(20).ToString) & "," & PrepareStr(dr(21).ToString) & "," & PrepareStr(dr(22).ToString) & "," & PrepareStr(dr(23).ToString) & "," & PrepareStr(dr(24).ToString) & ")")
            myRCommand = New SqlCommand(strSQL, myRConnection)
0
 
NCSOAuthor Commented:
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
NCSOAuthor Commented:
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-780496d9e6ed','7f1e9c83-3f16-42f3-9c58-3e3bc24f9558','11ad57b3-48c1-44b7-9dce-40194783b7ca','Offense Incident - (Original)','True','33029850-882d-4bfe-8ad0-b42770c2b89a','NULL','09a745a6-1dad-482e-9632-5bbea612b839','','','','','','','','','','','24a225e2-0fad-460f-8e07-0da8832ea443','','','http://128.10.192.200/Primary+Reports/Offense+Incident-Original&rs:Format=PDF&rs:Command=Render&rc:Parameters=false&Incident_ID={11AD57B3-48C1-44B7-9DCE-40194783B7CA}','','False','7/13/2006 9:01:11 AM')

Having the word NULL still gives the same error as before...

Am I missing something?

tnx
~j
0
 
nickhoggardCommented:
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
0
 
carmodykCommented:
Did you use my strSQL value?  Don't place he Quotes around the string in your main statement, let the PrepareStr Function do that work for you.  If there is a value in the string, then the function w will put the quotes around it like so: 'ValueA'.  If there is no value then the Function will just return the value Null, no quotes to go around the value null in a SQL statement.

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) & "," & PrepareStr(dr(1).ToString) & "," & PrepareStr(dr(2).ToString) & "," & PrepareStr(dr(3).ToString) & "," & PrepareStr(dr(4).ToString) & "," & PrepareStr(dr(5).ToString) & "," & PrepareStr(dr(6).ToString) & "," & PrepareStr(dr(7).ToString) & "," & PrepareStr(dr(8).ToString) & "," & PrepareStr(dr(9).ToString) & "," & PrepareStr(dr(10).ToString) & "," & PrepareStr(dr(11).ToString) & "," & PrepareStr(dr(12).ToString) & "," & PrepareStr(dr(13).ToString) & "," & PrepareStr(dr(14).ToString) & "," & PrepareStr(dr(15).ToString) & "," & PrepareStr(dr(16).ToString) & "," & PrepareStr(dr(17).ToString) & "," & PrepareStr(dr(18).ToString) & "," & PrepareStr(dr(19).ToString) & "," & PrepareStr(dr(20).ToString) & "," & PrepareStr(dr(21).ToString) & "," & PrepareStr(dr(22).ToString) & "," & PrepareStr(dr(23).ToString) & "," & PrepareStr(dr(24).ToString) & ")")
0
 
NCSOAuthor Commented:
argh, yeah... quotes....

please both of you suggest an answer so I can split the points between the two of you...

and thanks...

~j
0
 
YZlatCommented:
try DBNull.value
0
 
carmodykCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now