By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,018 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Syntax error in insert

P: n/a
MP
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
Feb 3 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"MP" <No******@Thanks.com> wrote in message
news:43***********************@news.nationwide.net ...
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

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.

Feb 3 '06 #2

P: n/a
MP
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.


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
Feb 5 '06 #3

P: n/a
"MP" <no****@Thanks.com> wrote in message
news:zd******************@tornado.rdc-kc.rr.com...
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.


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

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.
Feb 5 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.