Hey guys,

First off I want to say thanks to all who helped me last time I ran into a problem. Everyone went out of their way to help me and I greatly appreciate it. However, I am having some problems again and would again appreciate any insight and help on this issue.

Here is the problem: I have a search page for a LARGE database that checks to see if the user has filled in a particular box, and if not it ignores it and goes to the next one to build the query to send to the database. The problem I am getting is I get an error that states:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

/Portal/Portal/Misc/Practicum/practicum2/admin_sites-search_results.asp, line 60



So here is my coding for my search page:

<%
'If the session variable is False or does not exsist then redirect the user to the unauthorised user page
If Session("blnIsUserGood") = False or IsNull(Session("blnIsUserGood")) = True then
    'Redirect to unathorised user page
    Response.Redirect"admin_failed.asp"
End If
%>

<%

Dim query


Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
dbGlobalWeb.ConnectionTimeout = 15
dbGlobalWeb.CommandTimeout = 30
dbGlobalWeb.Open "DBQ=" & Server.MapPath("practicum.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20 ;", "username", "password"

query = "SELECT * FROM sites_training, sites_info WHERE"

If Request.Form("TypeSearch1") <> "" Then
    If Request.Form("TypeSearch1") = "clinical" Then
        query = query & " site_ta-macl.sites_training LIKE '%Y%' AND "
    Elseif Request.Form("TypeSearch1") = "forensic" Then
        query = query & " site_ta-mafo.sites_training LIKE '%Y%' AND "
    Elseif Request.Form("TypeSearch1") = "io" Then
        query = query & " site_ta-maio.sites_training LIKE '%Y%' AND "
    Elseif Request.Form("TypeSearch1") = "assess" Then
        query = query & " site_ta-pdas.sites_training LIKE '%Y%' AND "
    Elseif Request.Form("TypeSearch1") = "therapy" Then
        query = query & " site_ta-pdth.sites_training LIKE '%Y%' AND "
    Elseif Request.Form("TypeSearch1") = "adassess" Then
        query = query & " site_ta-pdad-type.sites_training LIKE '%Assessment%' AND "
    Elseif Request.Form("TypeSearch1") = "adtherapy" Then
        query = query & " site_ta-pdad-type.sites_training LIKE '%Therapy%' AND "
    Elseif Request.Form("TypeSearch1") = "assesstherapy" Then
        query = query & " site_ta-pdad-type.sites_training LIKE '%Assessment of Therapy%' AND "
    End If
End If

If Request.Form("DaInBox2") <> "" Then
    query = query & " " & Request.Form("TypeSearch2") & " LIKE '%" & Request.Form("DaInBox2") & "%' AND "
End If

If Request.Form("DaInBox3") <> "" Then
    query = query & " " & Request.Form("TypeSearch3") & " LIKE '%" & Request.Form("DaInBox3") & "%' AND "
End If

If Request.Form("DaInBox4") <> "" Then
    query = query & " " & Request.Form("TypeSearch4") & " LIKE '%" & Request.Form("DaInBox4") & "%' AND "
End If

If Request.Form("DaInBox5") <> "" Then
    query = query & " " & Request.Form("TypeSearch5") & " LIKE '%" & Request.Form("DaInBox5") & "%' AND "
End If

query = query & "site_code.sites_info <> 0 ORDER BY site_name.sites_info ASC"

Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset")
rsGlobalWeb.Open query, dbGlobalWeb, 3
%>

<center>
<table width="640" border="0">
  <tr>
    <td><h1 align="center"><b>Administration</b></h1></td>
  </tr>
  <tr>
    <td><div align="center"><i>Practicum Site Database &amp; Student Site Selections</i></div></td>
  </tr>
  <tr>
    <td>
      <div align="center">&nbsp;&nbsp;</div></td>
  </tr>
  <tr>
    <td><div align="center">
      <h2><b>Practicum Sites - Search Results </b></h2>
    </div></td>
  </tr>
  <tr>
    <td><div align="center">

    </div></td>
  </tr>
  <tr>
    <td>
      <div align="center">&nbsp;&nbsp;</div></td>
  </tr>
  <tr>
    <td><div align="center">

    <%
If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then%>

<h2 align="center">No match found in Practicum Sites.</h2>
<a href="admin_sites-search.asp">Back to Search Page</a>
<%Else%>


<%If Not rsGlobalWeb.BOF Then%>

<h2>Practicum Sites Matching Search:</h2>

<%
    Do While Not rsGlobalWeb.EOF
    %>

      <br>
<A HREF="javascript:popUp('admin_sites-view_show.asp?ID=<%= rsDC("site_code.sites_info") %>')">
<%= rsDC("site_name.sites_info") %>
</a>

<% rsGlobalWeb.MoveNext
    Loop
    %>

<%End If%>
<%End If%>
<%
rsGlobalWeb.Close
dbGlobalWeb.Close
%>

    </div></td>
  </tr>
</table>

</center>


Any help is greatly appreciated and thanks again for all your help in the past!

Michael W. Vollmer

__________________
Michael W. Vollmer

What does the final query look like? Response.Write out query and see what you get. That's the first step.. I don't see anything immediately apparent with the code or query.

Peter
------------------------------------------------------
Work smarter, not harder.

Here is what I get when I added in the Response.Write(query):

SELECT * FROM sites_training, sites_info WHERE site_name.sites_info LIKE '%t%' AND site_code.sites_info <> 0 ORDER BY site_name.sites_info ASC

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

/Portal/Portal/Misc/Practicum/practicum2/admin_sites-search_results.asp, line 62

I don't see anything that looks wrong at this point, unless i mispelled a field name, but I am pretty confident that I did not. Any ideas?

Michael W. Vollmer

I just thought of something else that may or may not have anything to do with this problem. The query I am trying to run with this search page should look through all the records of one table for a condition (ie. site_ta-macl = 'Y') and then display all the records listed alphabetically by a field (site_name) in another table. Both tables have a common field called site_code. Should I change the structure of my query or will it display what I am trying to get from it? I have a feeling I am leaving something out of the query. Thanks again!

Michael W. Vollmer

Your query structure looks ok. Unless you try to use the site_code column you shouldn't have a problem with that. If you do use it, you'd get the AMBIGUOUS COLUMN NAME error but that's not the case.

That error doesn't jive with me. I can't recall ever seeing that error except when calling a stored procedure or a function.

Wait a minute....

SELECT * FROM sites_training, sites_info WHERE site_name.sites_info LIKE '%t%' AND site_code.sites_info <> 0 ORDER BY site_name.sites_info ASC

You have sites_info in the FROM, but you don't use it.
You have site_code in the WHERE, but not the FROM.
Also, you have sites_info as a column of site_code. Is this right?
There are several errors here.

Peter
------------------------------------------------------
Work smarter, not harder.

You have sites_info in the FROM, but you don't use it. - I used it in the AND site_code.site_info <> 0
You have site_code in the WHERE, but not the FROM. - That's because site_code is a column
Also, you have sites_info as a column of site_code. Is this right? - No, site_code is a column of site_info


site_info is a table, just like site_training. site_code is a column in both site_info and site_training. Should I have put site_info.site_code to get the info out of the site_code column in the the site_info table then?



Michael W. Vollmer

Yes, correct. When you reference column names, you should do that in a TableName.Columnname syntax.

This means that site_info.site_code refers to the column site_code in the site_info table.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Woah... there is some confusion here...

SQL in question:
SELECT * FROM sites_training, sites_info
WHERE site_name.sites_info LIKE '%t%'
AND site_code.sites_info <> 0
ORDER BY site_name.sites_info ASC

Comments in question (with responses):
You have sites_info in the FROM, but you don't use it. - I used it in the AND site_code.site_info <> 0
The use of "sites_info" there refers to a column of table "site_code", not a table.

You have site_code in the WHERE, but not the FROM. - That's because site_code is a column
Here site_code is used as the table (see the first comment)

Also, you have sites_info as a column of site_code. Is this right? - No, site_code is a column of site_info
See above.

I think it's a simple case of having your table and column names bass ackards. The SQL syntax is <table>.<fieldname>.

Peter
------------------------------------------------------
Work smarter, not harder.

Okay, my bad here guys. I fixed all of the spots where I made the switch (i think anyways). Here is the final coding:

<%
'If the session variable is False or does not exsist then redirect the user to the unauthorised user page
If Session("blnIsUserGood") = False or IsNull(Session("blnIsUserGood")) = True then
    'Redirect to unathorised user page
    Response.Redirect"admin_failed.asp"
End If
%>

<%

Dim query


Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
dbGlobalWeb.ConnectionTimeout = 15
dbGlobalWeb.CommandTimeout = 30
dbGlobalWeb.Open "DBQ=" & Server.MapPath("practicum.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20 ;", "username", "password"

query = "SELECT * FROM sites_training, sites_info WHERE"

If Request.Form("TypeSearch1") <> "" Then
    If Request.Form("TypeSearch1") = "clinical" Then
        query = query & " sites_training.site_ta-macl LIKE '%Y%' AND "
    Elseif Request.Form("TypeSearch1") = "forensic" Then
        query = query & " sites_training.site_ta-mafo LIKE '%Y%' AND "
    Elseif Request.Form("TypeSearch1") = "io" Then
        query = query & " sites_training.site_ta-maio LIKE '%Y%' AND "
    Elseif Request.Form("TypeSearch1") = "assess" Then
        query = query & " sites_training.site_ta-pdas LIKE '%Y%' AND "
    Elseif Request.Form("TypeSearch1") = "therapy" Then
        query = query & " sites_training.site_ta-pdth LIKE '%Y%' AND "
    Elseif Request.Form("TypeSearch1") = "adassess" Then
        query = query & " sites_training.site_ta-pdad-type LIKE '%Assessment%' AND "
    Elseif Request.Form("TypeSearch1") = "adtherapy" Then
        query = query & " sites_training.site_ta-pdad-type LIKE '%Therapy%' AND "
    Elseif Request.Form("TypeSearch1") = "assesstherapy" Then
        query = query & " sites_training.site_ta-pdad-type LIKE '%Assessment of Therapy%' AND "
    End If
End If

If Request.Form("DaInBox2") <> "" Then
    query = query & " " & Request.Form("TypeSearch2") & " LIKE '%" & Request.Form("DaInBox2") & "%' AND "
End If

If Request.Form("DaInBox3") <> "" Then
    query = query & " " & Request.Form("TypeSearch3") & " LIKE '%" & Request.Form("DaInBox3") & "%' AND "
End If

If Request.Form("DaInBox4") <> "" Then
    query = query & " " & Request.Form("TypeSearch4") & " LIKE '%" & Request.Form("DaInBox4") & "%' AND "
End If

If Request.Form("DaInBox5") <> "" Then
    query = query & " " & Request.Form("TypeSearch5") & " LIKE '%" & Request.Form("DaInBox5") & "%' AND "
End If

query = query & "sites_info.site_code <> 0 ORDER BY sites_info.site_name ASC"

Response.Write(query)

Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset")
rsGlobalWeb.Open query, dbGlobalWeb, 3
%>

<center>
<table width="640" border="0">
  <tr>
    <td><h1 align="center"><b>Administration</b></h1></td>
  </tr>
  <tr>
    <td><div align="center"><i>Practicum Site Database &amp; Student Site Selections</i></div></td>
  </tr>
  <tr>
    <td>
      <div align="center">&nbsp;&nbsp;</div></td>
  </tr>
  <tr>
    <td><div align="center">
      <h2><b>Practicum Sites - Search Results </b></h2>
    </div></td>
  </tr>
  <tr>
    <td><div align="center">

    </div></td>
  </tr>
  <tr>
    <td>
      <div align="center">&nbsp;&nbsp;</div></td>
  </tr>
  <tr>
    <td><div align="center">

    <%
If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then%>

<h2 align="center">No match found in Practicum Sites.</h2>
<a href="admin_sites-search.asp">Back to Search Page</a>
<%Else%>


<%If Not rsGlobalWeb.BOF Then%>

<h2>Practicum Sites Matching Search:</h2>

<%
    Do While Not rsGlobalWeb.EOF
    %>

      <br>
<A HREF="javascript:popUp('admin_sites-view_show.asp?ID=<%= rsGlobalWeb("site_code.sites_info") %>')">
<%= rsGlobalWeb("site_name.sites_info") %>
</a>

<% rsGlobalWeb.MoveNext
    Loop
    %>

<%End If%>
<%End If%>
<%
rsGlobalWeb.Close
dbGlobalWeb.Close
%>

    </div></td>
  </tr>
</table>

</center>



But I am still getting this error for one of the searches:

SELECT * FROM sites_training, sites_info WHERE sites_training.site_ta-macl LIKE '%Y%' AND sites_info.site_code <> 0 ORDER BY sites_info.site_name ASC
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

/Portal/Portal/Misc/Practicum/practicum2/admin_sites-search_results.asp, line 62


Did I miss one of the tablename.columnname switches? Any ideas? Thanks again for all of your help too, I feel like an idiot. I guess my brain is running bass ackwards lately.

Michael W. Vollmer

I've lost track of what columns were similar between tables, but using SELECT * can give you problems. if you know which columns you want, you should select those explicitly.

Peter
------------------------------------------------------
Work smarter, not harder.