VB6 How to insert into a table from resultset as it is when field is null

Question

CREATE TABLE [dbo].[TABLE001]
    (
    [T001_int] [int] NULL,
    [T001_char] [char](100) NULL,
    [T001_decimal] [decimal](5, 0) NULL
    )

CREATE TABLE [dbo].[TABLE002]
    (
    [T002_int] [int] NULL,
    [T002_char] [char](100) NULL,
    [T002_decimal] [decimal](5, 0) NULL
    )

INSERT INTO TABLE002 VALUES (NULL, NULL, NULL)

TABLE DEFINITION ABOVE

    Dim strQuery    As String
    Dim rdoEnv      As rdoEnvironment
    Dim rdoCon      As rdoConnection
    Dim rdoRS       As rdoResultset

    strQuery = ""
    strQuery = strQuery & " SELECT * FROM TABLE002"

    Set rdoEnv = rdoEnvironments(0)
    Set rdoCon = rdoEnv.OpenConnection("", rdDriverNoPrompt, True, MyCon)
    Set rdoRS = rdoCon.OpenResultset(strQuery, rdOpenKeyset, rdConcurReadOnly)

    Do Until rdoRS.EOF = True

        strQuery = ""
        strQuery = strQuery & " INSERT INTO TABLE001"
        strQuery = strQuery & " VALUES"
        strQuery = strQuery & " (" & rdoRS!T002_int & ", '" & rdoRS!T002_char & "'," & rdoRST002_decimal & ")"

        rdoCon.Execute (strQuery)
        rdoRS.MoveNext

    Loop

Above is my VB6 code

When rdoRS!T002_int = null, error occurs

When rdoRS!T002_char = null, inserted as ''

When rdoRS!T002_decimal = null, error occurs

I want to insert NULL as NULL

How can I fix this

Please don't tell me that I should not do this.

I want to know how to insert as null.

Please don't make more work for other people by vandalizing your posts. By posting on Stack Overflow, you've granted a non-revocable right, under the [CC BY-SA 3.0 license](https://creativecommons.org/licenses/by-sa/3.0), for SO to distribute that content. By SO policy, any vandalism will be reverted. If you want to know more about deleting a post, please take a look at [How does deleting work?](https://meta.stackexchange.com/q/5221) — , Jan 29 '19 at 09:29

Robert Kock · Accepted Answer · 2018-12-17T11:15:29.940

1

You could try this:

Dim int_value As String
Dim str_value As String
Dim dec_value As String

int_value = IIf(IsNull(myrs!T002_int), "NULL", CStr(myrs!T002_int))
str_value = IIf(IsNull(myrs!T002_char), "NULL", "'" & myrs!T002_char & "'")
dec_value = IIf(IsNull(myrs!T002_decimal), "NULL", CStr(myrs!T002_decimal))

strQuery = ""
strQuery = strQuery & " INSERT INTO TABLE001"
strQuery = strQuery & " (T001_int, T001_char, T001_decimal)"
strQuery = strQuery & " VALUES"
strQuery = strQuery & " (" & int_value & ", " & str_value & ", " & dec_value & ")"

Some notes:

When inserting, always specify the columns.
You'll get an error in case the T002_char column contains a quote or something else that messes up the SQL syntax.
It's better to use prepared statements.

edited Dec 17 '18 at 11:15

answered Dec 17 '18 at 09:58

Robert Kock

5,795
1
12
20

No, one [should not try this](https://stackoverflow.com/q/332365/11683). – GSerg Dec 17 '18 at 10:07
@GSerg of course you're right: I just wanted to solve the OP's tiny problem, forgetting about the huge mistake.... – Robert Kock Dec 17 '18 at 10:10
when field is null, I cant do cstr(field). – グジョンミン Dec 17 '18 at 10:12
The statement checks whether the field is null. If so, it returns the string 'NULL'. Otherwise, it converts the value into a string. – Robert Kock Dec 17 '18 at 10:19
As @GSerg stated: use a prepared statement instead of a plain string in order to avoid nasty problems. – Robert Kock Dec 17 '18 at 10:26
Updated the answer. – Robert Kock Dec 17 '18 at 11:15

VB6 How to insert into a table from resultset as it is when field is null

1 Answers1