CODE NOT UPDATING 1

[jedel]

Hi all

Can anybody tell me why this code is not updating the records?
Got it from Dr Devs site and I even used my own FAQ, but I still can't get it to work.

The code processes all the way through to the redirect page, but when I go back to the records, they have not been updated.

<%
If Request("Submit") <> "" Then
   intRecIDs = Replace(Request("hidRecIDs"), "*", "")
   arrRecIDs = Split(intRecIDs, ", ")
   For i = 0 to Ubound(arrRecIDs)
      txtsurname = Replace(Request("txtsurname" & arrRecIDs(i)), "'", "''")
      txtfirstname = Replace(Request("txtfirstname" & arrRecIDs(i)), "'", "''")
	  txtemail = Replace(Request("txtemail" & arrRecIDs(i)), "'", "''")
	  txtph = Replace(Request("txtph" & arrRecIDs(i)), "'", "''")
	  txtmob = Replace(Request("txtmob" & arrRecIDs(i)), "'", "''")

      Set CmdUpdate = Server.CreateObject ("ADODB.Command")
		CmdUpdate.ActiveConnection = MM_connect_STRING
		
      commUpdate.CommandText = "UPDATE KnectMbrTBL SET mbr_Surname = '" & txtsurname & "', mbr_Firstname = '" & txtfirstname & "',mbr_email = '" & txtemail & "',mbr_Ph = '" & txtph & "', mbr_Mob = '"  & txtmob & "'  WHERE RecID = " & arrRecIDs(i)
	   
      commUpdate.CommandType = 1
      commUpdate.CommandTimeout = 0
      commUpdate.Prepared = true
      commUpdate.Execute()
   Next
   
   Response.Redirect("UDgroups.asp?LoginID=" & (mbrs.Fields.Item("mbr_GroupID").Value))
End If
%>

Any help would be great

Cheers

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[tsuji]

Choose the name between CmdUpdate or commUpdate.

 

[jedel]

Good Pickup.

Unfortunately, it did not resolve the problem.

Here is the latest code. Its straight out of Dr Dev tutorial on multiple updating of records.

<%
If Request("Submit") <> "" Then
   intRecIDs = Replace(Request("hidRecIDs"), "*", "")
   arrRecIDs = Split(intRecIDs, ", ")
   For i = 0 to Ubound(arrRecIDs)
      txtsurname = Replace(Request("txtsurname" & arrRecIDs(i)), "'", "''")
      txtfirstname = Replace(Request("txtfirstname" & arrRecIDs(i)), "'", "''")
	  txtemail = Replace(Request("txtemail" & arrRecIDs(i)), "'", "''")
	  txtph = Replace(Request("txtph" & arrRecIDs(i)), "'", "''")
	  txtmob = Replace(Request("txtmob" & arrRecIDs(i)), "'", "''")

      Set commUpdate = Server.CreateObject ("ADODB.Command")
		commUpdate.ActiveConnection = MM_connect_STRING
		
      commUpdate.CommandText = "UPDATE KnectMbrTBL SET"_ 
	  &"mbr_Surname = '" & txtsurname & "',"_ 
	  &"mbr_Firstname = '" & txtfirstname & "',"_
	  &"mbr_email = '" & txtemail & "',"_
	  &"mbr_Ph = '" & txtph & "',"_ 
	  &"mbr_Mob = '"  & txtmob & "'"_  
	  &"WHERE RecID = " & arrRecIDs(i)
	   
      commUpdate.CommandType = 1
      commUpdate.CommandTimeout = 0
      commUpdate.Prepared = true
      commUpdate.Execute()
   Next
   
   Response.Redirect("UDgroups.asp?LoginID=" & (mbrs.Fields.Item("mbr_GroupID").Value))
End If
%>

Let me know if you need to look at any other code

Thanks for your input

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[DreXor]

internet options temp files, settings, check for new versions of page every visit, and add to your code:

<% response.expires = 0 %>

before anything else in the file

that's just to be on the safe side that nothing is caching, my guess is that you're using querystring to pass the record value, which caches very easily. double check the actual DB that the record has or hasn't been updated.
i'll clean that up a little bit for you and post here shortly, but personally i prefer just using a connection object and executes. less confusing in many aspects, few ex ceptions include pagination of recordsets, or getting a new record's identity directly after creation.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

 

[DreXor]

new Point to ponder is a possible BAD sql statement, like trying to set a zero length field to "" or whatnot, i bring this up because of the redirect, there's a chance that the ADO Connection is throwing an error, yet you're not seeing it due to this page redirects immediately
another note is that your update strings were missing some spaces, most important being between the last single quote and the word WHERE

Note example is based on the idea that your connection string is a standard DB connection string.

[green]'re-written code[/green]
If Request("Submit") <> "" Then
    intRecIDs = Replace(Request("hidRecIDs"), "*", "")
    arrRecIDs = Split(intRecIDs, ", ")
    
    DIM SQL(0) [green]' this is an array value in case of memo feilds or anything that may exceed a normal string variable size cap[/green]
    Set commUpdate = Server.CreateObject ("ADODB.Connection")
    commUpdate.Open MM_connect_STRING
[green]'combined the text transformations and the sql statement into one, because i'm not seeing a need to actually generate a bunch of extra variables AND note the addition of squarebrackets on the field names and table names, as well as additional spaces after each portion ot the sql statement.[/green]    
    For i = 0 to Ubound(arrRecIDs)
        SQL(0) = "UPDATE [KnectMbrTBL SET]"_ 
            &"[mbr_Surname] = '" & SQLQuotes(Request("txtsurname" & arrRecIDs(i))) & "', "_ 
            &"[mbr_Firstname] = '" & SQLQuotes(Request("txtfirstname" & arrRecIDs(i))) & "', "_
            &"[mbr_email] = '" & SQLQuotes(Request("txtemail" & arrRecIDs(i))) & "', "_
            &"[mbr_Ph] = '" & SQLQuotes(Request("txtph" & arrRecIDs(i))) & "', "_ 
            &"[mbr_Mob] = '"  & SQLQuotes(Request("txtmob" & arrRecIDs(i))) & "' "_  
            &" WHERE [RecID] = " & arrRecIDs(i)
[green] 'unrem the next line and rem the redirect to see the update sql in case you're curious.[/green]
'Response.Write SQL(0)

        Set RsUpdate = commUpdate.Execute(SQL(0)) [green]'fire and forget recordset[/green]
    Next
    [green]'Dont forget to close and clear your objects[/green]
    Set RsUpdate = Nothing
    commUpdate.close
    set commUpdate = nothing
    Response.Redirect("UDgroups.asp?LoginID=" & (mbrs.Fields.Item("mbr_GroupID").Value))
End If
Function SQLQuotes(inputString) [green]'Handles the sql string quotes both single and double.[/green]
    If NOT isNull(inputString) Then
        SQLQuotes = Replace(Replace(inputString,"'","''"),Chr(34),Chr(34)&chr(34))
    Else
        SQLQuotes = ""
    end if
End Function

After thinking about this more i would say the main problem was the redirect hid an SQL error, which was the lack of space before the WHERE clause. try adding that space before anything.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

 

[jedel]

Drex,

Tried all of your suggestions with the space in the SQL. Tried my code and your code all to no avail. I know the connection is working because I had an error through the redirect code so it had to be passing through. I rem'd the redirect line un unrem'd the response write line and it just refreshed the same page with the old text back into the fields (ie did not update)

Although I really don't like doing it, I'kk give the the code for the entire process.

The javascript code

<script type="text/javascript">
function RecUpdate(RecID){
var ThisID = "*" + (RecID) + "*"
if (document.mbrsFRM.hidRecID.value == ""){
document.mbrsFRM.hidRecID.value = (ThisID)
}
if (document.mbrsFRM.hidRecID.value != ""){
var str = document.mbrsFRM.hidRecID.value;
var pos = str.indexOf(ThisID);
if (pos == -1) {
document.mbrsFRM.hidRecID.value = document.mbrsFRM.hidRecID.value + ", " + (ThisID)
}
}
}
</script>

The update SQL

<%
If Request("Submit") <> "" Then
   intRecIDs = Replace(Request("hidRecIDs"), "*", "")
   arrRecIDs = Split(intRecIDs, ", ")
   For i = 0 to Ubound(arrRecIDs)
      txtsurname = Replace(Request("txtsurname" & arrRecIDs(i)), "'", "''")
      txtfirstname = Replace(Request("txtfirstname" & arrRecIDs(i)), "'", "''")
	  txtemail = Replace(Request("txtemail" & arrRecIDs(i)), "'", "''")
	  txtph = Replace(Request("txtph" & arrRecIDs(i)), "'", "''")
	  txtmob = Replace(Request("txtmob" & arrRecIDs(i)), "'", "''")

set commUpdate = Server.CreateObject("ADODB.Command")
commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"_
         & "Data Source=" & Server.MapPath("/database/knect.mdb") & ";"_
         & "Persist Security Info=False"
      'Set commUpdate = Server.CreateObject ("ADODB.Command")
		'commUpdate.ActiveConnection = MM_connect_STRING
		
      commUpdate.CommandText = "UPDATE KnectMbrTBL SET"_ 
	  &"mbr_Surname = '" & txtsurname & "',"_ 
	  &"mbr_Firstname = '" & txtfirstname & "',"_
	  &"mbr_email = '" & txtemail & "',"_
	  &"mbr_Ph = '" & txtph & "',"_ 
	  &"mbr_Mob = '"  & txtmob & "'"_  
	  &" WHERE RecID = " & arrRecIDs(i)
	   
      commUpdate.CommandType = 1
      commUpdate.CommandTimeout = 0
      commUpdate.Prepared = true
      commUpdate.Execute()
   Next
   
   Response.Redirect("UDgroups.asp?LoginID=" & Session("mbrGpID"))
End If
%>

The Form itself

<form action="members.asp" METHOD="POST" name="mbrsFRM" target="_self" id="mbrsFRM">
      <table width="833" border="0" cellpadding="2">
        <tr>
          <td width="112" class="CSSButton">Surname</td>
          <td width="112" class="CSSButton">Firstname</td>
          <td width="112" class="CSSButton">email</td>
          <td width="112" class="CSSButton">Ph</td>
          <td width="112" class="CSSButton">Mobile</td>
          <td width="193" class="CSSButton">&nbsp;</td>
          <td width="36" class="CSSButton">&nbsp;</td>
        </tr>

        <% 
While ((Repeat1__numRows <> 0) AND (NOT mbrs.EOF)) 
%>
<% intRecID =(mbrs.Fields.Item("mbr_ID").Value) ' Store the current RecordID in a variable %>
          <tr>
            <td nowrap="nowrap"><input name="txtsurname<%= intRecID %>" type="text" class="normalTxtnobox" onChange="RecUpdate('<%= intRecID %>')"  value="<%=(mbrs.Fields.Item("mbr_Surname").Value)%>" /></td>
            <td nowrap="nowrap"><input name="txtfirstname<%= intRecID %>" type="text" class="normalTxtnobox" onChange="RecUpdate('<%= intRecID %>')" value="<%=(mbrs.Fields.Item("mbr_Firstname").Value)%>" /></td>
            <td nowrap="nowrap"><input name="txtemail<%= intRecID %>" type="text" class="normalTxtnobox" onChange="RecUpdate('<%= intRecID %>')"  value="<%=(mbrs.Fields.Item("mbr_email").Value)%>" /></td>
            <td nowrap="nowrap"><input name="txtph<%= intRecID %>" type="text" class="normalTxtnobox" onChange="RecUpdate('<%= intRecID %>')" value="<%=(mbrs.Fields.Item("mbr_Ph").Value)%>" /></td>
            <td nowrap="nowrap"><input name="txtmob<%= intRecID %>" type="text" class="normalTxtnobox" onChange="RecUpdate('<%= intRecID %>')"  value="<%=(mbrs.Fields.Item("mbr_Mob").Value)%>" /></td>
            <td nowrap="nowrap"></td>
            <td nowrap="nowrap"><%= intRecID %>
            <input name="hidRecID<%= intRecID %>" type="hidden" value="<%= intRecID %>" size="5" />            </td>
          </tr>
          <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  mbrs.MoveNext()
Wend
%>
<tr>
          <td><input type="submit" name="Submit" id="Submit" value="Submit" /></td>
          <td colspan="4"><input name="hidRecID" type="text" id="hidRecID" size="80" /></td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
      </table>
    
      <input type="hidden" name="MM_update" value="mbrsFRM" />
    </form>

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[DreXor]

i just did a search .. i kept having a nagging feeling and it was there... you're REQUESTING hidRecID[red]s[/red]
but submitting hidRecId, if that's not the problem its a big one in the right direction

seems you might have pluralized a few other fariables in the form handler

looks like it was good idea to post all 3 portions of code

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

 

[jedel]

Drex!!!!!!!!

If I could give 10 stars I would! What a mongrel little letter.

Don't you just love code!!!!!!!

That was the issue. All is working well now.

Cheers

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."