-
November 14th, 2018, 06:51 AM
#1
Insert with Null
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 into another 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 get error 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 converting from a character string to uniqueidentifier.
How I can solve it?
Thank you.
-
November 15th, 2018, 12:28 AM
#2
Re: Insert with Null
I don't know much about database, Makis, but I believe you can set your unique field to automatic so a unique ID will be inserted with each record. After that's done then perhaps you can alter your loop to simply insert your CODE and Description values first, then create another loop to update your fBaseBOMGID values.
Code:
'loop
NewString = "INSERT INTO LOCAL_Item (field1, field3) VALUES ("'" & Code & "', " & Description & "'")"
'create new loop
NewString = "UPDATE LOCAL_Item SET field2 = " & "'" & fBaseBOMGID & "' WHERE field1 = "'" & CODE & "'"
Then you'll just have a unique number where it's NULL assigned by the database, and the items with IDs will be updated by your loop.
-
November 15th, 2018, 02:31 AM
#3
Re: Insert with Null
I use the replace command and everything working perfect now
-
November 16th, 2018, 08:35 PM
#4
Re: Insert with Null
Hard to say exactly what the issue was as the code that threw the error does not appear to be shown here. My guess would be that you were trying to set a string var= the field value and since strings can not be a null that throws an error, One quick and easy workaround is to simply append an empty string to the end of the field in question as part of the assignment which makes null values into empty strings and avoids any error, likely faster than using a replace unless of course you are using replace in the Select statement then the replace may be faster.
Always use [code][/code] tags when posting code.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|