Incorrect Syntax when inserting into A db

Question

I am using EXCEL macros/vba to insert data from the xls sheet into a SQL database.

Can anyone explain why I am getting this error message...

INCORRECT SYNTAX NEAR 'OPEN'

The code I run is an insert statement

 Dim insert As String

 insert = "INSERT INTO dbo.HEADER 
    (plant, 
    taskno, 
    tskstatus, 
    category, 
    opened, 
    openedby, 
    title) 
 VALUES 
    ( 'UK', 
    (select Max(taskno) AS count from HEADER)+ 1), 
    'open', 
    'RFSA', 
    CONVERT(datetime,'" & strdate & "',103), 
    '" & UserId & "', 
    '" & strtitle & "')"

The code inserts the following into a SQL database.

'OPEN' is just a value I am trying to insert into a collumn..

Amelie Turgeon · Accepted Answer · 2014-01-16 14:55:22Z

2

Your parenthesis don't match.

insert = "INSERT INTO dbo.HEADER 
    (plant, 
    taskno, 
    tskstatus, 
    category, 
    opened, 
    openedby, 
    title) 
 VALUES 
    ( 'UK', 
    (select Max(taskno) AS count from HEADER)+ 1, 
    'open', 
    'RFSA', 
    CONVERT(datetime,'" & strdate & "',103), 
    '" & UserId & "', 
    '" & strtitle & "')"

answered Jan 16, 2014 at 14:55

Amelie Turgeon

961 gold badge1 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-01-16 14:54:10Z

1

You want to use insert . . . select syntax:

INSERT INTO dbo.HEADER(plant, taskno, tskstatus, category, opened, openedby, title) 
    SELECT 'UK', Max(taskno) + 1, 'open', 'RFSA', 
           CONVERT(datetime,'" & strdate & "',103), 
           '" & UserId & "', 
           '" & strtitle & "'
    from Header;

However, because you are using SQL Server, you want taskno to be an identity column. This will automatically increment it on each insert.

answered Jan 16, 2014 at 14:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Incorrect Syntax when inserting into A db

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related