SELECT Query in ASP

[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.as p, 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(Sessio n("ERP_IR_Distr ibution_rs")) Then
Set rs = Session("ERP_IR _Distribution_r s")
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.CreateOb ject("ADODB.Rec ordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
Set Session("ERP_IR _Distribution_r s") = rs
End If
%>

Regards,
Hasnain Raja

[Bob Barrows [MVP]]

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.as p, 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(Sessio n("ERP_IR_Distr ibution_rs")) Then
Set rs = Session("ERP_IR _Distribution_r s")
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.CreateOb ject("ADODB.Rec ordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
Set Session("ERP_IR _Distribution_r s") = 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.W rite sql" statement.

Oh, and this:
Set Session("ERP_IR _Distribution_r s") = 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.CreateOb ject("ADODB.Rec ordset")
conn.Distributi onIR 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"

[McKirahan]

"Nano" <mh****@gmail.c omwrote in message
news:49******** *************** ***********@w28 g2000hsf.google groups.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.as p, 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(Sessio n("ERP_IR_Distr ibution_rs")) Then
Set rs = Session("ERP_IR _Distribution_r s")
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.CreateOb ject("ADODB.Rec ordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
Set Session("ERP_IR _Distribution_r s") = rs
End If
%>

Regards,
Hasnain Raja

Use "Response.W rite 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' "

[Nano]

On Dec 3, 9:20 pm, "McKirahan" <N...@McKirahan .comwrote:

"Nano" <mhr...@gmail.c omwrote in message

news:49******** *************** ***********@w28 g2000hsf.google groups.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.as p, 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(Sessio n("ERP_IR_Distr ibution_rs")) Then
Set rs = Session("ERP_IR _Distribution_r s")
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.CreateOb ject("ADODB.Rec ordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
Set Session("ERP_IR _Distribution_r s") = rs
End If
%>

Regards,
Hasnain Raja

Use "Response.W rite 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 :)

[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.

[McKirahan]

"Nano" <mh****@gmail.c omwrote in message
news:fd******** *************** ***********@e67 g2000hsc.google groups.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 & "' "

[Bob Barrows [MVP]]

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"