I am using ColdFusion to construct a SQL Server insert statement, the statement appears as expected in the debugger and if I cut and paste it into the Query Analyzer it works as expected. Yet from my code it produces the following error:
Database error number 105
[Macromedia][SQLServer JDBC Driver][SQLServer]Unclosed quotation mark before the character string 'kellyc'.
It almost seems as if somehow ColdFusion is introducing the problem. The server is MX, I similarly constructed Insert Statements that are working.
Here is the code...
From my Action Page...Its the first query that is failing.
I'm afraid I don't know a thing about ColdFusion, but here is what I would try: start up SQL Profiler and run a trace on your application. You can probably use the default settings for the trace unless it is a production server and in heavy use (in which case it's a pain to find the specific transaction you're looking for).
You can then identify the format of the T-SQL Statement (or SP) that is being used and test changes in your code to make corrections.
Sorry for the crosspost again, but thought I would post my resolution here as well....
OK, as it often turns out, the problem is not always what it seems from the lovely and informative error messages. The unclosed quote was actually in the second query in the transaction. This came to light for me when I started playing with trying to submit a string with a single quote in it and noticed ColdFusion MX does not display the text of an unsucessful query in the debug. (Personally I consider this a deficiency of MX in case any Macromedia folks want my 2 cents).
So... long story short I realized it wasn't the insert query that was throwing the error and fixed the offending query and it now works like a charm.
Nice of you to post the resolution. This is not meant to be an "I told you so," but it definitely points out an instance where SQL Profiler could have helped to narrow down the cause. Congrats for solving the problem.