Solved

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

Posted on 2006-07-13
9
319 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
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!

 

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

Technology Partners: 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

Suggested Solutions

Title # Comments Views Activity
Data organization issue 7 56
Need help with a query 14 55
Convert a string to date 4 40
Script to transfer files from web server to sharePoint server 3 34
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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