|
 |
access_asp thread: updating a table several times using loop
Message #1 by "Spencer Saunders" <ssaunders@i...> on Mon, 17 Dec 2001 11:47:22 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0074_01C186F0.96F9A260
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello all.
I am building somthing that allows a user to select a predetermined
category, or to create a new one, and then have the ability to order the
sequence in which these categories are displayed.
I've got everything worked out to populate dropdown menus (to select a
category) or to add a new one. The problem I have is getting a set of
SQL UPDATE statements to run through a loop.
form1 allows the user to select the order they want the categories to be
displayed.
Form2 write as many SQL statements as there are categories. The SQL is
generated fine but how do I execute the SQL without creating a
recordset? (requiring me to open and then close multiple recordsets
dynamically right?)
I'm probably missing something really dumb.
Here's what I got so far:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
'GET THE EXACT SAME RESULTS AS THE PREVIOUS FORM
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
SQL=3D"SELECT DISTINCT category, categoryimp FROM articles ORDER BY
categoryimp, category ASC"
DIM RS
set RS=3Dserver.createobject("ADODB.recordset")
SET RS =3D Conn.Execute(SQL)
RSArray =3D RS.GetRows
set RS =3D nothing
DIM newRS
set newRS=3Dserver.createobject("ADODB.Recordset")
for i=3D0 to Ubound(RSarray,2)
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
'THE PREVIOUS FORM USED THE CATEGORY NAMES AS THE RADIO BUTTON NAMES AND
THEREFORE WILL BE THE SAME AS THE RECORDSET ABOVE
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
SQL=3D"UPDATE articles SET categoryimp=3D"& request.form(Rsarray(0,i))
SQL=3DSQL &" WHERE category=3D"& RSarray(0,i)
response.write SQL &"<br>" &vbCrLf
SET newRS=3Dconn.execute(SQL)
Next
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D::
Thanks in advance
spencer
Message #2 by "Spencer Saunders" <ssaunders@i...> on Mon, 17 Dec 2001 23:21:50 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_02B2_01C18751.9B2553A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Okay I am slightly further than when I posted this earlier today but....
I am now having problems with executing an action command with no
recordset returned.
Pg 355 of ASP 3.0 says just to use :
conn.execute SQL, [var_for_records_affected], adCmdText +
adExecuteNoRecords
But I am getting a "Microsoft OLE DB Provider for ODBC Drivers error
'80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected
1" on that line of code.
Mine as follows.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
for i=3D0 to Ubound(RSarray,2)
SQL=3D"UPDATE articles SET categoryimp=3D"& request.form(Rsarray(0,i))
SQL=3DSQL &" WHERE category=3D"& RSarray(0,i)
response.write SQL &"<br>" &vbCrLf
conn.execute SQL, lngrecs, adCmdText + adExecuteNoRecords
Next
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Again, any help would be appreciated.
-spencer-
----- Original Message -----
From: Spencer Saunders
To: Access ASP
Sent: December 17, 2001 11:47 AM
Subject: [access_asp] updating a table several times using loop
Hello all.
I am building somthing that allows a user to select a predetermined
category, or to create a new one, and then have the ability to order the
sequence in which these categories are displayed.
I've got everything worked out to populate dropdown menus (to select a
category) or to add a new one. The problem I have is getting a set of
SQL UPDATE statements to run through a loop.
form1 allows the user to select the order they want the categories to
be displayed.
Form2 write as many SQL statements as there are categories. The SQL is
generated fine but how do I execute the SQL without creating a
recordset? (requiring me to open and then close multiple recordsets
dynamically right?)
I'm probably missing something really dumb.
Here's what I got so far:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
'GET THE EXACT SAME RESULTS AS THE PREVIOUS FORM
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
SQL=3D"SELECT DISTINCT category, categoryimp FROM articles ORDER BY
categoryimp, category ASC"
DIM RS
set RS=3Dserver.createobject("ADODB.recordset")
SET RS =3D Conn.Execute(SQL)
RSArray =3D RS.GetRows
set RS =3D nothing
DIM newRS
set newRS=3Dserver.createobject("ADODB.Recordset")
for i=3D0 to Ubound(RSarray,2)
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
'THE PREVIOUS FORM USED THE CATEGORY NAMES AS THE RADIO BUTTON NAMES
AND THEREFORE WILL BE THE SAME AS THE RECORDSET ABOVE
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
SQL=3D"UPDATE articles SET categoryimp=3D"& request.form(Rsarray(0,i))
SQL=3DSQL &" WHERE category=3D"& RSarray(0,i)
response.write SQL &"<br>" &vbCrLf
SET newRS=3Dconn.execute(SQL)
Next
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D::
Thanks in advance
spencer
$subst('Email.Unsub').
Message #3 by "Ken Schaefer" <ken@a...> on Tue, 18 Dec 2001 16:57:07 +1100
|
|
Put
<%Response.Buffer = True%>
at the top of the page, after <%Option Explicit%>. Then, amend your loop:
<%
For i = 0 to UBound(RSarray,2)
strSQL = _
"UPDATE articles " & _
"SET categoryimp = " & request.form(Rsarray(0,i)) & " " & _
"WHERE category=" & RSarray(0,i)
Response.Write(strSQL & "<br>")
Response.Flush
conn.execute strSQL, lngRecs, adCmdText + adExecuteNoRecords
Next
%>
Then, you should have a bunch of SQL statements - the last one displayed is
the one that is causing the error. Then goto
www.adopenstatic.com/faq/80040e10.asp to work out what the problem is.
Ceers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Spencer Saunders" <ssaunders@i...>
Subject: [access_asp] Re: updating a table several times using loop
Okay I am slightly further than when I posted this earlier today but....
I am now having problems with executing an action command with no recordset
returned.
Pg 355 of ASP 3.0 says just to use :
conn.execute SQL, [var_for_records_affected], adCmdText + adExecuteNoRecords
But I am getting a "Microsoft OLE DB Provider for ODBC Drivers error
'80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1"
on that line of code.
Mine as follows.
==============
for i=0 to Ubound(RSarray,2)
SQL="UPDATE articles SET categoryimp="& request.form(Rsarray(0,i))
SQL=SQL &" WHERE category="& RSarray(0,i)
response.write SQL &"<br>" &vbCrLf
conn.execute SQL, lngrecs, adCmdText + adExecuteNoRecords
Next
==============
Again, any help would be appreciated.
-spencer-
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #4 by "Spencer Saunders" <ssaunders@i...> on Tue, 18 Dec 2001 10:16:28 -0500
|
|
DOOOH!!!
I was missing the apostrophe's around the category name in my WHERE clause.
Thanks a bunch Ken.
I do have one quick question for you though- I know I SHOULD HAVE been using
<%Option Explicit%> on all my pages but every time I put it on a page the
page gets an internal server error. Any idea why that's happening>?
Thanks again
-spencer-
----- Original Message -----
From: Ken Schaefer <ken@a...>
To: Access ASP <access_asp@p...>
Sent: December 18, 2001 12:57 AM
Subject: [access_asp] Re: updating a table several times using loop
> Put
>
> <%Response.Buffer = True%>
>
> at the top of the page, after <%Option Explicit%>. Then, amend your loop:
>
> <%
> For i = 0 to UBound(RSarray,2)
>
> strSQL = _
> "UPDATE articles " & _
> "SET categoryimp = " & request.form(Rsarray(0,i)) & " " & _
> "WHERE category=" & RSarray(0,i)
>
> Response.Write(strSQL & "<br>")
> Response.Flush
>
> conn.execute strSQL, lngRecs, adCmdText + adExecuteNoRecords
>
> Next
> %>
>
> Then, you should have a bunch of SQL statements - the last one displayed
is
> the one that is causing the error. Then goto
> www.adopenstatic.com/faq/80040e10.asp to work out what the problem is.
>
> Ceers
> Ken
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Spencer Saunders" <ssaunders@i...>
> Subject: [access_asp] Re: updating a table several times using loop
>
>
> Okay I am slightly further than when I posted this earlier today but....
>
> I am now having problems with executing an action command with no
recordset
> returned.
>
> Pg 355 of ASP 3.0 says just to use :
> conn.execute SQL, [var_for_records_affected], adCmdText +
adExecuteNoRecords
>
> But I am getting a "Microsoft OLE DB Provider for ODBC Drivers error
> '80040e10'
> [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1"
> on that line of code.
>
> Mine as follows.
> ==============
>
> for i=0 to Ubound(RSarray,2)
> SQL="UPDATE articles SET categoryimp="& request.form(Rsarray(0,i))
> SQL=SQL &" WHERE category="& RSarray(0,i)
> response.write SQL &"<br>" &vbCrLf
> conn.execute SQL, lngrecs, adCmdText + adExecuteNoRecords
> Next
>
>
> ==============
>
> Again, any help would be appreciated.
>
> -spencer-
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
$subst('Email.Unsub').
|
|
|
View Cart
