I'm trying to request a value from a form and use the value to query an 
Access database using VBscript. The form value is in a text/string format. 
I tried converting the datatype of the form field value to a Long 
datatype. What else should I do to query the database with a numeric value 
form the form.

Here is the code: 

<%
   dim oConn
   dim oRS
   dim CS, DRV, DBQ
   dim SQL
   dim txtAE, txtCode
   dim sngAE, sngCode
   
   	txtAE = Request.Form("inputAE")
   txtCode = Request.Form("inputCode")
   
   
    
    Session.Contents("sesAE") = sngAE
    Session.Contents("sesCode") = sngCode
   
   DRV = "Driver={Microsoft Access Driver (*.mdb)};" 
   	DBQ=  "Dbq=//Cp-web2/wwwroot/eplans/TracingIndex.mdb;" 
   CS = DRV + DBQ	
   
    set oConn = Server.CreateObject("ADODB.connection")
    oConn.Open  CS 
    set oRS = Server.CreateObject("ADODB.recordset")
    
    if (txtAE = "") then
      sngCode = CLng(txtCode)
      SQL = "SELECT * FROM Source WHERE Source.[Code#] ='" & sngCode & "'"
      
    else 
      sngAE = CLng(txtAE)
      SQL = "SELECT * FROM Source WHERE Source.[A&E#] ='" & sngAE & "'"
      end if
   
   oRS.open SQL, oConn
%>
  	
   <table border="0" cellpadding="10">
   <tr><th>A&E#</th><th>Code#</th><th>Description</th></tr>
   <% do while not oRS.EOF %>
   <% = "<tr><td>" %>
    <a href= http://<% = oRS("A&E#") %>.dwf> 
   <% = oRS("A&E#") & "</td><td></a>" %> 
   <% = oRS("Code#") & "</td><td>" %>
   <% = oRS("Description") & "</td></tr>" %>
   <% oRS.movenext
   loop
    %>
</table>
	
  <%
   oRs.Close
   set oRS = nothing
   %>
 

a) Validate your input - you are opening yourself up to SQL injection
vulnerabilities the way you are doing things at the moment. What if someone
enters:

5;DELETE+FROM+YourMostPreciousTable;

into the URL? You're sending that straight to the database. Check out:
www.adopenstatic.com/resources/code/UIValidation.asp

b) Error handling! You are doing CLng() on a value, but what if it isn't
capable of being cast? You'll generate a nasty error. Better would be to
check for IsNumeric() before casting

c) Your actual error is detailed here:
http://www.adopenstatic.com/faq/80040e07.asp

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Jonathan" <martinbeer325201@c...>
Subject: [access_asp] [Microsoft][ODBC Microsoft Access Driver] Data type
mismatch in criteria expression.


: I'm trying to request a value from a form and use the value to query an
: Access database using VBscript. The form value is in a text/string format.
: I tried converting the datatype of the form field value to a Long
: datatype. What else should I do to query the database with a numeric value
: form the form.
:
: Here is the code:
:
: <%
:    dim oConn
:    dim oRS
:    dim CS, DRV, DBQ
:    dim SQL
:    dim txtAE, txtCode
:    dim sngAE, sngCode
:
:    txtAE = Request.Form("inputAE")
:    txtCode = Request.Form("inputCode")
:
:
:
:     Session.Contents("sesAE") = sngAE
:     Session.Contents("sesCode") = sngCode
:
:    DRV = "Driver={Microsoft Access Driver (*.mdb)};"
:    DBQ=  "Dbq=//Cp-web2/wwwroot/eplans/TracingIndex.mdb;"
:    CS = DRV + DBQ
:
:     set oConn = Server.CreateObject("ADODB.connection")
:     oConn.Open  CS
:     set oRS = Server.CreateObject("ADODB.recordset")
:
:     if (txtAE = "") then
:       sngCode = CLng(txtCode)
:       SQL = "SELECT * FROM Source WHERE Source.[Code#] ='" & sngCode & "'"
:
:     else
:       sngAE = CLng(txtAE)
:       SQL = "SELECT * FROM Source WHERE Source.[A&E#] ='" & sngAE & "'"
:       end if
:
:    oRS.open SQL, oConn
: %>
:
:    <table border="0" cellpadding="10">
:    <tr><th>A&E#</th><th>Code#</th><th>Description</th></tr>
:    <% do while not oRS.EOF %>
:    <% = "<tr><td>" %>
:     <a href= http://<% = oRS("A&E#") %>.dwf>
:    <% = oRS("A&E#") & "</td><td></a>" %>
:    <% = oRS("Code#") & "</td><td>" %>
:    <% = oRS("Description") & "</td></tr>" %>
:    <% oRS.movenext
:    loop
:     %>
: </table>
:
:   <%
:    oRs.Close
:    set oRS = nothing
:    %>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~