Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > SELECT Query in ASP

Reply
Thread Tools

SELECT Query in ASP

 
 
Nano
Guest
Posts: n/a
 
      12-03-2007
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
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      12-03-2007
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"


 
Reply With Quote
 
 
 
 
Nano
Guest
Posts: n/a
 
      12-04-2007
On Dec 3, 9:20 pm, "McKirahan" <(E-Mail Removed)> wrote:
> "Nano" <(E-Mail Removed)> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
>
>
> > 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
 
Reply With Quote
 
Nano
Guest
Posts: n/a
 
      12-04-2007
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.
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      12-04-2007
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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
select gridview row without using select button? =?Utf-8?B?RGFiYmxlcg==?= ASP .Net 5 02-27-2009 02:56 AM
ASP.NET won't retrieve query results that depend on union query Eric Nelson ASP .Net 5 02-04-2009 10:51 PM
ASP LIKE query using parameterized query Roland Hall ASP General 8 01-13-2007 05:08 AM
select of select box will select multiple in another box palmiere Javascript 1 02-09-2004 01:11 PM
ASP / SQL Query - Conditional SELECT Statement Guy Hocking ASP General 7 01-21-2004 08:41 PM



Advertisments