Converting int to varchar

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

Re: Converting int to varchar

Gazing into my crystal ball I observed ll
writing in news:f51781c7-6748-4099-94fe-71a6625f2656
@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))

--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Re: Converting int to varchar

ll wrote:
> 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.inetser ver.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

%>


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Converting int to varchar

"ll" wrote in message
news:f51781c7-6748-4099-94fe-71a6625f2656@e6g2000prf.googleg roups.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?

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

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

Re: Converting int to varchar

On Dec 6, 10:17 am, "McKirahan" wrote:
> "ll" wrote in message
>
> news:f51781c7-6748-4099-94fe-71a6625f2656@e6g2000prf.googleg roups.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?
>
> 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