Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-07-13
9
Medium Priority
?
335 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

660 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