November 14, 2018 at 4:42 am
Hi
I have a table called Remote_Item with some records like
Code | fBaseBOMGID | Description |
64167 | C5F2EA4A-A374-464B-A001-BBBCA2EAFF8D | Ear Plugs (5 Pairs) |
64082 | Null | Ear Defenders |
27940 | 9CF5E211-9A1F-42EA-B26E-C6693CED200D | EAR PROTECTOR |
93404 | Null | Folding Ear Defenders |
26564 | 7959E6AA-2E52-415E-B6FB-64D6CA2EA280 | SIGILL SPRAY ZINC |
45589 | A7859F70-453B-4F0B-BDBD-339A44488149 | BOLT CUTTERS CRV 900mm |
45885 | 4D396E23-EFE3-43C8-8E2A-090AE8704329 | BOLT CUTTERS CRV 750mm |
45876 | 868A057E-C43C-4B5C-B69D-D33C6ACF69D5 | BOLT CUTTERS CRV 600mm |
45869 | Null | BOLT CUTTERS CRV 450mm |
45852 | D3A74BBC-F947-4583-952D-D8E5FC89C40D | BOLT CUTTERS CRV 350mm |
Code = String
fCodeGID = unique identifier
Description = String
I create a loop so I can read these records and add them intoanother table.
Inside loop I build the insert string
Dim NewString AS String
NewString = "INSERT INTO LOCAL_Item VALUES (" & "'"& Code & "', '" & fBaseBOMGID & "', '"& Description & "'")
When the loop run working fine when all fields have data but I geterror when turns to read a null value…
For example.
I read the first line and sting become like…
INSERT INTO LOCAL_Item VALUES ('64167','C5F2EA4A-A374-464B-A001-BBBCA2EAFF8D', 'Ear Plugs (5 Pairs)')
Working fine….
When I read the second line the string become
INSERT INTO LOCAL_Item VALUES ('64082','Null', 'Ear Defenders')
And I get error for Null value.
I try to use isnull function but I get the error
Conversion failed when convertingfrom a character string to uniqueidentifier.
How I can solve it?
Thank you.
November 14, 2018 at 4:52 am
Why are you doing this row by row instead of as a set.
When you insert a NULL value, it doesn't have inverted commas round it in the INSERT statement. Therefore you will need to take account of that in whatever code you are using to generate your INSERT statement(s).
John
November 14, 2018 at 5:39 am
INSERT INTO LOCAL_Item VALUES ('64082','Null', 'Ear Defenders')
should beINSERT INTO LOCAL_Item VALUES ('64082',Null, 'Ear Defenders')
i.e. you can't have quotes around the Null
November 14, 2018 at 6:17 am
The code is part of VB.net application.
So I do a loop through data table....
It is not easy to control where to put or not put (') depends of null values.
November 14, 2018 at 6:42 am
I find a solution....... I use Cast To convert it to VarcChar
November 14, 2018 at 6:46 am
Firstly, code like the below is open to injection:NewString = "INSERT INTO LOCAL_Item VALUES (" & "'"& Code & "', '" & fBaseBOMGID & "', '"& Description & "'")
Concatenating raw string values leaves you wide open; you should be parametrising your values to avoid this.
The fact that you are using this method and getting an error that the string isn't a valid GUID, however, strongly implies that you're setting the value of fBaseBOMGID to the string value 'NULL', not leaving it as the value NULL. NULL and 'NULL' are not the same (the former is an unknown value, the latter is a 4 character string consisting of the letters N, U, L and L). Is this suspicion correct?
Rather than, somewhere, setting the value of fBaseBOMGID to 'NULL' leave it as NULL, and parametrise your query; you'll likely find the problem goes away then.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 15, 2018 at 12:30 am
Thank you all for the help....
Working fine now...
November 15, 2018 at 2:12 am
makis_best - Thursday, November 15, 2018 12:30 AMThank you all for the help....
Working fine now...
Did you fix the injection issue then by parametrising your query?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply