Converting int to varchar

Discussion in 'ASP General' started by ll, Dec 6, 2007.

  1. ll

    ll Guest

    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
     
    ll, Dec 6, 2007
    #1
    1. Advertising

  2. 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
     
    Adrienne Boswell, Dec 6, 2007
    #2
    1. Advertising

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

    %>


    --
    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.
     
    Bob Barrows [MVP], Dec 6, 2007
    #3
  4. ll

    ll Guest

    On Dec 6, 10:17 am, "McKirahan" <> wrote:
    > "ll" <> wrote in message
    >
    > news:...
    >
    > > 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
     
    ll, Dec 6, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. luna
    Replies:
    1
    Views:
    13,838
    Kevin Spencer
    Feb 6, 2004
  2. =?Utf-8?B?U2lsZWVzaA==?=

    Urgent Converting varchar to ntext in SQL

    =?Utf-8?B?U2lsZWVzaA==?=, Nov 11, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    15,855
    =?Utf-8?B?U2lsZWVzaA==?=
    Nov 12, 2004
  3. Elmo Watson
    Replies:
    0
    Views:
    2,960
    Elmo Watson
    Mar 29, 2006
  4. Schnoffos
    Replies:
    2
    Views:
    1,220
    Martien Verbruggen
    Jun 27, 2003
  5. Hal Styli
    Replies:
    14
    Views:
    1,646
    Old Wolf
    Jan 20, 2004
Loading...

Share This Page