ASP/ADO Direct SQL Insert Error With Escaped Quotes

Hello! Hopefully someone can help me with my problem! I'm using ASP/ADO with MSSQL Server 2000 and when I'm using direct SQL to Insert a string to my database I'm getting an error when the Escaped (SQL Encoded) string exceeds the max length of the db column.

I know this sounds straight forward but consider the following example if you will:

For illustrative purposes let's say we have a table with just one column like this:

value1 Varchar(10)

If you open a Query analyzer and perform the following query:

INSERT INTO my_table (value1) Values ('12345678''9')

The Insert is executed and no error occurs. If you query the database and look at the stored value it is displayed as: 12345678'9

The same is true if we're Inserting or Updating the same field using ASP/ADO and a simple stored procedure. ADO automatically escapes the single quotes (in the above example) for you and the Insert/Update is fine.

The PROBLEM and the reason for this long winded post is if you try to use ASP/ADO and direct SQL to perform the same Insert/Update as the example above. Consider the following:

strQuery = "UPDATE my_table SET value1 = '12345678''9'"
Set alphaRS = alphaConn.Execute(strQuery,, 1)

The above ASP code fails because the Length of the SQL Encoded string is greater than 10 characters; it is 11 characters with our escape sequence.

Aside from converting all Direct SQL Insert/Updates to stored procedures or doubling the Max Length's of my database fields, neither of which are acceptable solutions, what can I do?!! Why doesn't ADO handle this?

Many thanks to anyone who maybe be able to offer me a solution!

Cheers!

I see your point. I mocked up a quick test and the results are as you described.

Here is a quick workaround that might be usefull. The quote character in T-SQL has the ASCII value of 39. So you can think of a global replacement function which replaces all occurances of the quote in string with the CHAR(39) function. I tested this and the results were promising.

so your test string will become with this function
Hope this helps. With this you do not need to change your database or convert your direct inserts to stored procedures.

[note]: converting the direct inserts to stored procedures is a preferred solution:
- execution paths of all your queries is known therefore faster execution of your sql stamements,
- security is a lot tighter: you allow only access to stored procedures and not the underlying database
- you could use the robust transaction functionality of the sql server including table and row locking and such.
but that aside [/note]

How interersting/annoying. Seems to me like a bit of a logic error in MS's ADO code. I fail to see a valid reason behind performing the char string length before escaping the string. Odd.