Converting int to varchar

[ll]

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")%>

Would it be possible to CAST the request string to varchar?

Thanks
Louis

 

[Adrienne Boswell]

Gazing into my crystal ball I observed ll <[email protected]>
writing in @e6g2000prf.googlegroups.com:

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")%>

Would it be possible to CAST the request string to varchar?

Thanks
Louis

Seems you have it backwards. The field is looking for INT, and 071-213
is not an INT. You can break it apart cint(left(field,instr(field,"-")-
1)) and cint(mid(field,instr(field,"-")+1))

 

[Bob Barrows [MVP]]

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

%>

 

[ll]

If Request ("id") is a string then enclose it in single quotes; as in:

<%strSQL = "SELECT * FROM AMS where MinutesID = '" & Request ("id") & "'"%>

Many thanks for all of your help with this - this did the job. Will
look into the other solution, as well.
-L