CODEWITHSUNDEEP
sql-serverasp-classicvbscriptado
Steven Rumbalski
Steven Rumbalski

Reputation: 45541

SQL Server 2008 error: Incorrect sytax near a character that's not in my statement

Using vbscript to process a form in classic ASP, I create the following insert statement:

INSERT INTO [RemitChangeControl].[dbo].[FormData]
VALUES
(142, 2, 'asdfasdf');

I get this error:

Microsoft SQL Server Native Client 10.0 error '80040e14'

Incorrect syntax near '<'.

This strange because no where in my insert statement is the character '<' found. So how could there be an error near a non-existent character?

Oddly enough, if I create the same statement by including it directly in my code as a literal string, it works correctly.

Upvotes: 0

Views: 3536

Answers (2)

Steven Rumbalski
Steven Rumbalski

Reputation: 45541

Code I used for debugging another issue caused my problem. Specifically, I misunderstood VBScript parameter passing and assignment semantics.

Here's the code that runs the insert statement:

insert_statement = INSERT INTO [RemitChangeControl].[dbo].[FormData]" & vbcrlf _
    & "VALUES " & vbcrlf _
    & join(value_clauses.Items, "," & vbcrlf) & ";"

write_to_web_page insert_statement ' OOPS!
objconn.Execute insert_statement

Here's the function that it was calling:

sub write_to_web_page(s)
    ' assigment to s mutates it!
    s = Replace(Server.HTMLEncode(s), vbcrlf, "<br />")
    response.write("<p>" & t & "</p>")
end sub

When I pass insert_statement to write_to_web_page it is passed by reference. When I reassign the result of Replace to the local variable it mutates in the caller, resulting in insert_statment containing a number of "<br />". That's what I get for expecting Python assignment semantics in VBScript.

I finally figured this out while chopping my code down to size to flesh out my question here. Suprisingly, my small example did not exhibit the bug. I then inserted calls to write_to_web_page to figure out what was going on. Then my bug reappeared and the light bulb went on.

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

Here's a quick tutorial on setting up a trace using SQL Server Management Studio and Profiler:

http://www.petri.co.il/monitoring-with-sql-profiler.htm

Upvotes: 2

Related Questions