×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Insert with Null

Insert with Null

Insert with Null

(OP)
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.

RE: Insert with Null

Your problem is not uncommon.

The root of the problem is that there are single quotes around null. With single quotes around it, SQL Server sees it as a string containing the word null instead of the value null.

If the data type was varchar, the query would succeed and the word NULL would be inserted. Without the single quotes, the value null would be used instead. Since your column data type is uniqueidentifier, the word NULL is not acceptable.

When the value is null, remove the single quotes.

CODE

INSERT INTO LOCAL_Item VALUES ('64082','Null', 'Ear Defenders') 

CODE

‌‌Dim NewString AS String

NewString = "INSERT INTO LOCAL_Item VALUES (" & "'"& Code & "', '" & fBaseBOMGID & "', '"& Description & "'")

NewString = Replace(NewString, "'Null'", "NULL") 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Insert with Null

So what you have is this:

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
 
If you just want to "read these records and add them into another table", why not simply do:

INSERT INTO LOCAL_Item VALUES (
SELECT Code, fBaseBOMGID, Description From Remote_Item )


Giasas wavey3


---- Andy

There is a great need for a sarcasm font.

RE: Insert with Null

(OP)
Thank you all....

Both are perfect solutions....

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close