DeveloperBarn

Rebelle · #1 April 2nd, 2008, 09:59 AM

Hi All,

Looking for suggestions / advice.

I have an asp page where I display a recordset and these records are updated all at once. Well, there are about 185 results, but sometimes only a few records may be changed/updated but all get updated when the form is submitted. If I keep it this way, I was thinking I could add a comment field or checkbox to check for the fields that are changed. Need a way to let me know which records I have already updated.

Is there a way to only update those records changed and display on the screen a (changed on - field)?

Code:

function sZeroCheck(sData)
	if len(sData) < 1 then
		sData = 0
	end if
	sZeroCheck = sData
end function

Dim sItem,sMaterial, sMfg1, sMfg2, sMfg3
Dim rs, sSql
Dim nCounter

nCounter = 0
sItem = Request("ItemIDRow" & nCounter)

 response.write "First Loop" & sItem & "End"
do while len(sItem) > 0


 response.write "Second Loop"


sMaterial = Request.Form("MaterialRow" & nCounter)
sMfg1 = sZeroCheck(Request.Form("Mfg1Row" & nCounter))
sMfg2 = sZeroCheck(Request.Form("Mfg2Row" & nCounter))
sMfg3 = sZeroCheck(Request.Form("Mfg3Row" & nCounter))
%>
<%

Set rs = Server.CreateObject("ADODB.Recordset")


'build SQL
sSql = "update tblItems set Material = '" & sMaterial & "', ManuDel1 = " & sMfg1 & ", ManuDel2 = " & sMfg2 & ", ManuDel3 = " & sMfg3 & " "
sSql = sSql & " WHERE ItemID=" & sItem & " "
'response.write "   " & sSql & "  "

	rs.Open sSql, Conn, 3, 1

nCounter = nCounter + 1
sItem = Request("ItemIDRow" & nCounter)
'response.write " NCOunter = " & nCounter
Loop

Item --- Material ---- Del1 ----- Del2 ----- Del3
ToolBox--- TB3883 --------1 ---------- 0 ---------- 1
Kit1 ------- K38220 -------- 0 -------- 0 -------- 0
Kit2 --------K48201 -------- 1 --------1 -------- 0

Del1, Del 2, Del3 are textboxes and will get updated as required.

BLaaaaaaaaaarche · #2 April 2nd, 2008, 12:23 PM

Try this code. You can run it as is and see if that is what you are looking for.

Code:

<%
If Request.Form("Submit") <> "" Then
	For Each Item In Request.Form
		If Left(Item, 2) = "ID" Then
			intID = Split(Item, "_")(1)
			strMaterialRow = Request.Form("MaterialRow_" & intID)
			strMfg1Row = Request.Form("Mfg1Row_" & intID)
			strMfg2Row = Request.Form("Mfg2Row_" & intID)
			strMfg3Row = Request.Form("Mfg3Row_" & intID)

			strSQL = "UPDATE MyTable SET MaterialRow = '" & strMaterialRow & "', " & _
				"Mfg1Row = '" & strMfg1Row & "', " & _
				"Mfg2Row = '" & strMfg2Row & "', " & _
				"Mfg3Row = '" & strMfg3Row & "' " & _
				"WHERE ID = " & intID
			response.write strSQL & "<br>"
		End If
	Next
End If
%>
<form method="post" action="">
<table width="500" border="1">
  <tr>
    <td>Update</td>
    <td>Material</td>
    <td>Mfg1</td>
    <td>Mfg2</td>
    <td>Mfg3</td>
  </tr>
  <tr>
    <td><input type="checkbox" name="ID_1"></td>
    <td><input type="text" name="MaterialRow_1"></td>
    <td><input type="text" name="Mfg1Row_1"></td>
    <td><input type="text" name="Mfg2Row_1"></td>
    <td><input type="text" name="Mfg3Row_1"></td>
  </tr>
  <tr>
    <td><input type="checkbox" name="ID_2"></td>
    <td><input type="text" name="MaterialRow_2"></td>
    <td><input type="text" name="Mfg1Row_2"></td>
    <td><input type="text" name="Mfg2Row_2"></td>
    <td><input type="text" name="Mfg3Row_2"></td>
  </tr>
</table>
<input type="submit" name="Submit" value="Submit">
</form>

Rebelle · #3 April 3rd, 2008, 03:18 PM

Hi BLaaaaaaaaaarche,

Ok, having some difficultly here. After our conversation yesterday, I've added a new field for the LastModifiedOn to display the last time the record has been updated. I'm trying to work with the code above to continue to give a checkbox on the screen so if i check the checkbox, only the ones checked should get updated.

I'm not getting error on the screen when I submit but it's not updating.

1) Should I have added a field to hold the checkbox value? I have this:

Code:

<td><input type="checkbox" name="ID_<%response.write(i)%>"></td>

Thanks again!

BLaaaaaaaaaarche · #4 April 3rd, 2008, 04:47 PM

The ID_<Value> represents the value, or unique ID, of the record. Why are you using a counter there? You should be using the RS ID when looping through. You will need to provide more code as I can't see what you are working with.

Rebelle · #5 April 7th, 2008, 09:43 AM

Ok, here is what I have so far....trying to get it to only update which lines are checked.

Code:

<%
If Request.Form("Submit") <> "" Then
	For Each Item In Request.Form
		If Left(Item, 2) = "ID" Then
			intID = Split(Item, "_")(1)
			strMfg1Row = Request.Form("Mfg1_" & intID)
			strMfg2Row = Request.Form("Mfg2_" & intID)
			strMfg3Row = Request.Form("Mfg3_" & intID)


			strSQL = "UPDATE tblList SET Mfg1 = '" & strMfg1Row & "', " & _
				"Mfg2 = '" & strMfg2Row & "', " & _
				"Mfg3 = '" & strMfg3Row & "' " & _
				"WHERE ItemID = " & intID
			response.write strSQL & "<br>"
		End If

	Next
End If
%>

<form method="post" action="">
<table>
<TR>
<th></th>
<th>Desc.</th>
<th>Material No.</th>
<th>Mfg1</th>
<th>Mfg2</th>
<th>Mfg3</th>
      </TR>
</CENTER>


<%
' scroll to starting record
i = 1


do while not rs.EOF
	if i > iStopRec then
		exit do
	end if
	
	i = i + 1
%>

<TR>      
<td>
	<input type=hidden value="<%= rs("ItemID") %>" name="ItemIDRow<%response.write(i)%>">
	</td>

     <td><input type="checkbox" name="ID_<%response.write(i)%>"></td>
<td><input type="text" name="Description<%response.write(i)%>" value="<%=rs("Description")%>"
</td>
<td><input type="text" name="Material<%response.write(i)%>" value="<%=rs("MaterialNum")%>"
</td>

<TD>
	<%'Check to see if they are alowed to Edit mfg1 fields
		If cint(Session("AccessLevel")) >=4 then 
			%><INPUT CLASS="clrBg" type="text" name="Mfg1Row<%=response.write(i)%>" size="9" maxlength="9" value="<%=rs("Mfg1")%>"</TD><%
		Else
			%><INPUT CLASS="clrBg" type="text" name="Mfg1Row<%=response.write(i)%>" size="9" maxlength="9" readonly value="<%=rs("Mfg1")%>"</TD>	<%
		End if
	%>
	</TD>
<TD>
	<%'Check to see if they are alowed to Edit mfg2 fields
		If cint(Session("AccessLevel")) >=4 then 
			%><INPUT CLASS="clrBg" type="text" name="Mfg2Row<%=response.write(i)%>" size="9" maxlength="9" value="<%=rs("Mfg2")%>"</TD><%
		Else
			%><INPUT CLASS="clrBg" type="text" name="Mfg2Row<%=response.write(i)%>" size="9" maxlength="9" readonly value="<%=rs("Mfg2")%>"</TD>	<%
		End if
	%>
	</TD>
<TD>
	<%'Check to see if they are alowed to Edit mfg3 fields
		If cint(Session("AccessLevel")) >=4 then 
			%><INPUT CLASS="clrBg" type="text" name="Mfg3Row<%=response.write(i)%>" size="9" maxlength="9" value="<%=rs("Mfg3")%>"</TD><%
		Else
			%><INPUT CLASS="clrBg" type="text" name="Mfg3Row<%=response.write(i)%>" size="9" maxlength="9" readonly value="<%=rs("Mfg3")%>"</TD>	<%
		End if
	%>
	</TD>

<%
	i = i + 1
  rs.MoveNext
loop

rs.Close

%>

No errors but it's not updating...my response.write strSQL outputs:

Code:

UPDATE tblList SET Mfg1 = , Mfg2 = , Mfg3 = WHERE ItemID = 2

jmurrayhead · #6 April 8th, 2008, 10:21 AM

As BLaaaaaaaaaarche said above, you shouldn't be using a counter here:

Code:

<input type="checkbox" name="ID_<%response.write(i)%>">

Simply plugin the record ID from the recordset:

Code:

<input type="checkbox" name="ID_<%=rs("ItemID")%>">

Then, if this checkbox is checked, take the corresponding ItemID and update the record.

Rebelle · #7 April 8th, 2008, 12:34 PM

Ok, Thank you BLaaaaaaaaaarche and JMurrayhead very much, I am able to update just the records that are checked now.

But since I am now doing the update on the same page I need some assistance.

I am use to having this button:

Code:

<SCRIPT>
document.write(new VMLBtn("Save","validate()",90));
</SCRIPT>

with this for the form, where my update statement on UpdateListFinish.asp:

Code:

<FORM name=frmSearch action="UpdateListFinish.asp" method="post" ONSUBMIT="return false;">

Code:

function validate(){
      if (getIsDataValid()) {
          frmSearch.submit();
      }
  }


  function getIsDataValid(){
       return true;
  }

But now since I'm using this to submit button:

Code:

<input type="submit" name="Submit" value="SAVE">

with this:

Code:

<form method="post" action="">

How can I make the below work with the line below work with my original button(VMLBtn)?

Code:

If Request.Form("submit") <> "" Then

Rebelle · #8 April 8th, 2008, 02:07 PM

disregard previous post. in order for me to use my pretty button, i've moved the update code back to the updateFinish.asp and it's working well so far.

Thanks again to both of your for your help with this!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

LinkBacks (?) LinkBack to this Thread: http://www.developerbarn.com/asp-development/95-suggestions-changedon-field.html
Posted By	For	Type	Date
DeveloperBarn Forums - ASP Help, ASP.Net Help, PHP Help, SQL Help, Tutorials, Windows Help	This thread	Refback	April 2nd, 2008 10:29 AM

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
[Feedback] User Title Suggestions	jmurrayhead	Suggestions & Feedback	15	March 21st, 2008 12:54 PM

DeveloperBarn

Suggestions - ChangedOn field

Suggestions - ChangedOn field

LinkBack to this Thread: http://www.developerbarn.com/asp-development/95-suggestions-changedon-field.html

Similar Threads

The Following User Says Thank You to BLaaaaaaaaaarche For This Useful Post:
Rebelle (June 17th, 2008)