SQL Statement fails

This is Access 2007 with a SQL Server 2005 back end. Within my application I have a routine called ExecuteSQL() which is used constantly to write strings of data to the SQL Server. It uses the .Execute method of an ADODB.command object  I have noticed today that this routine is failing on a statement which has got CR/LF pairs in the data being written.

However, if I paste the exact same SQL Statement into a Query window in SQL Management Studio, it executes without a problem.

If I then remove the CR/LF pairs, the  statement will execute within MsAccess.

My original statement looks like this:

Insert into RevisionDetails (RevisionRef, FieldName, OldValue,NewValue) Values(22981, 'Payment Details', '', 'Settlement of this invoice preferably using SWIFT
 should be made by paying our Bankers:
The Royal Bank of Scotland plc
International Payment London Operations
P.O. Box 34842 Islington High Street,
 LONDON N1  8XLSwift Address:
 RBOS GB 2LAccount  ')
TownTalkAsked:
Who is Participating?
 
UnifiedISCommented:
Can you do a replace function for your cr/lf?  replace it with a different reference to a new line like the char code or maybe a constant (VB has vbNewLine).
0
 
SStoryCommented:
Can't you use an actual command object and do a parameter query--you know declare a command object, add parameter objects, set the text to a SQL string with parameter holders in it? It should clean up any data that isn't good and might just handle this for you.
0
 
TownTalkAuthor Commented:
@SSTory: My ExecuteSQL() routine is a general purpose routine which is used for writing to any combination of fields in any table. Surely with your approach I would have to write a different flavour of it for each usage?

My application has almost 100 tables in SQL Server. For several years I have had no problem with this routine.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
Please post your ADO code and the error message generated.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Does the Word Wrap *have* to be done in the SQL?

If it were me, I would create the main string, then create the "Wrapped" text segment.
Then concatenate them together at some later stage...


Juts a suggestion....

JeffCoachman
0
 
TownTalkAuthor Commented:
@acperkins: I have a routine called OpenDatabaseConnection which opens the ADO connection and creates the ADO command object.

Public Function ExecuteSQL(Criteria As String) As Boolean
           
    OpenDatabaseConnection
           
    DatabaseCommand.CommandText = Criteria
    DatabaseCommand.Execute
   
    ExecuteSQL=true
End Funtion

Like I said, i've been using this for years. Never had a problem like this before.
The error code is -2147217900
I could understand if we were talking about embedded quotes, but I would have thought that CRLF characters would be ok.
0
 
Anthony PerkinsCommented:
but I would have thought that CRLF characters would be ok.
They are OK.  Provided they are embedded correctly.
0
 
TownTalkAuthor Commented:
Embedded correctly? what does that mean? i've never given them any special treatment previously. My users type the data into a Text Box in MS Access and then it gets saved.
0
 
Anthony PerkinsCommented:
Fair enough.  Not sure how I can help then.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Again,


Does the Line feed *Have * to be done in SQL?
0
 
Ken SelviaRetiredCommented:
What is the width and datatype of your NewValues column? Your inserted text is 257 characters. If your column is 255 characters, the trailing 2 blank spaces may get trimmed in Mgt Studio but not in the ADO connection.
0
 
TownTalkAuthor Commented:
@kselva: In this case the field in the table is a varchar(2500)

@boag2000: I'm just trying to write into the database the characters that the User typed on the screen. But I take your point. I already have a routine to watch out for quote characters and double them up. I could modify that to swap CRLF characters for something else.

@UnifiedIS: I took a look  at vbnewline. In MsAccess it is a string constant containing ascii characters 13 and 10. So it is identical to CRLF
0
 
UnifiedISCommented:
On one that fails, can you loop through and identify every character that is there.  If a user is pasting a value, it may be bringing in something undesired that has no effect on the value.
0
 
TownTalkAuthor Commented:
hmmm.... this is getting stranger. I wrote a quck routine to go through all the records in my PaymentDetails table and make sure there were no characters there that I was unaware of. There weren't any.

Then from within that same routine, I called ExecuteSql() in exactly the same manner that caused the original issue, and I was surprised to find that the data including CRLFs was written to the table without a problem. I went back to the part of the code where the original problem was, and it still fails when trying to write exactly the same data.

Obviously something is different, but I can't see what yet. When I google the error code, most of the articles I am seeing are talking about illegal characters being to blame.
0
 
TownTalkAuthor Commented:
I replaced CRLF pairs with a tilde (~) and all is well now. Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.