Syntax error in INSERT INTO Statement

H

Hemil

Hi,

My application has an access front-end and a SQL Server
back-end.

I am trying to use an Insert Statement as following:

sql = "INSERT INTO OrderNote"

sql = sql + " (OrderTypeCode, [LineNo], NoteSeqNo,
PrtOnAck, PrtOnPick, PrtOnInvoice, PrtOnStatement, Note,
OrderHeaderId, OrderDetailId, CreatedDate, CreatedBy)"

sql = sql + " Values ('" + NtRecId + "', " +
NtCustPOLineNo + ", " + NtSeqNo + ", '" + PrintOnAck
+ "', '" + PrintOnPickSlip + "', '" + PrintOnInv + "', '"
+ PrintOnSt + "', '" + MsgTxt + "', '" + OrderHeaderId
+ "' ,'" + OrderDetailId + "', '" + LnUserDt1 + "', '"
+ "TextFileImport')"

Debug.Print sql
db.Execute sql

I get a "Syntax error in INSERT INTO Statement error" on
the code "db.Execute sql".

When I find the value of ?sql in the immediate window I
get:

INSERT INTO OrderNote (OrderTypeCode, [LineNo], NoteSeqNo,
PrtOnAck, PrtOnPick, PrtOnInvoice, PrtOnStatement, Note,
OrderHeaderId, OrderDetailId, CreatedDate, CreatedBy)
Values ('IN', 001, 0001, 'N', 'Y', 'Y', 'N', 'HALF FEED
COILS. REBATE ITEM 1768 - 125 =
1643 ', '158' ,'171', '05/04/2005', 'TextFileImport')

The above insert statement works perfectly alright when
used from SQL Server Query Analyser. But it gives the
error when executed from the access application.

I know that the column 'Note' is causing the problem
because the insert statement works fine from the access
application if I remove the field 'Note' and the
corresponding variable 'MsgTxt'.

The column note in the database is of datatype varchar(50)
and the variable MsgTxt is declared as a 'String' in the
access code. I know this is where it is breaking but dont
understand why and how to resolve it.

Please help. Any ideas will be appreciated.

Thanks & Regards,
Hemil.
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Since you're not using any Access tables, instead of running the query
against the linked table, try an SQL pass-thru (SPT) query. I usually
keep a QueryDef set up as an SPT and stuff it w/ an SQL statement & then
execute it. E.g.:

strSQL = ".... the SQL statement..."

dim db as dao.database
dim qd as dao.querydef
set db = currentdb
set qd = db.querydefs("query name")
qd.SQL = strSQL
qd.Execute dbFailOnError

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlJFOoechKqOuFEgEQJjYQCfcRZwuZhfMeX5ojsjVwB/KpBHYT8An19S
EkGAtj7r2IosltH11J4uWw8I
=aI5h
-----END PGP SIGNATURE-----
 
H

Hemil

Thanks Foster,

This worked! I dont know what was wrong in my code
because I have always used it that way earlier but the
point is your suggestion worked. Thanks a lot.

I will investigate the problem with my code later on when
I have time.

Cheers
Hemil.
-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Since you're not using any Access tables, instead of running the query
against the linked table, try an SQL pass-thru (SPT) query. I usually
keep a QueryDef set up as an SPT and stuff it w/ an SQL statement & then
execute it. E.g.:

strSQL = ".... the SQL statement..."

dim db as dao.database
dim qd as dao.querydef
set db = currentdb
set qd = db.querydefs("query name")
qd.SQL = strSQL
qd.Execute dbFailOnError

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlJFOoechKqOuFEgEQJjYQCfcRZwuZhfMeX5ojsjVwB/KpBHYT 8An19S
EkGAtj7r2IosltH11J4uWw8I
=aI5h
-----END PGP SIGNATURE-----
Hi,

My application has an access front-end and a SQL Server
back-end.

I am trying to use an Insert Statement as following:

sql = "INSERT INTO OrderNote"

sql = sql + " (OrderTypeCode, [LineNo], NoteSeqNo,
PrtOnAck, PrtOnPick, PrtOnInvoice, PrtOnStatement, Note,
OrderHeaderId, OrderDetailId, CreatedDate, CreatedBy)"

sql = sql + " Values ('" + NtRecId + "', " +
NtCustPOLineNo + ", " + NtSeqNo + ", '" + PrintOnAck
+ "', '" + PrintOnPickSlip + "', '" + PrintOnInv + "', '"
+ PrintOnSt + "', '" + MsgTxt + "', '" + OrderHeaderId
+ "' ,'" + OrderDetailId + "', '" + LnUserDt1 + "', '"
+ "TextFileImport')"

Debug.Print sql
db.Execute sql

I get a "Syntax error in INSERT INTO Statement error" on
the code "db.Execute sql".

When I find the value of ?sql in the immediate window I
get:

INSERT INTO OrderNote (OrderTypeCode, [LineNo], NoteSeqNo,
PrtOnAck, PrtOnPick, PrtOnInvoice, PrtOnStatement, Note,
OrderHeaderId, OrderDetailId, CreatedDate, CreatedBy)
Values ('IN', 001, 0001, 'N', 'Y', 'Y', 'N', 'HALF FEED
COILS. REBATE ITEM 1768 - 125 =
1643 ', '158' ,'171', '05/04/2005', 'TextFileImport')

The above insert statement works perfectly alright when
used from SQL Server Query Analyser. But it gives the
error when executed from the access application.

I know that the column 'Note' is causing the problem
because the insert statement works fine from the access
application if I remove the field 'Note' and the
corresponding variable 'MsgTxt'.

The column note in the database is of datatype varchar (50)
and the variable MsgTxt is declared as a 'String' in the
access code. I know this is where it is breaking but dont
understand why and how to resolve it.

Please help. Any ideas will be appreciated.

Thanks & Regards,
Hemil.
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top