need help updating mulitple records w/ Checkboxes

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!



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

Re: need help updating mulitple records w/ Checkboxes

bcap wrote:

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"

Re: need help updating mulitple records w/ Checkboxes

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

Re: need help updating mulitple records w/ Checkboxes

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/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

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"

Re: need help updating mulitple records w/ Checkboxes

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"

Re: need help updating mulitple records w/ Checkboxes

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

Re: need help updating mulitple records w/ Checkboxes

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

Re: need help updating mulitple records w/ Checkboxes

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

Re: need help updating mulitple records w/ Checkboxes

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

Re: need help updating mulitple records w/ Checkboxes

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

Re: need help updating mulitple records w/ Checkboxes

On Jul 24, 12:11 pm, "Bob Barrows [MVP]"
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 -

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

Re: need help updating mulitple records w/ Checkboxes

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

Re: need help updating mulitple records w/ Checkboxes

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


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.


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

Re: need help updating mulitple records w/ Checkboxes

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

Re: need help updating mulitple records w/ Checkboxes

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"