ll said:
My data input page uses a id generator which includes dashes, and the
corresponding column in the db is set up as varchar to handle that.
There is a problem, however, when the value in the varchar column is
compared to the string from the URL, and the following error message
occurs:
"Syntax error converting the varchar value '071-213' to a column of
data type int."
<%strSQL = "SELECT * FROM AMS where MinutesID = " & Request ("id")%> Firstly:
http://www.aspfaq.com/show.asp?id=2096
Would it be possible to CAST the request string to varchar?
I'm assuming you are talking about SQL Server ... please disclose your
database type AND VERSION when asking db-related questions. It is almost
always relevant.
Anyways, we cannot debug a sql statement without seeing what it actually
is. Add these lines immediately after the above line and rerun your
page:
Response.Write strSQL
Response.End
Look at the statement written to the browser window. Does it make sense?
Try copying it to the clipboard and pasting it into Query Analyzer to
test it. Does it run there? Without modification? If not, and you cannot
figure it out. show us the sql statement.
Based on the error you are seeing, you should think about how the query
engine is evaluating 071-213. Do you think there might be a chance that
it is subtracting 213 from 071? Your problem is due the the fact that
string literals need to be delimited. If I was going to make the huge
mistake of continuing to use dynamic sql, I would modify your vbscript
statement to:
<%strSQL = "SELECT * FROM AMS where MinutesID = '" & _
Request ("id") & "'"%>
....which will probably work as long as Request ("id") does not contain
apostrophes.
However ... I would not be making such a mistake:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
.... after reading which, you should be eagar to modify your code to look
like this:
<%
strSQL = "SELECT * FROM AMS where MinutesID = ?"
dim arParms
arParms = array(Request ("id"))
set cmd=createobject("adodb.command")
with cmd
.CommandText=sSQL
.CommandType=adCmdText
Set .ActiveConnection=CN
on error resume next
set rs = .Execute(,arParms)
end with
%>