CODEWITHSUNDEEP
sqlexcelvba
user3013325
user3013325

Reputation: 221

Incorrect Syntax when inserting into A db

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..

Upvotes: 0

Views: 198

Answers (2)

Amelie Turgeon
Amelie Turgeon

Reputation: 96

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 & "')"

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 1

Related Questions