SELECT Query in ASP

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:

[i]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[/i]

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.

[i]
<%
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
%>[/i]

Regards,
Hasnain Raja

Re: SELECT Query in ASP

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:
>
> [i]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[/i]
>
> 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.
>
> [i]
> <%
> 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
> %>[/i]
>
> 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-objec ts-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&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.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"

Re: SELECT Query in ASP

"Nano" wrote in message
news:49337759-b0c1-4c81-9662-fa1fa36f4aff@w28g2000hsf.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:
>
> [i]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[/i]
>
> 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.
>
> [i]
> <%
> 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
> %>[/i]
>
> 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' "

Re: SELECT Query in ASP

On Dec 3, 9:20 pm, "McKirahan" wrote:
> "Nano" wrote in message
>
> news:49337759-b0c1-4c81-9662-fa1fa36f4aff@w28g2000hsf.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:
>
> > [i]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[/i]
>
> > 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.
>
> > [i]
> > <%
> > 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
> > %>[/i]
>
> > 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 :)

Re: SELECT Query in ASP

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.

Re: SELECT Query in ASP

"Nano" wrote in message
news:fd62ea99-a8a8-4f28-9fec-892f62056e90@e67g2000hsc.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 & "' "

Re: SELECT Query in ASP

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"