Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Converting int to varchar

Reply
Thread Tools

Converting int to varchar

 
 
ll
Guest
Posts: n/a
 
      12-06-2007
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
 
Reply With Quote
 
 
 
 
Adrienne Boswell
Guest
Posts: n/a
 
      12-06-2007
Gazing into my crystal ball I observed ll <(E-Mail Removed)>
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

 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      12-06-2007
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/...e36562fee7804e

.... 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.


 
Reply With Quote
 
ll
Guest
Posts: n/a
 
      12-06-2007
On Dec 6, 10:17 am, "McKirahan" <(E-Mail Removed)> wrote:
> "ll" <(E-Mail Removed)> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Urgent Converting varchar to ntext in SQL =?Utf-8?B?U2lsZWVzaA==?= ASP .Net 2 11-12-2004 12:35 AM
Error converting data type varchar to numeric - stored procedures luna ASP .Net 1 02-06-2004 01:23 PM
int main(int argc, char *argv[] ) vs int main(int argc, char **argv ) Hal Styli C Programming 14 01-20-2004 10:00 PM
storing radio button values in database - binary, int or varchar?? Hazzard ASP .Net 4 07-09-2003 12:59 PM
dirty stuff: f(int,int) cast to f(struct{int,int}) Schnoffos C Programming 2 06-27-2003 03:13 AM



Advertisments