ADO exception with character combination inside string...

We have banged our heads against this all day without coming to grips
with what is causing the problem....

We have an MSSQL2000 database with both text and varchar columns in
some tables. Of course there is lots of other stuff as well but this
problem is with these data types.

We also have various Delphi7 programs that operate on this database.
These programs are set up to trap the exception in an ADO operation
and log the query text that was in use when the exception happened.
This system has been runnig for years but today a customer reported
the error described below...

What happens is that if we have a string to store inside the database
that looks like this:
'Some random text "fix:problem" here'
then we will get an exception when we send it in through a query via
an ADO connection.
If we change the string like this:
'Some random text ""fix:problem" here'
or
'Some random text "fix problem" here'
then the text is successfully stored. In the case of the double "" it
will be stored as 2 " characters in the database...

The rule for database exception seems to be:
- string contains a " character
- Somewhere after the " there is also a : character

The rule does not hold if the " char is doubled ("")

We also tried the query that failed directly in QueryAnalyzer (thanks
to our error log we can get the complete query text from the log).
Then it *succeeds* without problem and our string gets stored! So the
query itself is not a problem!

Then we tried with VisualBasic6 also via an ADO query, also here the
execution succeeded!

Finally we tried to compile our application in BDS2006 figuring that
something in the D7 ADO system needs fixing and that it might be fixed
in BDS2006. But no, the error occurs exactly in the same way also
here. :-(

So it seems to me like the ADO system in Delphi7 and BDS2006 somehow
are sensitive to the " char if followed by a : char later in the text!
Why???????

Is there anyone else who has seen this and is there a remedy for the
problem? Our customer really needs to specify some data with the
quoted string and inside the quotes he must have the colon....

.

Relevant Pages

  • RE: ODBC query in VB code Need HELP
    ... Everything for ADO is in the first 2 messages that I gave you. ... Since your pass-through query already exists (including the ... Dim STRSQL As String ...
    (microsoft.public.access.formscoding)
  • Re: mailmerge and sql
    ... means that you will not be able to see them in a database you open using the ... I believe you may have to use DAO instead of ADO to ... then creates a View containing a UNION query. ... Dim oCatalog As ADOX.Catalog ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Bitwise And
    ... I don't know how to create queries using ADO. ... ANSI query looks like in the MSysObjects table. ... > ANSI queries in a Non-Ansi database. ...
    (microsoft.public.access.queries)
  • Row Level Locking from Excel VBA query
    ... There are multiple users accessing the database at ... query that I am using so that two users will not get the same order ... Dim dbsConn As ADODB.Connection ... Dim connString As String ...
    (microsoft.public.access.queries)
  • Changing ODBC Connection between development and production
    ... Public Function ExecAgingReport(datToDate As String, ... Set db = CurrentDb ' Setting the database as current Database ... pass-through query ... qd.ReturnsRecords = True ' Query does retrieve records ...
    (microsoft.public.access.modulesdaovba)