Updating multiple rows in form still a problem

Jack

unread,

Feb 1, 2006, 1:25:29 PM2/1/06

to

Hi,
I got a form with multiple records. I am using the following method to
update all the records. However, the update is not working. When I am trying
to find out the sql statement generated, I am getting no output. The
following is the code:

<%
Dim CN, myDSN, sql_update, arr_ids

myDSN="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\GMISDATA.mdb"

set CN=server.createobject("ADODB.Connection")
CN.Open myDSN

'Let us fill our arr_ids with the ids - split on comma space because when we
have multiple form
' inputs withthe same name and value comes through as a comma-space
delimited string.
arr_ids = Split(Request.Form("ExpIntID"),", ")

'now loop through each id, build the sql, and execute the sql
Dim id

For each id in arr_ids
sql_update = "UPDATE tblGMISExpenditures_Quarter set IsChecked = "
If request.form("chk_Unlock_" & id) <> "" Then
sql_update = sql_update & "True"
else
sql_update = sql_update & "False"
End If

'And finally the where porttion

'sql_update = sql_update & " WHERE ExpIntID = " & id
'response.write sql_update & "<br>"

CN.execute sql_update
next

Response.write "Bye" & "<br>"
CN.close
Set CN = Nothing

%>

I appreciate any help.

Bob Barrows [MVP]

unread,

Feb 1, 2006, 1:33:20 PM2/1/06

to

Jack wrote:
> Hi,
> I got a form with multiple records. I am using the following method to
> update all the records. However, the update is not working. When I am
> trying to find out the sql statement generated, I am getting no
> output. The following is the code:

> 'And finally the where porttion
>
> 'sql_update = sql_update & " WHERE ExpIntID = " & id
> 'response.write sql_update & "<br>"

Why are these lines commented out?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jack

unread,

Feb 1, 2006, 1:49:26 PM2/1/06

to

Bob,
Actually, those for some reason got commented. I tried by uncommenting both
the lines. With this the response.write for the sql statement is still giving
blank. Hope I could explain the confusion. Thanks.

Bob Barrows [MVP]

unread,

Feb 1, 2006, 2:05:18 PM2/1/06

to

Well, you are going to need to do more response.writing to see where the
problem lies (why are you still using dynamic sql? <grin>)

<%
Dim CN, myDSN, sql_update, arr_ids

myDSN="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\GMISDATA.mdb"

set CN=server.createobject("ADODB.Connection")
CN.Open myDSN

'Let us fill our arr_ids with the ids - split on comma space because when we
'have multiple form
' inputs withthe same name and value comes through as a comma-space
'delimited string.

Response.Write "Request.Form(""ExpIntID"") contains " & _
Request.Form("ExpIntID") & "<BR>"

arr_ids = Split(Request.Form("ExpIntID"),", ")

'now loop through each id, build the sql, and execute the sql
Dim id

For each id in arr_ids
sql_update = "UPDATE tblGMISExpenditures_Quarter set IsChecked = "

response.write sql_update & "<br>"

If request.form("chk_Unlock_" & id) <> "" Then
sql_update = sql_update & "True"
else
sql_update = sql_update & "False"
End If

Response.Write "Request.Form(""chk_Unlock_""" & id & ") contains " & _
Request.Form("chk_Unlock_" & id) & "<BR>"
response.write sql_update & "<br>"

'And finally the where porttion

sql_update = sql_update & " WHERE ExpIntID = " & id
response.write sql_update & "<br>"

CN.execute sql_update
next

Response.write "Bye" & "<br>"
CN.close
Set CN = Nothing

%>

Bob Barrows

Jack

unread,

Feb 1, 2006, 3:09:19 PM2/1/06

to

Bob,
The result of running the pages with the various response.write is
Request.Form("ExpIntId") contains
the sql statement is also not showing anything. How do you proceed here?

Bob Barrows [MVP]

unread,

Feb 1, 2006, 3:42:22 PM2/1/06

to

Find out why Request.Form("ExpIntId") contains nothing.

The sql statement is not "showing anything" because the loop never runs.

Jack

unread,

Feb 1, 2006, 6:36:27 PM2/1/06

to

Well Bob, I spend quite a while to go through my code to find why the
request.form("ExpIntID") value is not showing up. However, I could not find
the reason even after trying to find from different angle. I am attaching the
asp code associated with the form. If you can figure it out, please let me
know. Thanks.
CODE:
<%@ Language=VBScript %>

<HTML>
<BODY>
<%
'-----------------------START OF ADDDING EXTRA CODE
----------------------------
if Session("LoggedOn") <> "YES" Then
response.redirect("default.asp")
end if
'----------------------END OF ADDING EXTRA
CODE---------------------------------
%>

<%
'The following line is to prevent this page coming from history.
'We need a new page fro the server each time so that all the
'session variables are reset
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
%>
<% Response.Buffer = True %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<title>List All Subgrants For Unlocking or Unverifying</title>
<%
'We need this to determine grantid. When coming into FDSReport.asp from
ListSubgrants.asp
'the first time, we get grantid from the query string. Otherwise
'we use the session grantid through out this application.
session("sess_FiscalFirst")="Y"
%>
<SCRIPT LANGUAGE="VBScript">

sub btn_LogOut_OnClick()
dim asnwer
answer=msgbox("Are you sure you want to exit?.",vbYesNo,"Warning")
if answer=vbyes then
window.location.href = "http://localhost/gwis"
end if
end sub

sub btn_Save_OnClick()
dim asnwer
answer=msgbox("Are you sure you want to save your
changes?",vbYesNo,"Warning")
if answer=vbyes then
window.location.href = "http://localhost/gwis/processundo.asp"
end if
end sub
</SCRIPT>
</HEAD>

<%

myDSN="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\GMISDATA.mdb"

set CN=server.createobject("ADODB.Connection")
CN.Open myDSN

'sRS stands for recordset that corresponds to all active subgrants
set sRS=server.createobject("ADODB.Recordset")
'set sRS1 = server.createobject("ADODB.Recordset")
sRS.ActiveConnection = CN
'sRS1.ActiveConnection = CN
SQL = "SELECT * from qryUnlockAndUnverify"
sRS.Open SQL
'SQL1 = "SELECT count(*) from GMISExpenseCombo1"
' sRS1.Open SQL1
%>

<div ALIGN="CENTER">
<big><big><font COLOR="navy">On-Line Grant Reporting System</font></big></big>

<H4>
<font COLOR="red">Listing Of Active Subgrants For Unlocking or
Unverifying</font>
</H4>
</div>

  <input type="button" value="Log Out" name="btn_LogOut" >
<input type="submit" value="Save" name="btn_Save">

<form method = "POST" NAME="frmUnDo ACTION = "processundo.asp">
<div ALIGN="CENTER">
<table border=1 width=90% height=1>
<tr>
<th>Key</th>
<th>SubgrantID</th>
<th>Title</th>
<th>Unlock</th>
<th>Unverify</th>
</tr>
<%

'Queue up to the top record of the recordset
if not sRS.eof then sRS.MoveFirst

'Make a temporary variable for user id
Dim t_id

do until sRS.eof
t_id = sRS("ExpIntID")
Key = sRS("SubgrantIntId")
Subgrant = sRS("ComboID")
Title = sRS("Title")
'l_IsChecked = sRS("IsChecked")
'l_IsVerified = sRS("IsVerified")
%>

<td align='center'>
<font color="#000000">
<input type="checkbox" name="chk_Unlock_<%=t_id%>" value="ON" <%if
sRS("IsChecked") = True then response.write " checked" else Response.write "
unchecked"%>>
</td>

</td>

<td align='center'>
<font color="#000000">
<input type="checkbox" name="chk_UnVerify_<%=t_id%>" value="ON" <%if
sRS("IsVerified")= true then response.write" checked" else Response.write "
unchecked"%>>

</td>

</tr>

<%
sRs.MoveNext
loop
sRS.Close
Set sRS = Nothing
%>

</DIV>

</table>

</form>

</BODY>
</HTML>

Bob Barrows [MVP]

unread,

Feb 1, 2006, 7:54:07 PM2/1/06

to

When you run this page and view its source, do you see the id values in the
hidden fields?

Jack wrote:
> Well Bob, I spend quite a while to go through my code to find why the
> request.form("ExpIntID") value is not showing up. However, I could
> not find the reason even after trying to find from different angle. I
> am attaching the asp code associated with the form. If you can figure
> it out, please let me know. Thanks.
> CODE:

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

Jack

unread,

Feb 1, 2006, 11:52:24 PM2/1/06

to

Bob,
Just now I checked the page and doing a view source, I can see the id values
in the hidden field. The recordsource has 161 records. I am just copying the
first two row values from the view source :

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<title>List All Subgrants For Unlocking or Unverifying</title>

sub btn_LogOut_OnClick()
dim asnwer
answer=msgbox("Are you sure you want to exit?.",vbYesNo,"Warning")
if answer=vbyes then
window.location.href = "http://localhost/gwis"
end if
end sub

sub btn_Save_OnClick()
dim asnwer
answer=msgbox("Are you sure you want to save your
changes?",vbYesNo,"Warning")
if answer=vbyes then
window.location.href = "http://localhost/gwis/processundo.asp"
end if
end sub
</SCRIPT>
</HEAD>

<div ALIGN="CENTER">
<big><big><font COLOR="navy">On-Line Grant Reporting System</font></big></big>

<H4>
<font COLOR="red">Listing Of Active Subgrants For Unlocking or
Unverifying</font>
</H4>
</div>

<input type="submit" value="Save" name="btn_Save">

<form method = "POST" NAME="frmUnDo ACTION = "processundo.asp">
<div ALIGN="CENTER">
<table border=1 width=90% height=1>
<tr>
<th>Key</th>
<th>SubgrantID</th>
<th>Title</th>
<th>Unlock</th>
<th>Unverify</th>
</tr>

<div style="cursor:hand"><p align="left"

OnClick="location.href='mainreportagain1.asp?GrantID=5428'">
<u><font color="#0000FF">
5428</font></u></div></td>

</td>

03-A50-81628

FY04 -FY05- FY06 Juvenile Accountability Admin.
</td>

</td>

</td>

</tr>

<div style="cursor:hand"><p align="left"

OnClick="location.href='mainreportagain1.asp?GrantID=5558'">
<u><font color="#0000FF">
5558</font></u></div></td>

</td>

04-W02-81686

Comprehensive Criminal Justice Training
</td>

</td>

</td>

</tr>

Hope this helps. Regards.

Bob Barrows [MVP]

unread,

Feb 2, 2006, 7:03:05 AM2/2/06

to

Jack wrote:
> Bob,
> Just now I checked the page and doing a view source, I can see the id
> values in the hidden field. The recordsource has 161 records. I am
> just copying the first two row values from the view source :
>

> sub btn_Save_OnClick()
> dim asnwer
> answer=msgbox("Are you sure you want to save your
> changes?",vbYesNo,"Warning")
> if answer=vbyes then
> window.location.href = "http://localhost/gwis/processundo.asp"
> end if
> end sub
> </SCRIPT>
> </HEAD>
>

I normally don't deal with client-side issues in this group but:

By setting the window.location.href property, you are failing to submit the
form.
Instead of navigating to the processundo page, you need to call the form's
submit() method. Give your form element an id and do:

document.getElementById("formid").submit()

Also, this is malformed html (no end quote after frmUnDo):

Bob Barrows

Bob Barrows [MVP]

unread,

Feb 2, 2006, 9:58:46 AM2/2/06

to

Bob Barrows [MVP] wrote:
> Jack wrote:
>> Bob,
>> Just now I checked the page and doing a view source, I can see the id
>> values in the hidden field. The recordsource has 161 records. I am
>> just copying the first two row values from the view source :
>>
>> sub btn_Save_OnClick()
>> dim asnwer
>> answer=msgbox("Are you sure you want to save your
>> changes?",vbYesNo,"Warning")
>> if answer=vbyes then
>> window.location.href = "http://localhost/gwis/processundo.asp"
>> end if
>> end sub
>> </SCRIPT>
>> </HEAD>
>>
> I normally don't deal with client-side issues in this group but:
>
> By setting the window.location.href property, you are failing to
> submit the form.
> Instead of navigating to the processundo page, you need to call the
> form's submit() method. Give your form element an id and do:
>
> document.getElementById("formid").submit()
>

Actually, even this is not necessary. I just realized that btn_Save is an
input of type="submit", so all you have to do is set the event's returnvalue
to false to prevent a form submission.

if answer=vbno then
event.returnvalue = false
end if

Bob Barrows
--

Jack

unread,

Feb 2, 2006, 1:05:35 PM2/2/06

to

Bob,
Thanks a lot for pointing out where the problem is. I had no idea why the
Request.form element was not producing any value. However, with your
suggestion I made the necessary changes and the values appeared like magic.
The rest of the sql statement was easy to built. You saved a tons of time and
gave the right direction. Thanks again for your generous help. I appreciate
it. Best regards.

Jack

unread,

Feb 2, 2006, 1:08:34 PM2/2/06

to

Thanks for the alternative suggestion. Regards.