SELECT Query in ASP

N

Nano

I have created ASP file from MS Access. It has the following Code. But
it gives an error at:

rs.Open sql, conn, 3, 3

The Error is:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected
1.
/MyWeb/ERP-IR Distribution.asp, line 23


This error only comes when I write query that includes WHERE clause,
simple SELECT query runs without any error.

Kinldy help, below mention is the code.


<%
If IsObject(Session("ERP_IR_Distribution_rs")) Then
Set rs = Session("ERP_IR_Distribution_rs")
Else
sql = "SELECT [ERP-IR].ID, [ERP-IR].Department, [ERP-IR].[Logged
on], [ERP-IR].Issue, [ERP-IR].Code, [ERP-IR].Status, [ERP-IR].[Action
Taken], [ERP-IR].[Recommendations/ Comments], [ERP-IR].[Action By],
[ERP-IR].Due, [ERP-IR].[Closed on], [ERP-IR].[Key User], [ERP-IR].[KU
Comments] FROM [ERP-IR] WHERE ((([ERP-
IR].Department)=""Distribution"")) "
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
Set Session("ERP_IR_Distribution_rs") = rs
End If
%>


Regards,
Hasnain Raja
 
B

Bob Barrows [MVP]

Nano said:
I have created ASP file from MS Access. It has the following Code. But
it gives an error at:

rs.Open sql, conn, 3, 3

The Error is:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected
1.
/MyWeb/ERP-IR Distribution.asp, line 23


This error only comes when I write query that includes WHERE clause,
simple SELECT query runs without any error.

Kinldy help, below mention is the code.


<%
If IsObject(Session("ERP_IR_Distribution_rs")) Then
Set rs = Session("ERP_IR_Distribution_rs")
Else
sql = "SELECT [ERP-IR].ID, [ERP-IR].Department, [ERP-IR].[Logged
on], [ERP-IR].Issue, [ERP-IR].Code, [ERP-IR].Status, [ERP-IR].[Action
Taken], [ERP-IR].[Recommendations/ Comments], [ERP-IR].[Action By],
[ERP-IR].Due, [ERP-IR].[Closed on], [ERP-IR].[Key User], [ERP-IR].[KU
Comments] FROM [ERP-IR] WHERE ((([ERP-
IR].Department)=""Distribution"")) "
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
Set Session("ERP_IR_Distribution_rs") = rs
End If
%>


Regards,
Hasnain Raja

Before opening the recordset, writhe the contents of sql to Response so you
can see the actual query being sent to Jet to be executed. You cannot debug
a sql statement without knowing what it is. Typically, looking at the
statement in the browser window will make the error obvious. If not, copy
the statement from the browser window, open your database in Access, create
a new query in design view, switch to SQL View and paste the statement. Try
running it. If you've built it correctly, it should run without
modification. If not, you may get a more helpful error message. If you're
still stuck, show us the sql statement.

Looking at your code, I don't see any problems that stick out, so I will
wait to see the results of your "Response.Write sql" statement.

Oh, and this:
Set Session("ERP_IR_Distribution_rs") = rs

is a horrible idea. Do not save ADO objects in session or application:
http://classicasp.aspfaq.com/components/should-i-store-objects-in-session/application-scope.html

You might want to consider turning that sql statement into a saved query
once you get it running in Access. Perhaps save it as "DistributionIR".
Doing so will allow you to run it as simply as:

Set rs = Server.CreateObject("ADODB.Recordset")
conn.DistributionIR rs

Here are some posts I've made about using parameterized saved queries:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]
 
N

Nano

I have created ASP file from MS Access. It has the following Code. But
it gives an error at:
rs.Open sql, conn, 3, 3
The Error is:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected
1.
/MyWeb/ERP-IR Distribution.asp, line 23
This error only comes when I write query that includes WHERE clause,
simple SELECT query runs without any error.
Kinldy help, below mention is the code.

<%
If IsObject(Session("ERP_IR_Distribution_rs")) Then
Set rs = Session("ERP_IR_Distribution_rs")
Else
sql = "SELECT [ERP-IR].ID, [ERP-IR].Department, [ERP-IR].[Logged
on], [ERP-IR].Issue, [ERP-IR].Code, [ERP-IR].Status, [ERP-IR].[Action
Taken], [ERP-IR].[Recommendations/ Comments], [ERP-IR].[Action By],
[ERP-IR].Due, [ERP-IR].[Closed on], [ERP-IR].[Key User], [ERP-IR].[KU
Comments] FROM [ERP-IR] WHERE ((([ERP-
IR].Department)=""Distribution"")) "
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
Set Session("ERP_IR_Distribution_rs") = rs
End If
%>
Regards,
Hasnain Raja

Use "Response.Write sql" before the "rs.Open" statement.

Here's how I broke it down for readability:

sql = "SELECT
[ERP-IR].ID,
[ERP-IR].Department,
[ERP-IR].[Logged on],
[ERP-IR].Issue,
[ERP-IR].Code,
[ERP-IR].Status,
[ERP-IR].[Action Taken],
[ERP-IR].[Recommendations/ Comments],
[ERP-IR].[Action By],
[ERP-IR].Due,
[ERP-IR].[Closed on],
[ERP-IR].[Key User],
[ERP-IR].[KU Comments]
FROM [ERP-IR]
WHERE ((([ERP-IR].Department)=""Distribution""))"

Perhaps your "WHERE" clause should be:

FROM [ERP-IR] WHERE [ERP-IR].Department = 'Distribution' "- Hide quoted text -

- Show quoted text -

Thanks Guys, you people have always been so helpful. Thanks for the
best practices too, I am new to all this and am learning by time :)
 
N

Nano

This works fine but what if I have to choose criteria from the FORUM.
I have got the data in a variable via querystring, now when I execute
it using that variable in WHERE clause it gives the same error.

Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected
1.

Error comes at this line:

rs.Open sql, conn, 3, 3

Kindly advise.
 
B

Bob Barrows [MVP]

Nano said:
This works fine but what if I have to choose criteria from the FORUM.
I have got the data in a variable via querystring, now when I execute
it using that variable in WHERE clause it gives the same error.

Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected
1.

Error comes at this line:

rs.Open sql, conn, 3, 3

Kindly advise.

Please. Don't ask us to debug a sql statement without showing it to us. You
must write it to response and show us the result of your vbscript code that
is supposed to generate the statement
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top