Updating multiple rows in form still a problem

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

CN.execute sql_update
next

Response.write "Bye" & "
"
CN.close
Set CN = Nothing

%>

I appreciate any help.

Re: Updating multiple rows in form still a problem

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

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.

Re: Updating multiple rows in form still a problem

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]" wrote:

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

Re: Updating multiple rows in form still a problem

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

<%
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") & "
"

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 & "
"
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) & "
"
response.write sql_update & "
"

'And finally the where porttion

sql_update = sql_update & " WHERE ExpIntID = " & id
response.write sql_update & "
"

CN.execute sql_update
next

Response.write "Bye" & "
"
CN.close
Set CN = Nothing

%>

Bob Barrows
Jack wrote:
> 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]" wrote:
>
>> 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 & "
"
>>
>> 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.

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

Re: Updating multiple rows in form still a problem

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]" wrote:

> Well, you are going to need to do more response.writing to see where the
> problem lies (why are you still using dynamic sql? )
>
> <%
> 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") & "
"
>
> 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 & "
"
> 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) & "
"
> response.write sql_update & "
"
>
> 'And finally the where porttion
>
> sql_update = sql_update & " WHERE ExpIntID = " & id
> response.write sql_update & "
"
>
> CN.execute sql_update
> next
>
> Response.write "Bye" & "
"
> CN.close
> Set CN = Nothing
>
> %>
>
> Bob Barrows
> Jack wrote:
> > 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]" wrote:
> >
> >> 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 & "
"
> >>
> >> 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.
>
> --
> 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.
>
>
>

Re: Updating multiple rows in form still a problem

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

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

Jack wrote:
> 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]" wrote:
>
>> Well, you are going to need to do more response.writing to see where
>> the problem lies (why are you still using dynamic sql? )
>>
>> <%
>> 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") & "
"
>>
>> 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 & "
"
>> 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) & "
"
>> response.write sql_update & "
"
>>
>> 'And finally the where porttion
>>
>> sql_update = sql_update & " WHERE ExpIntID = " & id
>> response.write sql_update & "
"
>>
>> CN.execute sql_update
>> next
>>
>> Response.write "Bye" & "
"
>> CN.close
>> Set CN = Nothing
>>
>> %>
>>
>> Bob Barrows
>> Jack wrote:
>>> 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]" wrote:
>>>
>>>> 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 & "
"
>>>>
>>>> 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.
>>
>> --
>> 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.

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

Re: Updating multiple rows in form still a problem

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



<%
'-----------------------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 %>




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



<%

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

Re: Updating multiple rows in form still a problem

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"

Re: Updating multiple rows in form still a problem

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 :