Using QODBC with ALPHA5, I can succesfully insert invoice (header and lines) except when I include any Dates, so I can only assume my date format is incorrect.

Below are the error codes and variations I have tried.

Code=195.000000,Text="Database API specific error"#0d#0a#0d#0a#09"Your database has

returned the following error code and description to Alpha Five."#0d#0a#09"Consult your

database documentation for further information."#0d#0a#0d#0a"11030 - '[QODBC] Insert value

must be a simple value"#0d#0a"SQL State is:

42S00',NativeCode=11030.000000,NativeText="[QODBC] Insert value must be a simple

value"#0d#0a"SQL State is:

42S00,Syntax=,API=,RowsAffected=0.000000,Success=F,Error=T,Canceled=F,Warnings=F

INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,

BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,

BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES

("800000F3-1197629414","80000055-1197631694","OP/1030616","Shaldon Road                   

","Newton Abbot                    ","Devon                           ","                  

             ","XXX 4SQ                        ","UK",0,"80000006-1197628269",2007-12-11,"

",0)

------------------------------------------------------------------------------------------

Code=195.000000,Text="Database API specific error"#0d#0a#0d#0a#09"Your database has

returned the following error code and description to Alpha Five."#0d#0a#09"Consult your

database documentation for further information."#0d#0a#0d#0a"11015 - '[QODBC] Expected

lexical element not found: <identifier>"#0d#0a"SQL State is:

42000',NativeCode=11015.000000,NativeText="[QODBC] Expected lexical element not found:

<identifier>"#0d#0a"SQL State is:

42000,Syntax=,API=,RowsAffected=0.000000,Success=F,Error=T,Canceled=F,Warnings=F

INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,

BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,

BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES

("800000F3-1197629414","80000055-1197631694","OP/1030616","Shaldon Road                   

","Newton Abbot                    ","Devon                           ","                  

             ","XXX 4SQ                       

","UK",0,"80000006-1197628269",{d'2007-12-11'}," ",0)

-------------------------------------------------------------------------------------------

Code=28.000000,Text="Error parsing SQL statement"#0d#0a#0d#0a#09"Your database has returned

the following error code and description to Alpha Five."#0d#0a#09"Consult your database

documentation for further information."#0d#0a#0d#0a"514 - 'line 1:513: unexpected char:

'{'',NativeCode=514.000000,NativeText="line 1:513: unexpected char:

'{',Syntax=,API=,RowsAffected=0.000000,Success=F,Error=T,Canceled=F,Warnings=F

INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,

BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,

BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES

("800000F3-1197629414","80000055-1197631694","OP/1030616","Shaldon Road                   

","Newton Abbot                    ","Devon                           ","                  

             ","XXX 4SQ                       

","UK",0,"80000006-1197628269",{'2007-12-11'}," ",0)

-------------------------------------------------------------------------------------------

Code=195.000000,Text="Database API specific error"#0d#0a#0d#0a#09"Your database has

returned the following error code and description to Alpha Five."#0d#0a#09"Consult your

database documentation for further information."#0d#0a#0d#0a"11024 - '[QODBC] Invalid

operand for operator: <assignment>"#0d#0a"SQL State is:

42S00',NativeCode=11024.000000,NativeText="[QODBC] Invalid operand for operator:

<assignment>"#0d#0a"SQL State is:

42S00,Syntax=,API=,RowsAffected=0.000000,Success=F,Error=T,Canceled=F,Warnings=F

INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,

BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,

BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES

("800000F3-1197629414","80000055-1197631694","OP/1030616","Shaldon Road                   

","Newton Abbot                    ","Devon                           ","                  

             ","XXX 4SQ                       

","UK",0,"80000006-1197628269",'2007-12-11'," ",0)

QODBC is looking for:

INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,
BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,
BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted)
VALUES ('800000F3-1197629414', '80000055-1197631694', 'OP/1030616', 'Shaldon Road',
'Newton Abbot', 'Devon', ' ', 'XXX 4SQ', 'UK' ,0, '80000006-1197628269', {d'2007-12-11'} ,' ', 0)

but you can't do a insert into the Invoice table without doing cached InvoiceLine inserts first.

See: How can I create multiple lines in the Invoice, Purchase, Journal and other tables? for more.

Thank you for your reply.

My invoice header insert statement is executed after the cached Invoice lines, and as I said if I dont include a date it works correctly.

Unless my eyes deceive me my attempt:-

INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,

BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,

BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES

("800000F3-1197629414","80000055-1197631694","OP/1030616","Shaldon Road                   

","Newton Abbot                    ","Devon                           ","                  

             ","XXX 4SQ                       

","UK",0,"80000006-1197628269",{d'2007-12-11'}," ",0)

Has the date in the format you have posted but results in "Expected lexical element not found: "

Here are the results after I changed the quotes.   I have included the invoice line statement and result.

INSERT INTO InvoiceLine( InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate,

InvoiceLineAmount, FQSaveToCache ) VALUES ("80000014-1197629891","900.326             ",   

     6,       322,1)

Code=0.000000,Text="Success,NativeCode=0.000000,NativeText=,Syntax=,API=,RowsAffected=1.000

000,Success=T,Error=F,Canceled=F,Warnings=F

INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,

BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,

BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES

('800000F3-1197629414','80000055-1197631694','OP/1030616','Shaldon Road                   

','Newton Abbot                    ','Devon                           ','                  

             ','XXX 4SQ                       

','UK',0,'80000006-1197628269',{d'2007-12-11'},' ',0)

Code=195.000000,Text="Database API specific error"#0d#0a#0d#0a#09"Your database has

returned the following error code and description to Alpha Five."#0d#0a#09"Consult your

database documentation for further information."#0d#0a#0d#0a"11015 - '[QODBC] Expected

lexical element not found: <identifier>"#0d#0a"SQL State is:

42000',NativeCode=11015.000000,NativeText="[QODBC] Expected lexical element not found:

<identifier>"#0d#0a"SQL State is:

42000,Syntax=,API=,RowsAffected=0.000000,Success=F,Error=T,Canceled=F,Warnings=F

Bearing in mind that my INSERT statement works fine with double quotes, if no date is used, and that the line INSERT results in "Success=T", the changing of the double quotes to single quotes didnt seem likely to have much effect.   However, one never knows for sure so I changed them.

INSERT INTO InvoiceLine( InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate,

InvoiceLineAmount, FQSaveToCache ) VALUES ('80000014-1197629891','900.326             ',   

     6,       322,1)

Code=0.000000,Text="Success,NativeCode=0.000000,NativeText=,Syntax=,API=,RowsAffected=1.000

000,Success=T,Error=F,Canceled=F,Warnings=F

INSERT INTO Invoice( CustomerRefListID, ARAccountRefListID, RefNumber, BillAddressAddr1,

BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode,

BillAddressCountry, IsPending, TermsRefListID, ShipDate, Memo, IsToBePrinted) VALUES

('800000F3-1197629414','80000055-1197631694','OP/1030632','Shaldon Road                   

','Newton Abbot                    ','Devon                           ','                  

             ','XXX 4SQ                       

','UK',0,'80000006-1197628269',{d'2007-12-12'},' ',0)

Code=195.000000,Text="Database API specific error"#0d#0a#0d#0a#09"Your database has

returned the following error code and description to Alpha Five."#0d#0a#09"Consult your

database documentation for further information."#0d#0a#0d#0a"11015 - '[QODBC] Expected

lexical element not found: <identifier>"#0d#0a"SQL State is:

42000',NativeCode=11015.000000,NativeText="[QODBC] Expected lexical element not found:

<identifier>"#0d#0a"SQL State is:

42000,Syntax=,API=,RowsAffected=0.000000,Success=F,Error=T,Canceled=F,Warnings=F

 Same result as before.

Please look at the end of the QODBC Message log by clicking on "Review QODBC Messages" for the real errors and what the actual problem is with the SQL Statements you are executing.

The QODBC message log is empty.   I cleared it yesterday and it hasnt added any messages since even though I have been running the process.

However what may be a better clue is from the Trace log.   Please note - this is a snippet of what i believe to be the relevant part.

 INSERT INTO "Invoice"  ("CustomerRefListID", "ARAccountRefListID", "RefNumber", "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity", "BillAddressState", "BillAddressPostalCode", "BillAddressCountry", "IsPending", "TermsRefListID", "ShipDate",
     "Memo", "IsToBePrinted") VALUES ('8000004E-1197628865', '80000055-1197631694', 'OP/1030822', 'Siskin Drive Parkway West       ', 'Middlemarch Business Park       ', 'Coventry                        ', '                                ', 'CV3 4PW 
                           ', 'UK', 0, '80000006-1197628269', {ts '{d'2-00-7- 12:-1:3'.}'}, ' ', 0)

It is looks like its trying to convert the date to time.

"Correct: {ts '{d'2-00-7- 12:-1:3'.}'} is what is giving you a "[QODBC] Expected lexical element not found: " error. "

Yea right I figured that out thanks.   What i dont understand is WHY im getting {ts '{d'2-00-7- 12:-1:3'.}'} in the trace log when im passing {d'2007-12-13''}.   As far as I can see I am passing the date in the format you say it is supposed to be.   I have spent the best part of a day messing with that format to see what the trace log produces and I am no closer to figuring out HOW TO MAKE THIS WORK.

I have managed to insert 540 invoice in my test company but without being able to enter any dates.   Getting the invoices to work took me about 2 hours.   Getting a date to work has so far taken me over a week and its getting dull.

If you are able to tell me WHY my dates arent working id be obliged but at the moment its looking like Im doing the right things and qodbc isnt.

I can only pass qodbc the date in one correct format - if it isnt {d'2007-12-13''} then id really like to know what it is, and if it is {d'2007-12-13''}. then id like to know why it doesnt work.

Thank you.

I am not using a date/time variable because it wouldnt be the correct format..   I am using a string constructed from elements of a date.  I get the {ts in the trace log when I put the date in the format you specify, and I have tried it using your example date cut and pasted - hardcoded - into the insert statement.

If QODBC isnt reformatting it to add the {ts then im at a loss to where it comes from.

I just ran the following test using VB Demo and everything worked correctly:-

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit 1', 1.00000,
1.00, '20000-999022286', 1)

INSERT INTO "Invoice" ("CustomerRefListID", "ARAccountRefListID", "TxnDate",
"RefNumber", "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity",
"BillAddressState", "BillAddressPostalCode", "BillAddressCountry",
"IsPending", "TermsRefListID", "DueDate", "ShipDate", "ItemSalesTaxRefListID",
"Memo", "IsToBePrinted", "CustomerSalesTaxCodeRefListID") VALUES ('470001-1071525403',
'40000-933270541', {d'2007-12-01'}, 'Ship1', 'Brad Lamb', '1921 Appleseed
Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '10000-933272658',
{d'2008-01-01'}, {d'2008-01-01'}, '2E0000-933272656', 'Memo Test', 0,
'10000-999022286')

and created this invoice in QuickBooks: