Solved

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

Posted on 2006-07-13
9
326 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 5

Assisted Solution

by:nickhoggard
nickhoggard earned 250 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 250 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

624 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