Can't figure out syntax error problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Can't figure out syntax error problem

Author

Topic

ahouse
Starting Member

27 Posts

Posted - 2007-07-12 : 17:25:03

Well guys, I've been fighting with this error for some time now and I've finally given up in hopes that someone out there has the solution for me. I'm sure there will be many questions but I have to start somewhere.
Here is the .asp file I have created followed by the stored procedure and finally the error message received after clicking the submit button.
All I am trying to do, is update the skipcomments field in my locates table with the new skipcomment. I am passing in the old one and allowing it to be modified before clicking the submit button. The even harder thing to swallow is the fact that on my test server this seems to function flawlessly, but then my actual server, I get this error. I can't find anything that looks different between the two. Any help is greatly appreciated!!

Andrew

<%@ LANGUAGE="VBSCRIPT" %>



<% dim accountID
dim getComment
accountID = Request.QueryString("id")
getComment = Request.QueryString("comments")
%>
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Edit Skip Comments</title>
</head>

<body>

<form action="EditComment.asp" method ="post">
Account #: <br /><input type = "text" name = "accountNum" size="10" READONLY value="<%=accountID%>" ><br />
Skip Comments:<br /> <textarea rows ="3" cols="31" name="skipComments" wordwrap="hard"><%=getComment%>
</textarea><br />
<input type = "submit" value = "Save Comments" />
<%
dim account
dim comment
dim fixComment
account = Request.Form("accountNum")
comment = Request.Form("skipComments")
fixComment = "EXEC dbo.usp_UpdateSkipComments @account = '" & account & "', @comment = '" & comment & "'"
Conn.Execute(fixComment)
%>

Here is the stored procedure I am calling
CREATE PROCEDURE usp_UpdateSkipComments

@account varchar(10),
@comment varchar(2000)

AS

UPDATE Locates SET SkipComments = @comment
WHERE Account = @account
GO

After clicking submit button, this error occurs:

An Error of the type "Microsoft OLE DB Provider for ODBC Drivers" has occurred

There is no need to contact us about this error as we are already aware. This is for information only.
Regards
The vera Web Master

Error Number -2147217900
Error Code
Error Description [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 's'.
ASP Description
Category Microsoft OLE DB Provider for ODBC Drivers
File Name /EditComment.asp
Line Number 33
Column -1
Error Source

The form values (if any) are:

Name Value
accountNum XXXXXXXXXX
skipComments XXXXXXXXXXXXXX

The session variable values (if any) are:

Name Value
PreviousAccount
CurrentAccount 5000107138
EndClock 7/12/2007 3:55:59 PM
StartClock 7/12/2007 3:56:07 PM

ahouse
Starting Member

27 Posts

Posted - 2007-07-13 : 11:51:15

Ok it looks like the problem was that I had an " ' " in my comments string which was causing the error.

so the comment being passed looked like

Andrew's comments

comments = 'Andrew's comments' the apostrophe is the problem. So my next question, how do I differentiate between the possessive apostrophe in my name and the end of the comment?

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-07-13 : 12:14:32

quote:
Originally posted by ahouse
comment = Request.Form("skipComments")
fixComment = "EXEC dbo.usp_UpdateSkipComments @account = '" & account & "', @comment = '" & comment & "'"

This should work


comment = Request.Form("skipComments")
comment = Replace(comment,"'","''") 
fixComment = "EXEC dbo.usp_UpdateSkipComments @account = '" & account & "', @comment = '" & comment & "'"

Thanks
Karunakaran

ahouse
Starting Member

27 Posts

Posted - 2007-07-13 : 12:43:02

Thank you so much for the help!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-13 : 14:10:48

No no no ... *never* concatenate stuff together like this ... always, always, always use parameters. Do not construct EXEC sql statements and stuff strings and escape characters all in there, simply call the stored procedure directly referencing it by name and passing in the appropriate parameters. this is shorter, faster, simpler, safer (no sql injection) and more accurate -- no conversions or escaping or anything is required, and it is also the correct way to do it. Please give it a shot. There is no reason to do things with concatenation.

see:

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

for more on this. That explains dynamic SQL; for stored procedures, it's even easier.

1) create your ADODB command
2) be sure the CommandType is adCmdStoredProc
3) set the commandText to the name of the stored proc
4) Add parameters. (read up on ADO for more on this)

That's it. No EXEC. No concatenation. No escaping. No converting everything to a string. No issues with NULL. Always, always, always use parameters. The #1 rule of accessing a database from a client.

- Jeff
http://weblogs.sqlteam.com/JeffS

ahouse
Starting Member

27 Posts

Posted - 2007-08-07 : 10:23:11

Thank you for the lesson - I am just a beginner and I really appreciate the information you have provided. I will be trying the parameters thing today then.

My only question is in this statement

Dim cm As New SqlCommand("", YourConnection)

What is the first set of ("", this argument doing?
And I'm not understanding YourConnection - do I have to make a seperate connection to the database like this?

Dim cm As New SqlCommand("", Conn.Open"UID=" & "myID" & ";PWD=mypw" etc...?

Thanks again for the help. I feel enlightened

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-07 : 10:44:33

Are you using ASP or ASP.NET? The article's example is in ASP.NET. However, the article is not a "cut and paste" type of article; it is talking about a concept using some examples. The overall *concept* -- always using parameters -- is what is important, not the code samples.

- Jeff
http://weblogs.sqlteam.com/JeffS

ahouse
Starting Member

27 Posts

Posted - 2007-08-07 : 12:47:00

Yes I see how the concept of parameters greatly increases security as well as simplifying code and resources. I am using ASP not ASP.NET. I will play around with it a little while and see what I can come up with.

Andrew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources