Odd problem with insert statement 1

[sixtoedsloth]

Hi,

I have a sql statement that doesnt work.
the error is -2147217900 (error near '(')

vb6 sp5, sql server sp3.

the sql works when pasted into query analyser.
ive tried single and double quotes
square brackets/no square brackets round the fieldnames

any ideas, its driving me insane??

Russ


sql = INSERT INTO scanbox ([SCAN_ID],[MACHINE_ID],[DTI_NO],[PLU],[ORDER_NO],[PALLET_SERIAL],[PACK_DATE],[WEIGHT],[SCANNED_DATE],[SCANNED_TIME],[OPERATOR_ID],[STOCK_CODE],[PROG_VERSION],[BATCH_NO],[TRANSFER_ORDNUM],[HAULIER_KEY],[WARE_ID],[LOC_ID],[USE_BY_DATE],[SPLIT_FROM_DTI],[KILL_DATE],[CARCASE_SERIAL],[ORG_CARCASE_SPECIES],[ORG_CARCASE_TYPE],[ORG_OWN_GRADE],[ORG_EURO_GRADE],[IS_CARCASE],[LOT],[BARCODE],[QTY],[ORIG_PALLET_SERIAL],[INNER_PACKS],[FILLER]) VALUES ( '0300235719','03','00235719',1352,28852,132519,'2004-01-15',16.28,'2004-01-18',803,'AF','1352','scan001 v7.47',4853,0,'','B1','DEFAULT','2004-01-22','','',0,'','','','','','','',0,0,2,'')

 

[MarkSweetland]

sql = INSERT INTO scanbox ([SCAN_ID],[MACHINE_ID],[DTI_NO],[PLU],[ORDER_NO],
[PALLET_SERIAL],[PACK_DATE],[WEIGHT],[SCANNED_DATE],
[SCANNED_TIME],[OPERATOR_ID],[STOCK_CODE],[PROG_VERSION],
[BATCH_NO],[TRANSFER_ORDNUM],[HAULIER_KEY],[WARE_ID],
[LOC_ID],[USE_BY_DATE],[SPLIT_FROM_DTI],[KILL_DATE],
[CARCASE_SERIAL],[ORG_CARCASE_SPECIES],[ORG_CARCASE_TYPE],
[ORG_OWN_GRADE],[ORG_EURO_GRADE],[IS_CARCASE],[LOT],
[BARCODE],[QTY],[ORIG_PALLET_SERIAL],[INNER_PACKS],[FILLER])
[red]SELECT[/red] '0300235719','03','00235719',
1352,28852,132519, '2004-01-15',16.28, '2004-01-18', 803,'AF', '1352','scan001 v7.47',4853,0,'','B1', 'DEFAULT','2004-01-22','','', 0,'','','','','','','',0,0,2,''


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[sixtoedsloth]

Hi Mark,

Now the message is "error near SELECT"
what the heck am i doing wrong?

an ive never seen that syntax (using select)
but hey, thats not saying much ;->

Regards,


Russ

 

[MarkSweetland]

Are you sure the whole statement is encompassed with double quotes?


sql = "INSERT INTO scanbox " & _
" ([SCAN_ID],[MACHINE_ID],[DTI_NO],[PLU]," & _
" [ORDER_NO], [PALLET_SERIAL],[PACK_DATE],[WEIGHT], " & _
" [SCANNED_DATE], [SCANNED_TIME],[OPERATOR_ID], " & _
" [STOCK_CODE],[PROG_VERSION], [BATCH_NO],[TRANSFER_ORDNUM], " & _
" [HAULIER_KEY],[WARE_ID], [LOC_ID],[USE_BY_DATE], " & _
" [SPLIT_FROM_DTI], [KILL_DATE], [CARCASE_SERIAL], " & _
" [ORG_CARCASE_SPECIES], [ORG_CARCASE_TYPE], [ORG_OWN_GRADE], " & _
" [ORG_EURO_GRADE],[IS_CARCASE],[LOT],[BARCODE], " & _
" [QTY],[ORIG_PALLET_SERIAL],[INNER_PACKS],[FILLER]) " & _
" SELECT '0300235719','03','00235719', 1352, 28852, 132519, " & _
" '2004-01-15',16.28, '2004-01-18', 803,'AF', '1352', " & _
" 'scan001 v7.47',4853,0,'','B1', DEFAULT','2004-01-22', " & _
" '','', 0,'','','','','','','',0,0,2,''"


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[SonOfEmidec1100]

Assuming the string is enclosed in double quotes it looks ok to me as shown. One possiblity is in your code you have an extra single quote at this point VALUES '(

 

[sixtoedsloth]

for debugging I am writing the sql statement out to a text file just before i execute it, so what you see is a cut and paste of the string. Also it works fine, if i paste it into query analyser! i am at a loss, i have been tearing my hair out for days now!

Regards,

Russ

 

[MarkSweetland]

I created a table in MySQl and executed the statement as written with no errors, which you have done in your analyzer. So I believe your problems may lie in the method you are using to execute the query--I assume ADO? Because ADO has a limitation on the number of bytes you can use to send the command string, and your statement is pretty long, I think the command string is being truncated between the .execute() and what is actually provided to be executed on the server. Hence the "error near ')'... " when you try to execute a legitimate statement.

You could try creating a stored procedure that accepts the column data as parameters and does the insert into your table.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[MarkSweetland]

After thinking about it, I'm not sure that the ADO command has a length (within reason) restriction. I confuse myself sometimes.

But I still think your statement is being truncated somewhere...


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[zemp]

Have you tried the method outlined in faq709-1526?

Might be faster, certainly safer for your data and you don't have to worry about the single quote.

Take Care,

zemp

"If the grass looks greener... it's probably because there is more manure."

 

[sixtoedsloth]

Thankyou MarkSweetland!

it was indeed being truncated.

it was my own stupid fault with a typo
a new install of vb6 and i hadnt set option explicit
to always be on and was adding two different strings
in the cmd.execute and the correct two in the debug.print

i would never have found it if you hadnt answered, because my code is always correct, never needs checking and it must be a fault with vb, ado or SQL ;->

thankyou and a star!