CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013
    Posts
    27

    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.

  2. #2
    Join Date
    Sep 2018
    Posts
    38

    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.

  3. #3
    Join Date
    Mar 2013
    Posts
    27

    Re: Insert with Null

    I use the replace command and everything working perfect now

  4. #4
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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
  •  





Click Here to Expand Forum to Full Width

Featured