Syntax error in INSERT INTO Statement

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

 

[Douglas J. Steele]

Try putting square brackets around Notes in the list of fields, like you did
with LineNo.