I have developed a class for reading from and writing to an MS SQL Server (v7) database, using forms as intermediaries for updating data.
The class reads the data from the server, inserts into and array together with other data from the table/View. This is then used to populate forms in Access.
The problem I am having is not with the class per se, but with the SQL string used to update and insert data.
The class has Addnew and Update methods which take the new or updated data from the array and use it to generate a SQL string which is then used to update the SQL database using the ADO connection object as in MyConn.execute SQLString
This works in most cases. However, where it falls down is when I have a long bit of text in a memo field. For instance:
the string:
"Insert into vOrderLines ([lngFID],[intDefNo],[strDefectDesc],[strDefectCode],[intUnits],[baseSORCost],[itemCost],[costXVat],[strTrade],[strJob]) Values (cast(81 as Int),cast(1 as smallInt),'Renew bath complete with white pressed steel bath, including taps, bath panels and frame, to match existing or as specified, all pipework and fittings, earth bonding, make good to tiled surround and seal joint to wall','PD4010',cast(1 as smallInt),cast(304.33 as float),cast(25 as float),cast(25 as float),'Bath, basin, sink','Job') SELECT @@IDENTITY AS 'Identity'"
Works with no problem. The memotext (beginning 'Renew bath...') is 217 chars long.
The SQLString:
"Insert into vOrderLines ([lngFID],[intDefNo],[strDefectDesc],[strDefectCode],[intUnits],[baseSORCost],[itemCost],[costXVat],[strTrade],[strJob]) Values (cast(81 as Int),cast(2 as smallInt),'Rebuild chimney in new brick up to 3 courses below lowest roof level, point and refit pots, tiles, slates, cowls, lead flashing, cables and wiring, renew pots, cowls, flue liners, lead flashing as necessary and clear all debris from roof and gutters. 1 f ','ES7051',cast(1 as smallInt),cast(562.11 as float),cast(456 as float),cast(456 as float),'Chimneys','Job') SELECT @@IDENTITY AS 'Identity'"
fails with a -2147217900 error, - "Unclosed quotation mark before the character string 'Rebuild chimney in new brick up to 3 courses below lowest roof level, point and refit pots, tiles, slates, cowls, lead flashing, cables and wiring, renew pots, cowls, flue liners, lead flashing as necessary and clear all debris from roof and gutters. 1 f'."
The memotext here is 247 chars long.
At first, I thought it might be a poblem with the string itself, but after using debug.print to extract it and pasting it into SQL Query Analyser, it worked on its own. Also, pasting it into a pass-through query with the Return Records property set to No, also worked. Using DAO to alter the pass-through queries SQL parameter in case of error failed because only part of the string was fed through. This is where I think the error is - Access/DAO/ADO is truncating my string.
What I would like to know is if there is a work-around that will allow my, otherwise successful class, to work in these cases?
thanks
Bill
The class reads the data from the server, inserts into and array together with other data from the table/View. This is then used to populate forms in Access.
The problem I am having is not with the class per se, but with the SQL string used to update and insert data.
The class has Addnew and Update methods which take the new or updated data from the array and use it to generate a SQL string which is then used to update the SQL database using the ADO connection object as in MyConn.execute SQLString
This works in most cases. However, where it falls down is when I have a long bit of text in a memo field. For instance:
the string:
"Insert into vOrderLines ([lngFID],[intDefNo],[strDefectDesc],[strDefectCode],[intUnits],[baseSORCost],[itemCost],[costXVat],[strTrade],[strJob]) Values (cast(81 as Int),cast(1 as smallInt),'Renew bath complete with white pressed steel bath, including taps, bath panels and frame, to match existing or as specified, all pipework and fittings, earth bonding, make good to tiled surround and seal joint to wall','PD4010',cast(1 as smallInt),cast(304.33 as float),cast(25 as float),cast(25 as float),'Bath, basin, sink','Job') SELECT @@IDENTITY AS 'Identity'"
Works with no problem. The memotext (beginning 'Renew bath...') is 217 chars long.
The SQLString:
"Insert into vOrderLines ([lngFID],[intDefNo],[strDefectDesc],[strDefectCode],[intUnits],[baseSORCost],[itemCost],[costXVat],[strTrade],[strJob]) Values (cast(81 as Int),cast(2 as smallInt),'Rebuild chimney in new brick up to 3 courses below lowest roof level, point and refit pots, tiles, slates, cowls, lead flashing, cables and wiring, renew pots, cowls, flue liners, lead flashing as necessary and clear all debris from roof and gutters. 1 f ','ES7051',cast(1 as smallInt),cast(562.11 as float),cast(456 as float),cast(456 as float),'Chimneys','Job') SELECT @@IDENTITY AS 'Identity'"
fails with a -2147217900 error, - "Unclosed quotation mark before the character string 'Rebuild chimney in new brick up to 3 courses below lowest roof level, point and refit pots, tiles, slates, cowls, lead flashing, cables and wiring, renew pots, cowls, flue liners, lead flashing as necessary and clear all debris from roof and gutters. 1 f'."
The memotext here is 247 chars long.
At first, I thought it might be a poblem with the string itself, but after using debug.print to extract it and pasting it into SQL Query Analyser, it worked on its own. Also, pasting it into a pass-through query with the Return Records property set to No, also worked. Using DAO to alter the pass-through queries SQL parameter in case of error failed because only part of the string was fed through. This is where I think the error is - Access/DAO/ADO is truncating my string.
What I would like to know is if there is a work-around that will allow my, otherwise successful class, to work in these cases?
thanks
Bill
