By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,268 Members | 1,371 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,268 IT Pros & Developers. It's quick & easy.

SELECT Query in ASP

P: n/a
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
Dec 3 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Nano wrote:
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/compone...ion-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&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Dec 3 '07 #2

P: n/a
"Nano" <mh****@gmail.comwrote in message
news:49**********************************@w28g2000 hsf.googlegroups.com...
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' "

Dec 3 '07 #3

P: n/a
On Dec 3, 9:20 pm, "McKirahan" <N...@McKirahan.comwrote:
"Nano" <mhr...@gmail.comwrote in message

news:49**********************************@w28g2000 hsf.googlegroups.com...


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 :)
Dec 4 '07 #4

P: n/a
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.
Dec 4 '07 #5

P: n/a
"Nano" <mh****@gmail.comwrote in message
news:fd**********************************@e67g2000 hsc.googlegroups.com...
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
Since you didn't show us your WHERE clause I'll guess.

Use something like:

sql = "SELECT * FROM [ERP-IR]" _
& " WHERE [ERP-IR].Department = '" & variable & "' "
Dec 4 '07 #6

P: n/a
Nano wrote:
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

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Dec 4 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.