|
|
You're in the
inetserver asp general
group:need help updating mulitple records w/ Checkboxes
inetserver asp general:
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 "<font color='red' face='arial' size='2'><center><b>Status has been updated.</b></font></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 & "><font size='1'><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
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
[quoted text, click to view]
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"
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) & "<BR>" 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) & "<BR>" Next sqlstmt="UPDATE MediaDetail SET iStatusID='"& MyArray(i) & "' WHERE iReqID='" & MyArray2(i) Response.write sqlstmt & "<br>" 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) & "<BR>" cmd.Execute ,arParms,128 'adExecuteNoRecords Next conn.close: set conn=nothing Else End If
[quoted text, click to view]
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 "<BR>" 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.inetserver.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"
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) & "<BR>" 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) & "<BR>" Next sqlstmt="UPDATE MediaDetail SET iStatusID='"& MyArray(i) & "' WHERE iReqID='" & MyArray2(i) Response.write sqlstmt & "<br>" 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) & "<BR>" cmd.Execute ,arParms,128 'adExecuteNoRecords Next conn.close: set conn=nothing Else End If
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) & "<BR>" 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) & "<BR>" Next sqlstmt="UPDATE MediaDetail SET iStatusID='"& MyArray(i) & "' WHERE iReqID='" & MyArray2(i) Response.write sqlstmt & "<br>" 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) & "<BR>" cmd.Execute ,arParms,128 'adExecuteNoRecords Next conn.close: set conn=nothing Else End If
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) & "<BR>" 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) & "<BR>" Next sqlstmt="UPDATE MediaDetail SET iStatusID='"& MyArray(i) & "' WHERE iReqID='" & MyArray2(i) Response.write sqlstmt & "<br>" 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) & "<BR>" cmd.Execute ,arParms,128 'adExecuteNoRecords Next conn.close: set conn=nothing Else End If
[quoted text, click to view]
Bob Barrows [MVP] wrote: Oh wait. I just reread your original post and I now see that is the problem.> 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 "<BR>" on the end of the response.writes). Is it? Should all > seven of those records be having the iStatusIS field set to '99'? > 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) & "<BR>" 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) & "<BR>" 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) & "<BR>" 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"
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) & "<BR>" 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) & "<BR>" Next sqlstmt="UPDATE MediaDetail SET iStatusID='"& MyArray(i) & "' WHERE iReqID='" & MyArray2(i) Response.write sqlstmt & "<br>" 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) & "<BR>" cmd.Execute ,arParms,128 'adExecuteNoRecords Next conn.close: set conn=nothing Else End If
On Jul 24, 12:11 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
[quoted text, click to view] 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 "<BR>" 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) & "<BR>" > 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) & "<BR>" > 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) & "<BR>" > 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) & "<BR>" 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) & "<BR>" Next sqlstmt="UPDATE MediaDetail SET iStatusID='"& MyArray(i) & "' WHERE iReqID='" & MyArray2(i) Response.write sqlstmt & "<br>" 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) & "<BR>" cmd.Execute ,arParms,128 'adExecuteNoRecords Next conn.close: set conn=nothing Else End If
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) & "<BR>" 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) & "<BR>" Next sqlstmt="UPDATE MediaDetail SET iStatusID='"& MyArray(i) & "' WHERE iReqID='" & MyArray2(i) Response.write sqlstmt & "<br>" 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) & "<BR>" cmd.Execute ,arParms,128 'adExecuteNoRecords Next conn.close: set conn=nothing Else End If
[quoted text, click to view]
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: [quoted text, click to view] > > Here is the current code: > > Dim MyString, MyArray, conn,cmd, arParms > Dim MyString2, MyArray2, i > > MyString = request.form("ChangeStatus") remove these lines: *********************************************************** [quoted text, click to view] > MyArray = Split(MyString,",") 'the delimiter is the comma ***********************************************************> For i = 0 to ubound(MyArray) > response.write "MyArray(" & i & "): " & MyArray(i) & "<BR>" > Next > <snip> Why did you change what I wrote? This [quoted text, click to view] > 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> [quoted text, click to view] > 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 & "<BR>" > 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"
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) & "<BR>" Next sqlstmt="UPDATE MediaDetail SET iStatusID=? WHERE iReqID=?" Response.write "<br>" & sqlstmt & "<br><br>" 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 & "<BR>" 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
[quoted text, click to view]
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 [quoted text, click to view] > ------------------------------------------------------------------------------------------------------------------------------------------------ > > 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"
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |