SQL type mismatch - confusing to a beginner

Results 1 to 3 of 3

Thread: SQL type mismatch - confusing to a beginner

  1. #1
    David Freer Guest

    Default SQL type mismatch - confusing to a beginner

    I need to detemine if a string entered by a user matches a text field in an on-line database. To do this, I retrieve a string from a form text field, do a little processing on the string and then try to form an appropriate SQL statement. Several variations are shown below which work but are not useful to me. The one that I need to works invokes a "type-mismatch" error. <BR><BR>Assume that 6C1B9C62615EB481 was enter into the form text field and retrieved into the variable strID using <BR><BR>strID=Request.Form("RegID")<BR><BR>&#039Th e following works fine<BR>SQL="SELECT * FROM Upgrade WHERE RegID=&#039 6C1B9C62615EB481&#039"<BR>set rstUpgrades = conn.execute(SQL)<BR><BR>&#039 The also works fine<BR>strID = "&#039 6C1B9C62615EB481&#039" &#039 works<BR>SQL="SELECT * FROM Upgrade WHERE RegID=" & strID<BR>set rstUpgrades = conn.execute(SQL)<BR><BR>&#039The above work fine but I obviously have "hard-wired" a user <BR>&#039response into the code - no good.<BR><BR>&#039The following fails when I use strID immediately upon retrival.<BR><BR>strID=Request.Form("RegID") &#039 this is what I need to work<BR>SQL="SELECT * FROM Upgrade WHERE RegID=" & strID<BR>set rstUpgrades = conn.execute(SQL) &#039 this fails<BR><BR><BR>&#039So the basic question is why does the following work<BR><BR>strID = "&#039 6C1B9C62615EB481&#039"<BR>SQL="SELECT * FROM Upgrade WHERE RegID=" & strID<BR>set rstUpgrades = conn.execute(SQL)<BR><BR>&#039 and these fail?<BR><BR>strID = "6C1B9C62615EB481" &#039 note the missing &#039 quotes<BR>SQL="SELECT * FROM Upgrade WHERE RegID=" & strID<BR>set rstUpgrades = conn.execute(SQL)<BR><BR>&#039 or<BR><BR>strID=Request.Form("RegID") &#039 this is what I need to work<BR>SQL="SELECT * FROM Upgrade WHERE RegID=" & strID<BR>set rstUpgrades = conn.execute(SQL) &#039 this fails<BR><BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: SQL type mismatch - confusing to a beginner

    If the datatype is of datetime or a string type (char, varchar, text), then the value for the column needs to be in quotes. Sine we use double quotes to form the string in ASP, developers often use single quotes, like you did in the working examples.<BR><BR>So, where you have:<BR>strID=Request.Form("RegID") &#039 this is what I need to work<BR>SQL="SELECT * FROM Upgrade WHERE RegID=" & strID<BR>set rstUpgrades = conn.execute(SQL) <BR><BR>You need to edit the SQL assign statement so it reads:<BR>SQL="SELECT * FROM Upgrade WHERE RegID=&#039" & strID & "&#039"<BR><BR>(The ampersand is used for string concatenation in VBScript.) Note how we are "surrounding" the value with single quotes.<BR><BR>See:<BR>To Quote or Not to Quote: That is the Question<BR>http://www.4guysfromrolla.com/webtech/013099-1.shtml<BR><BR>That article discusses the need for quotation marks in SQL statements, and the art of concatenating those strings. Have a great day!<BR><BR>

  3. #3
    Join Date
    Dec 1969
    Posts
    1,016

    Default RE: SQL type mismatch - confusing to a beginner

    Thank you, Scott. This fixed the problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •