need help updating mulitple records w/ Checkboxes

bcap

Hello,

I really would apprciate help! =)

What I want to do is be able to change the status of mulitple records
using a drop down and a checkbox.

I have a drop down called "ChangeStatus" with the values to change the
status to Pending, Accepted, Declined, Cancelled, Completed.

In the status field, I have a checkbox next to the current status it
is in.

If I check the checkbox, I want the record to update to that specific
record with the new status from the drop down and keep all other
status in their current state.

Currently, the way I have it working is, if you check any check box,
it will update ALL records to the new status.

Below is what my current code looks like, I would appreciate any
advice!

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

<form method="post" action="EditMainPageStat.asp?UpdateStat=1">
<%

If request.querystring("UpdateStat") = "1" then

Response.write "<center>Status has been updated.</center>"

ChangeStat = request.form("ChangeStatus")

Dim MyString, MyArray
MyString = request.form("ChangeStatus")
MyArray = Split(MyString,",") 'the delimiter is the comma
For i = 0 to UBound(MyArray)

Dim MyString2, MyArray2
MyString2 = Request.form("iReqID")
MyArray2 = Split(MyString2,",") 'the delimiter is the comma
For x = 0 to UBound(MyArray2)

set conn=server.createobject("adodb.connection")
conn.open "data Source=xxx;User ID=xxx;Password=xxx;"

sqlstmt = "UPDATE MediaDetail"
sqlstmt = sqlstmt & " SET "',"
sqlstmt = sqlstmt & "iStatusID='" & MyArray(i) & "'"
sqlstmt = sqlstmt & " WHERE iReqID=" & MyArray2(x)

Set RS = conn.execute(SQLstmt)

Next
Next

Else
End If
%>
Response.write "<td class=tx bgColor=" & clr & "><center> <input type='hidden' value='" & rs1("iReqID") &
"' name='iReqId'></center></td>"
Response.Write "<td class=tx bgColor=" & clr & "><center> "&
rs1("StatusDesc") & "  "
Response.write "<input type='checkbox' name='s' value='1' if
instr(s,'1') then Response.Write('checked') End If></center></td>"

---------------------------------------------------------------------------------------------------------------------------------------------
Thank you very much!

Ray

Jul 24 '07 #1

Follow Post Reply

2044

Bob Barrows [MVP]

bcap wrote:
<snip>
We need to see the actual sql statements being executed by the server, not
just the code used to generate those statements. In your loop do this:

Response.Write sqlstmt

and comment out the

Set RS = conn.execute(SQLstmt)
line.

Incidently, why on earth are you opening a recordset to execute a sql
statement that does not retrive records?!? Change that statement to:

conn.execute SQLstmt,,129
'129 = 1 (adCmdText) + 128 (adExecuteNoRecords)

--
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"

Jul 24 '07 #2

bcap

Hi,

Here is the reults on teh SQLStmt:

conn.execute SQLstmt,,129
'129 = 1 (adCmdText) + 128 (adExecuteNoRecords)

UPDATE MediaDetail SET iStatusID='99' WHERE iReqID=34378UPDATE
MediaDetail SET iStatusID='99' WHERE iReqID= 34371UPDATE MediaDetail
SET iStatusID='99' WHERE iReqID= 34373UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34375UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34376UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34377UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34372

Jul 24 '07 #3

Bob Barrows [MVP]

bcap wrote:

Hi,

Here is the reults on teh SQLStmt:

conn.execute SQLstmt,,129
'129 = 1 (adCmdText) + 128 (adExecuteNoRecords)

UPDATE MediaDetail SET iStatusID='99' WHERE iReqID=34378UPDATE
MediaDetail SET iStatusID='99' WHERE iReqID= 34371UPDATE MediaDetail
SET iStatusID='99' WHERE iReqID= 34373UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34375UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34376UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34377UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34372

Hmm. This looks like it's working correctly (I should have had you put a
" " on the end of the response.writes). Is it? Should all seven of those
records be having the iStatusIS field set to '99'?

I don't see any way any of these statements could be updating ALL the
records in your database table, unless it contains only those 7 records ...
How are you determining that all the records are getting updated?
PS. Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/...e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access
--
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"

Jul 24 '07 #4

Bob Barrows [MVP]

Bob Barrows [MVP] wrote:

bcap wrote:
>Hi,

Here is the reults on teh SQLStmt:

conn.execute SQLstmt,,129
'129 = 1 (adCmdText) + 128 (adExecuteNoRecords)

UPDATE MediaDetail SET iStatusID='99' WHERE iReqID=34378UPDATE
MediaDetail SET iStatusID='99' WHERE iReqID= 34371UPDATE MediaDetail
SET iStatusID='99' WHERE iReqID= 34373UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34375UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34376UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34377UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34372

Hmm. This looks like it's working correctly (I should have had you
put a " " on the end of the response.writes). Is it? Should all
seven of those records be having the iStatusIS field set to '99'?

Oh wait. I just reread your original post and I now see that is the problem.
Let's look at your code a little more closely... ah, I see some problems:

Dim MyString, MyArray
MyString = request.form("ChangeStatus")
MyArray = Split(MyString,",") 'the delimiter is the comma
For i = 0 to UBound(MyArray)

Dim MyString2, MyArray2

All Dim statements should be at the top of the procedure. Do not be
declaring variables inside loops - it can lead to unexpected results

MyString2 = Request.form("iReqID")
MyArray2 = Split(MyString2,",") 'the delimiter is the comma
For x = 0 to UBound(MyArray2)

set conn=server.createobject("adodb.connection")
conn.open "data Source=xxx;User ID=xxx;Password=xxx;"

This is really atrocious. All you need is ONE connection. By creating and
opening it in the loop, you've just created seven connections ... which you
never close!!! let's rearrange this:

Dim MyString, MyArray, conn,cmd, arParms
Dim MyString2, MyArray2, i
MyString = request.form("ChangeStatus")
MyArray = Split(MyString,",") 'the delimiter is the comma

'let's make sure this contains what you expect - comment out
'this loop when finished debugging
For i = 0 to ubound(MyArray)
response.write "MyArray(" & i & "): " & MyArray(i) & " "
Next
MyString2 = Request.form("iReqID")
MyArray2 = Split(MyString2,",") 'the delimiter is the comma

'let's make sure this contains what you expect - comment out
'this loop when finished debugging
For i = 0 to ubound(MyArray2)
response.write "MyArray2(" & i & "): " & MyArray2(i) & " "
Next

' I think you only need a single loop

sqlstmt="UPDATE MediaDetail SET iStatusID=? WHERE iReqID=?"
set conn=server.createobject("adodb.connection")
conn.open "data Source=xxx;User ID=xxx;Password=xxx;"
set cmd=createobject("adodb.command")
with cmd
.CommandType = 1 'adCmdText
.CommandText=sqlstmt
Set .ActiveConnection = conn
end with
for i = 0 to ubound(MyArray2)
arParms=Array(MyArray(i),MyArray2(i))
'comment out this Response.Write when finished debugging
Response.Write "The iStatusID field in the row containing iReqID =" & _
MyArray2(i) & " should be getting updated to " & MyArray(i) & " "
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
conn.close: set conn=nothing
If this does not work as you expect, show us the results of the
response.writes
--
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"

Jul 24 '07 #5

bcap

Hi,
Thank you for all the tips and advice. I am a nwevie trying to figure
this all out.

I am still having some problems, I think I am incorrectly passing the
parameters on the UPDATE statement. I also get the following error
message:

Microsoft VBScript runtime error '800a0009'

Subscript out of range: '7'

Here is the sqlstmt:

MyArray(0): 3
MyArray2(0): 34378
MyArray2(1): 34371
MyArray2(2): 34373
MyArray2(3): 34375
MyArray2(4): 34376
MyArray2(5): 34377
MyArray2(6): 34372
Here is the current code:

Dim MyString, MyArray, conn,cmd, arParms
Dim MyString2, MyArray2, i

MyString = request.form("ChangeStatus")
MyArray = Split(MyString,",") 'the delimiter is the comma
For i = 0 to ubound(MyArray)
response.write "MyArray(" & i & "): " & MyArray(i) & " "
Next
MyString2 = Request.form("iReqID")
MyArray2 = Split(MyString2,",") 'the delimiter is the comma
For i = 0 to ubound(MyArray2)
response.write "MyArray2(" & i & "): " & MyArray2(i) & " "

Next

sqlstmt="UPDATE MediaDetail SET iStatusID='"& MyArray(i) & "' WHERE
iReqID='" & MyArray2(i)
Response.write sqlstmt & " "

set conn=server.createobject("adodb.connection")
conn.open "data Source=Media;User ID=Websql;Password=websql;"
set cmd=createobject("adodb.command")
with cmd
..CommandType = 1 'adCmdText
..CommandText=sqlstmt
Set .ActiveConnection = conn
end with
for i = 0 to ubound(MyArray2)
arParms=Array(MyArray(i),MyArray2(i))
'comment out this Response.Write when finished debugging
'Response.Write "The iStatusID field in the row containing iReqID =" &
_
'MyArray2(i) & " should be getting updated to " & MyArray(i) & " "
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
conn.close: set conn=nothing

Else
End If

Jul 24 '07 #6

bcap

Thanks for all your help and tips. Im new and learning as I go, so
the help is much appreciated.

Here is the Array return:

MyArray(0): 2
MyArray2(0): 34378
MyArray2(1): 34371
MyArray2(2): 34373
MyArray2(3): 34375
MyArray2(4): 34376
MyArray2(5): 34377
MyArray2(6): 34372

I am getting the following error messages:

Microsoft VBScript runtime error '800a0009'

Subscript out of range: '7'

Here is my current code: (I think I am passing the variables
incorrectly in the update statement)

Dim MyString, MyArray, conn,cmd, arParms
Dim MyString2, MyArray2, i

MyString = request.form("ChangeStatus")
MyArray = Split(MyString,",") 'the delimiter is the comma
For i = 0 to ubound(MyArray)
response.write "MyArray(" & i & "): " & MyArray(i) & " "
Next
MyString2 = Request.form("iReqID")
MyArray2 = Split(MyString2,",") 'the delimiter is the comma
For i = 0 to ubound(MyArray2)
response.write "MyArray2(" & i & "): " & MyArray2(i) & " "

Next

sqlstmt="UPDATE MediaDetail SET iStatusID='"& MyArray(i) & "' WHERE
iReqID='" & MyArray2(i)
Response.write sqlstmt & " "

set conn=server.createobject("adodb.connection")
conn.open "data Source=Media;User ID=Websql;Password=websql;"
set cmd=createobject("adodb.command")
with cmd
..CommandType = 1 'adCmdText
..CommandText=sqlstmt
Set .ActiveConnection = conn
end with
for i = 0 to ubound(MyArray2)
arParms=Array(MyArray(i),MyArray2(i))
'comment out this Response.Write when finished debugging
'Response.Write "The iStatusID field in the row containing iReqID =" &
_
'MyArray2(i) & " should be getting updated to " & MyArray(i) & " "
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
conn.close: set conn=nothing

Else
End If

Jul 24 '07 #7

bcap

Jul 24 '07 #8

bcap

Jul 24 '07 #9

bcap

Jul 24 '07 #10

bcap

On Jul 24, 12:11 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:

Bob Barrows [MVP] wrote:
bcap wrote:
Hi,

Here is the reults on teh SQLStmt:

conn.execute SQLstmt,,129
'129 = 1 (adCmdText) + 128 (adExecuteNoRecords)

UPDATE MediaDetail SET iStatusID='99' WHERE iReqID=34378UPDATE
MediaDetail SET iStatusID='99' WHERE iReqID= 34371UPDATE MediaDetail
SET iStatusID='99' WHERE iReqID= 34373UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34375UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34376UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34377UPDATE MediaDetail SET
iStatusID='99' WHERE iReqID= 34372

Hmm. This looks like it's working correctly (I should have had you
put a " " on the end of the response.writes). Is it? Should all
seven of those records be having the iStatusIS field set to '99'?

Oh wait. I just reread your original post and I now see that is the problem.
Let's look at your code a little more closely... ah, I see some problems:

Dim MyString, MyArray
MyString = request.form("ChangeStatus")
MyArray = Split(MyString,",") 'the delimiter is the comma
For i = 0 to UBound(MyArray)

Dim MyString2, MyArray2

All Dim statements should be at the top of the procedure. Do not be
declaring variables inside loops - it can lead to unexpected results

MyString2 = Request.form("iReqID")
MyArray2 = Split(MyString2,",") 'the delimiter is the comma
For x = 0 to UBound(MyArray2)

set conn=server.createobject("adodb.connection")
conn.open "data Source=xxx;User ID=xxx;Password=xxx;"

This is really atrocious. All you need is ONE connection. By creating and
opening it in the loop, you've just created seven connections ... which you
never close!!! let's rearrange this:

Dim MyString, MyArray, conn,cmd, arParms
Dim MyString2, MyArray2, i
MyString = request.form("ChangeStatus")
MyArray = Split(MyString,",") 'the delimiter is the comma

'let's make sure this contains what you expect - comment out
'this loop when finished debugging
For i = 0 to ubound(MyArray)
response.write "MyArray(" & i & "): " & MyArray(i) & " "
Next
MyString2 = Request.form("iReqID")
MyArray2 = Split(MyString2,",") 'the delimiter is the comma

'let's make sure this contains what you expect - comment out
'this loop when finished debugging
For i = 0 to ubound(MyArray2)
response.write "MyArray2(" & i & "): " & MyArray2(i) & " "
Next

' I think you only need a single loop

sqlstmt="UPDATE MediaDetail SET iStatusID=? WHERE iReqID=?"
set conn=server.createobject("adodb.connection")
conn.open "data Source=xxx;User ID=xxx;Password=xxx;"
set cmd=createobject("adodb.command")
with cmd
.CommandType = 1 'adCmdText
.CommandText=sqlstmt
Set .ActiveConnection = conn
end with
for i = 0 to ubound(MyArray2)
arParms=Array(MyArray(i),MyArray2(i))
'comment out this Response.Write when finished debugging
Response.Write "The iStatusID field in the row containing iReqID =" & _
MyArray2(i) & " should be getting updated to " & MyArray(i) & " "
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
conn.close: set conn=nothing

If this does not work as you expect, show us the results of the
response.writes
--
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"- Hide quoted text -

- Show quoted text -

Jul 24 '07 #11

bcap

Jul 24 '07 #12

Bob Barrows [MVP]

bcap wrote:

Hi,
Thank you for all the tips and advice. I am a nwevie trying to figure
this all out.

I am still having some problems, I think I am incorrectly passing the
parameters on the UPDATE statement. I also get the following error
message:

Microsoft VBScript runtime error '800a0009'

Subscript out of range: '7'

Here is the sqlstmt:

MyArray(0): 3

This is the problem right here: you are only getting a single value for the
status id. When I wrote my code snip, I assumed you would have a status
value for each req id value. obviously this is not the case. You are getting
a single status value. Is that the intent? If so, make the following
changes:

>
Here is the current code:

Dim MyString, MyArray, conn,cmd, arParms
Dim MyString2, MyArray2, i

MyString = request.form("ChangeStatus")

remove these lines:
************************************************** *********

MyArray = Split(MyString,",") 'the delimiter is the comma
For i = 0 to ubound(MyArray)
response.write "MyArray(" & i & "): " & MyArray(i) & " "
Next

************************************************** *********
<snip>

Why did you change what I wrote? This

sqlstmt="UPDATE MediaDetail SET iStatusID='"& MyArray(i) & "' WHERE
iReqID='" & MyArray2(i)

Should be this:
sqlstmt="UPDATE MediaDetail SET iStatusID=? WHERE iReqID=?"

The ?s are called parameter markers. We will use a Command object to pass
values to them.

<snip>

for i = 0 to ubound(MyArray2)
arParms=Array(MyString,MyArray2(i))
'comment out this Response.Write when finished debugging
'Response.Write "The iStatusID in the row containing iReqID =" & _
'MyArray2(i) & " should be getting updated to " & MyString & " "
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
conn.close: set conn=nothing

Else
End If

--
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"

Jul 24 '07 #13

bcap

Hi nd thanks again!

I am getting the following error message:

Microsoft VBScript runtime error '800a01a8'

Object required: ''
Which seems to be coming from the line:

cmd.Execute ,arParms,128 'adExecuteNoRecords

Here is my sqlstmt:

UPDATE MediaDetail SET iStatusID=? WHERE iReqID=?

Here is my current code:

------------------------------------------------------------------------------------------------------------------------------------------------
If request.querystring("UpdateStat") = "1" then
Dim MyString, MyArray, conn,cmd, arParms
Dim MyString2, MyArray2, i

MyString = request.form("ChangeStatus")

MyString2 = Request.form("iReqID")
MyArray2 = Split(MyString2,",") 'the delimiter is the comma
For i = 0 to ubound(MyArray2)
response.write "MyArray2(" & i & "): " & MyArray2(i) & " "
Next

sqlstmt="UPDATE MediaDetail SET iStatusID=? WHERE iReqID=?"

Response.write " " & sqlstmt & " "
for i = 0 to ubound(MyArray2)
arParms=Array(MyString,MyArray2(i))
'comment out this Response.Write when finished debugging
Response.Write "The iStatusID in the row containing iReqID =" & _
MyArray2(i) & " should be getting updated to " & MyString & " "
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
conn.close: set conn=nothing

Else
End If
------------------------------------------------------------------------------------------------------------------------------------------------

Also, just to make sure I am sharing my idea correctly, I want to try
to better illustrate.

I only want the code to change the status type, only if the checkbox
is checked. Since I can't send a print screen here I'll try to best
illustrate ...
First there is a drop down where you can select the status you want to
change to including (Accepted, Pending, Declined, . Completed)

Below this is a table with the Record ID (iRedID) and a status field
with their current state with a check box next to it. It looks
something like this:

ReqID Status (checkbox)

34372 Pending [ ]
34373 Accepted [ ]
34371 Accepted [ ]
34375 Pending [ ]
34376 Pending [ ]
34377 Pending [ ]
34378 Pending [ ]

So if I changed the drop down of status type to "Completed", and then
selected the cheked off checkboxes for ReqID 34377 and 34378, only
these two records would get updated to "Completed", the others would
stay at their current status value so by page would then look like
this:

ReqID Status (checkbox)

34372 Pending [ ]
34373 Accepted [ ]
34371 Accepted [ ]
34375 Pending [ ]
34376 Pending [ ]
34377 Completed [ ]
34378 Completed [ ]
I hope I am making sense. thanks again for all your time and
thoughts.

Kind Regards,

Ray

Jul 25 '07 #14

Bob Barrows [MVP]

bcap wrote:

Hi nd thanks again!

I am getting the following error message:

Microsoft VBScript runtime error '800a01a8'

Object required: ''
Which seems to be coming from the line:

cmd.Execute ,arParms,128 'adExecuteNoRecords

You seem to have removed the part where the command object was supposed to
be initialized. Go back and look at my code sample

------------------------------------------------------------------------------------------------------------------------------------------------

Also, just to make sure I am sharing my idea correctly, I want to try
to better illustrate.

I only want the code to change the status type, only if the checkbox
is checked. Since I can't send a print screen here I'll try to best
illustrate ...
First there is a drop down where you can select the status you want to
change to including (Accepted, Pending, Declined, . Completed)

OK, my revised code sample should work then.
--
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"

Jul 26 '07 #15

need help updating mulitple records w/ Checkboxes

This discussion thread is closed

Similar topics