Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Adding NULL value to SQL from vb.net...

Posted on 2006-07-13
9
Medium Priority
?
345 Views
Last Modified: 2010-04-23
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
Comment
Question by:NCSO
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 5

Assisted Solution

by:nickhoggard
nickhoggard earned 1000 total points
ID: 17102676
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
 
LVL 6

Expert Comment

by:carmodyk
ID: 17102692
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
 

Author Comment

by:NCSO
ID: 17102757
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:NCSO
ID: 17102792
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
 
LVL 5

Expert Comment

by:nickhoggard
ID: 17102827
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
 
LVL 6

Accepted Solution

by:
carmodyk earned 1000 total points
ID: 17102839
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
 

Author Comment

by:NCSO
ID: 17102997
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
 
LVL 35

Expert Comment

by:YZlat
ID: 17103015
try DBNull.value
0
 
LVL 6

Expert Comment

by:carmodyk
ID: 17103044
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question