×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Query Problem with Access 97 Database via ASP

Query Problem with Access 97 Database via ASP

Query Problem with Access 97 Database via ASP

(OP)
Hello All!  I am having a query problem on a numeric value.  I am using the Access 97 database.  It comes up with the following error messages:

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/ds3select.asp, line 57

Please help!  Thanks a million!

Below is the short script that I used:

<%
' Declare our variables... always good practice!
Dim strURL     ' The URL of this page so the form will work
               ' no matter what this file is named.

Dim cnnSearch  ' ADO connection
Dim rstSearch  ' ADO recordset
Dim strDBPath  ' path to our Access database (*.mdb) file

Dim strSQL     ' The SQL Query we build on the fly
Dim strSearch  ' The text being looked for

' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")

' Retreive the term being searched for.  I'm doing it on
' the QS since that allows people to bookmark results.
' You could just as easily have used the form collection.
strSearch = Request.QueryString("search")

' Since I'm doing this all in one page I need to see if anyone
' has searched for something.  If they have we hit the DB.
' O/W I just show the search form and quit.

%>
<p>Search our DS3 database by ECP number.  (% returns all)</p>
<form action="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>" />
<input type="submit" />
</form>
<p>[Try '176' for an example]</p>
<%
If strSearch <> "" Then
    ' MapPath of virtual database file path to a physical path.
    ' If you want you could hard code a physical path here.
    strDBPath = Server.MapPath("/ds3/ds3.mdb")


    ' Create an ADO Connection to connect to the sample database.
    ' We're using OLE DB but you could just as easily use ODBC or a DSN.
    Set cnnSearch = Server.CreateObject("ADODB.Connection")

    ' This line is for the Access sample database:
    cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

    ' We're actually using SQL Server so we use this line instead:
    ' cnnSearch.Open Application("SQLConnString")

    ' Build our query based on the input.
    strSQL = "SELECT ecp, carrier, t1id " _
        & "FROM b1cell_alpha " _
        & "WHERE ecp = '%" & Replace(strSearch, "'", "''") & "%' " _
        & "ORDER BY ecp;"

    ' Execute our query using the connection object.  It automatically
    ' creates and returns a recordset which we store in our variable.
    Set rstSearch = cnnSearch.Execute(strSQL)

    ' Display a table of the data in the recordset.  We loop through the
    ' recordset displaying the fields from the table and using MoveNext
    ' to increment to the next record.  We stop when we reach EOF.
    ' For fun I'm combining some fields and showwing you can do more then
    ' just spit out the data in the form it is in in the table.
    %>
    <table border="1">
        <tr>
        <th>ECP</th>
        <th>T1 Circuit ID</th>
        </tr>
    <%
    Do While Not rstSearch.EOF
        %>
        <tr>
        <td align=center><%= rstSearch.Fields("ecp").Value %></td>
        <td align=center><%= rstSearch.Fields("t1id").Value %></td>
        </tr>
        <%

        rstSearch.MoveNext
    Loop
    %>
    </table>
    <%
    ' Close our recordset and connection and dispose of the objects
    rstSearch.Close
    Set rstSearch = Nothing
    cnnSearch.Close
    Set cnnSearch = Nothing
End If

' That's all folks!  See it's really not all that hard.
%>

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources