Syntax error in insert

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MP - 03 Feb 2006 16:36 GMT

Hi
Posted this several hours ago to another ng but it never showed up thought
i'd try here.

using vb6, ado, .mdb, jet4.0, no access

given table tblJob with field JobNumber text(10)

'The example I had to go by
'INSERT INTO tblCustomers (CustomerID, [Last Name], [First Name])
' VALUES (1, 'Kelly', 'Jill')

'these dont' work
'TRY WITH PARENS
'TRY WITH QUOTING FIELDNAME
'TRY WITH ENDING SEMICOLON
'sSql = "INSERT into tblJob ('JobNumber'), '05015';"
'sSql = "INSERT into tblJob ('JobNumber') '05015';"
'sSql = "INSERT into tblJob ('JobNumber')'05015';"

'TRY WITHOUT ENDING SEMICOLON
'sSql = "INSERT into tblJob ('JobNumber'), '05015'"
'sSql = "INSERT into tblJob ('JobNumber') '05015'"
'sSql = "INSERT into tblJob ('JobNumber')'05015'"

'TRY WITHOUT QUOTING
'TRY WITH ENDING SEMICOLON
'sSql = "INSERT into tblJob (JobNumber), '05015';"
'sSql = "INSERT into tblJob (JobNumber) '05015';"
'sSql = "INSERT into tblJob (JobNumber)'05015';"
''TRY WITHOUT ENDING SEMICOLON
'sSql = "INSERT into tblJob (JobNumber), '05015'"
'sSql = "INSERT into tblJob (JobNumber) '05015'"
'sSql = "INSERT into tblJob (JobNumber)'05015'"

'TRY WITHOUT PARENS
'sSql = "INSERT into tblJob JobNumber, '05015';"
'sSql = "INSERT into tblJob JobNumber, '05015';"
'sSql = "INSERT into tblJob JobNumber '05015';"
''WITHOUT SEMICOLON
'sSql = "INSERT into tblJob JobNumber, '05015'"
'sSql = "INSERT into tblJob JobNumber '05015'"
'sSql = "INSERT into tblJob JobNumber'05015'"

'TRY WITH QUOTING TABLENAME
'sSql = "INSERT into 'tblJob' JobNumber, '05015';"
'sSql = "INSERT into 'tblJob' JobNumber, '05015';"
'sSql = "INSERT into 'tblJob' JobNumber '05015';"

'sSql = "INSERT into 'tblJob' JobNumber, '05015'"
'sSql = "INSERT into 'tblJob' JobNumber '05015'"
'sSql = "INSERT into 'tblJob' JobNumber'05015';"

'TRY WITH VALUES KEYWORD
'sSql = "INSERT into tblJob ('JobNumber'),Values ('05015')"
'sSql = "INSERT into tblJob('JobNumber'),Values ('05015')"

'sSql = "INSERT into tblJob ('JobNumber'), Values ('05015')"
'sSql = "INSERT into tblJob (JobNumber), VALUES ('05015')"

'sSql = "INSERT into tblJob (JobNumber), VALUES ('05015');"
'sSql = "INSERT into tblJob (JobNumber) VALUES ('05015');"
sSql = "INSERT into tblJob (JobNumber) VALUES ('05015')"

'Error Message: ErrNum: <-2147217900> (Syntax error in INSERT INTO
statement.)

oRefConn is an open connection to mdb
oRefConn.Execute sSql

any help?
Tia
Mark

Reply to this Message

Anthony England - 03 Feb 2006 21:36 GMT

> Hi
> Posted this several hours ago to another ng but it never showed up
[quoted text clipped - 70 lines]
> Tia
> Mark

It looks like you have tried a lot of things, but surely the last is
correct? In fact, you could choose to have the final semi-colon or not so
either of these should work:
sSql = "INSERT into tblJob (JobNumber) VALUES ('05015')"
sSql = "INSERT into tblJob (JobNumber) VALUES ('05015');"

If it still doesn't like it, my only suggestion is that there isn't a field
tblJob.JobNumber or at least not with that spelling. Perhaps there is a
space between Job and Number. Beware if you are looking at the data using
Access, the column may have a heading that is not the name of the field. In
other words the field could be named 'J102' but have a caption property of
JobNumber and this is what it displays when you view the table.

Reply to this Message

MP - 05 Feb 2006 03:24 GMT

> It looks like you have tried a lot of things, but surely the last is
> correct? In fact, you could choose to have the final semi-colon or not so
[quoted text clipped - 8 lines]
> other words the field could be named 'J102' but have a caption property of
> JobNumber and this is what it displays when you view the table.

I am such an idiot.
I had code to create the table(tblJob) with that field(JobNumber) -so I was
sure they were there - but it turned out I was using a filename that already
existed and so the code bypassed the creation sub<vbg>....and the existing
field name was in fact different. Double DUH! I would have expected that
the error message would have been "no such fieldname" ...or something
helpful like that...from now on I'll always lead with an if table-and-field
exists test...just to compensate for my obvious stupidities.

:-)

Thanks for looking at this Anthony.

Mark

Reply to this Message

Anthony England - 05 Feb 2006 16:05 GMT

>> It looks like you have tried a lot of things, but surely the last is
>> correct? In fact, you could choose to have the final semi-colon or not
[quoted text clipped - 30 lines]
>
> Mark

You're welcome. I can see why the error message led you to question the
syntax - it really is quite mis-leading in this case.

Reply to this Message