navigation
a webmaster learning community
     Home    Register     Search      Help      Login    
Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums
 

Advanced search
Recent Posts

 Todays Posts
 Most Active posts
 Posts since last visit
 My Recent Posts
 Mark posts read

Microsoft MVP

 

Update multiple records

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> ASP and Database >> Update multiple records 		Page: [1]
 
jgerding13

 

Posts: 6
Joined: 3/11/2008
Status: offline

  		Update multiple records - 4/1/2008 14:57:38   
I found a piece of code to allow me to update multiple tables at once, but I am getting a synrax error in the update query.
I am new to ASP so if someone could point me in the right direction I would appreciate it. 

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
If Request("Submit") <> "" Then
	intIDs = Replace(Request("hidIDs"), "*", "")  ' remove all the asterisks, to create a list like this: 2, 5, 8, 9 etc.
	arrIDs = Split(intIDs, ", ")				' Create an array, wich will contain just the IDs of the records we need to update
	For i = 0 to Ubound(arrIDs)					' Loop trough the array
		strText = Replace(Request("txtText" & arrIDs(i)), "'", "''")
		intNum  = Replace(Request("txtNum" & arrIDs(i)), "'", "''")
		
		set commUpdate = Server.CreateObject("ADODB.Command")
		commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../machines.mdb") & ";Persist Security Info=False"
		commUpdate.CommandText = "UPDATE Beverage  SET Jan08 = '" & strText & "', Feb08 = " & intNum & " WHERE ID = " & arrIDs(i)		
		commUpdate.CommandType = 1
		commUpdate.CommandTimeout = 0
		commUpdate.Prepared = true
		commUpdate.Execute()
	Next
	strMessage = i & " Records Updated"
	Response.Redirect("testupdate.asp?Message=" & strMessage)
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../machines.mdb") & ";Persist Security Info=False"
Recordset1.Source = "SELECT ID, Jan08, Feb08  FROM Beverage"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Update Demo</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">
<!--
//  When the value in a textfield is changed, notice the onChange="RecUpdate('<%= intID %>')"
//  on each of the textfields,  the value of the Record ID associated with that field
//  is passed to the RecUpdate function. First the value is surounded with 2 asterisks e.g. *6*
//  This is so that *1* can be distinguished from *10*, *11* etc.
function RecUpdate(ID){
var ThisID = "*" + (ID) + "*"
if (document.form1.hidIDs.value == ""){	// If the hidden field is empty
document.form1.hidIDs.value = (ThisID)	// Store the value in the hidden field (hidIDs) as it is.
}
if (document.form1.hidIDs.value != ""){  // If the hidden field isn't empty
var str = document.form1.hidIDs.value;	// Store the contents of the hidden field in the variable str
var pos = str.indexOf(ThisID);				// Search str to see if this ID is allready in it.
if (pos == -1) {							// If the position returned is -1 it isn't allredy in there,  
document.form1.hidIDs.value = document.form1.hidIDs.value + ", " + (ThisID)  
} 											// so add ", " and this ID to what is already in hidIDs 
}											// to create a list like this *2*, *5*, *8* etc.	
}
//-->
</script>
</head>
<body>
<font size="2" face="Arial, Helvetica, sans-serif"><%= Request.QueryString("Message") %></font>
<br>
<form name="form1" method="POST" action="testupdate.asp">
<table width="500" border="0" cellpadding="0" cellspacing="1" bgcolor="#666699">
  <tr>
    <td><table width="500" border="0" cellpadding="0" cellspacing="6" bgcolor="#EEEFF2">
      <tr>
        <td width="37"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">ID</font></strong></td>
        <td width="217"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">Jan08</font></strong></td>
        <td width="226"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">Feb08</font></strong></td>
      </tr>
      <% 
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) 
%>
<% intID =(Recordset1.Fields.Item("ID").Value) ' Store the current RecordID in a variable %>
      <tr>
        <td nowrap><font size="1" face="Arial, Helvetica, sans-serif"><%= intID %><input name="hidID<%= intID %>" type="hidden" value="<%= intID %>" size="5"></font></td>
        <td nowrap><font size="1" face="Arial, Helvetica, sans-serif"><input name="txtText<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Jan08").Value)%>" size="20"></font></td>
        <td nowrap><font size="1" face="Arial, Helvetica, sans-serif"><input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Feb08").Value)%>" size="20"></font></td>
      </tr>
      <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>
    </table></td>
  </tr>
</table>
<br>
<input name="hidIDs" type="text" size="40">
<font size="2" face="Arial, Helvetica, sans-serif"><=== This would be hidden</font><br>
<br>
<font size="2" face="Arial, Helvetica, sans-serif">
<input type="submit" name="Submit" value="Update">
</font>
</form>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>


< Message edited by rdouglass -- 4/1/2008 15:46:21 >
rdouglass

 

Posts: 9167
From: Biddeford, ME USA
Status: offline

  		RE: Update multiple records - 4/1/2008 15:47:01   
What is the exact error you're getting?

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to jgerding13)
jgerding13

 

Posts: 6
Joined: 3/11/2008
Status: offline

  		RE: Update multiple records - 4/1/2008 15:49:20   
Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement.

/employee/intranet/sales/machines/testupdate.asp, line 16


If I update no records it gives me the message 0 records updated
but if I try to update a record I get the above

(in reply to rdouglass)
rdouglass

 

Posts: 9167
From: Biddeford, ME USA
Status: offline

  		RE: Update multiple records - 4/1/2008 15:55:27   
quote:

... SET Jan08 = '" & strText & "', Feb08 = " & intNum & " ...


Right there you're updating Jan08 as a string and Feb08 as a number. Are they defined those field types in the DB? It would seem to me that they (the Jan08 and Feb08 fields) would both be a string or a number and not one of each.

That help any?

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to jgerding13)
Spooky

 

Posts: 26597
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Update multiple records - 4/1/2008 15:55:41   
Why does jan08 have quotes and feb08 doesnt ? Are they not the same database type?

<edit> ooops too slow :)

(in reply to jgerding13)
rdouglass

 

Posts: 9167
From: Biddeford, ME USA
Status: offline

  		RE: Update multiple records - 4/1/2008 15:57:43   
quote:

<edit> ooops too slow :)


Hey, I *finally* beat you to one. I'm usually the one saying that. :)

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to Spooky)
jgerding13

 

Posts: 6
Joined: 3/11/2008
Status: offline

  		RE: Update multiple records - 4/1/2008 16:00:33   
They are both Text fields so I changed the code to

"UPDATE Beverage SET Jan08 = '" & strText & "', Feb08 = " & strNum & " WHERE ID = " & arrIDs(i)

and still get the exact same error

(in reply to rdouglass)
jgerding13

 

Posts: 6
Joined: 3/11/2008
Status: offline

  		RE: Update multiple records - 4/1/2008 16:06:15   
Like I said I found the piece of code and modified it so I am trying to make it work for my needs and really don't know asp at all what should the update statement be?

commUpdate.CommandText = "UPDATE Beverage SET Jan08 = '" & strText & ", Feb08 = " & strNum & '" WHERE ID = " & arrIDs(i)

Both Jan08 and Feb08 are text fields in the DB

I appreciate the help

(in reply to rdouglass)
rdouglass

 

Posts: 9167
From: Biddeford, ME USA
Status: offline

  		RE: Update multiple records - 4/1/2008 16:19:39   
quote:

commUpdate.CommandText = "UPDATE Beverage SET Jan08 = '" & strText & "', Feb08 = " & intNum & " WHERE ID = " & arrIDs(i)


Well if they're both strings, you want to do it this way:

commUpdate.CommandText = "UPDATE Beverage SET Jan08 = '" & strText & "', Feb08 = '" & intNum & "' WHERE ID = " & arrIDs(i)

Pay close attention to the apostrophes around intNum; you need apostrophes around text strings.

However, are you sure that's the item you want to use? It doesn't make sense to me and of course I have no clue as to what your DB looks like.

Maybe you don't want the fields Jan08 and Feb08 after all but I don't know what your goal is.

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to jgerding13)
rdouglass

 

Posts: 9167
From: Biddeford, ME USA
Status: offline

  		RE: Update multiple records - 4/1/2008 16:21:39   

quote:

ORIGINAL: jgerding13

They are both Text fields so I changed the code to

"UPDATE Beverage SET Jan08 = '" & strText & "', Feb08 = " & strNum & " WHERE ID = " & arrIDs(i)

and still get the exact same error


Be careful of that. By changing a variable name from intNum to strNum does not change the item from a number to a string.


_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to jgerding13)
jgerding13

 

Posts: 6
Joined: 3/11/2008
Status: offline

  		RE: Update multiple records - 4/1/2008 17:04:15   
Ok thanks I think I am closer, here is my final code below
But what ever value I put in Jan08 box gets replicated to all months 

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
If Request("Submit") <> "" Then
	intIDs = Replace(Request("hidIDs"), "*", "")  ' remove all the asterisks, to create a list like this: 2, 5, 8, 9 etc.
	arrIDs = Split(intIDs, ", ")				' Create an array, wich will contain just the IDs of the records we need to update
	For i = 0 to Ubound(arrIDs)					' Loop trough the array
		strText = Replace(Request("txtText" & arrIDs(i)), "'", "''")
		intNum  = Replace(Request("txtNum" & arrIDs(i)), "'", "''")
		
		set commUpdate = Server.CreateObject("ADODB.Command")
		commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../machines.mdb") & ";Persist Security Info=False"
		commUpdate.CommandText = "UPDATE Beverage  SET Jan08 = " & strText & ",Feb08 = " & strText & ",March08 = " & strText & ",Qtr_1_2008 = " & strText & ",Apr08 = " & strText & ",May08 = " & strText & ",June08 = " & strText & ",Qtr_2_2008 = " & strText & ",July08 = " & strText & ",Aug08 = " & strText & ",Sept08 = " & strText & ",Qtr_3_2008 = " & strText & ",Oct_08 = " & strText & ",Nov_08 = " & strText & ",Dec_08 = " & strText & ",Qtr_4_2008 = " & strText & ",Total = " & strText & "   WHERE ID = " & arrIDs(i)		
		commUpdate.CommandType = 1
		commUpdate.CommandTimeout = 0
		commUpdate.Prepared = true
		commUpdate.Execute()
	Next
	strMessage = i & " Records Updated"
	Response.Redirect("testupdate.asp?Message=" & strMessage)
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../machines.mdb") & ";Persist Security Info=False"
Recordset1.Source = "SELECT *  FROM Beverage"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Update Demo</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">
<!--
//  When the value in a textfield is changed, notice the onChange="RecUpdate('<%= intID %>')"
//  on each of the textfields,  the value of the Record ID associated with that field
//  is passed to the RecUpdate function. First the value is surounded with 2 asterisks e.g. *6*
//  This is so that *1* can be distinguished from *10*, *11* etc.
function RecUpdate(ID){
var ThisID = "*" + (ID) + "*"
if (document.form1.hidIDs.value == ""){	// If the hidden field is empty
document.form1.hidIDs.value = (ThisID)	// Store the value in the hidden field (hidIDs) as it is.
}
if (document.form1.hidIDs.value != ""){  // If the hidden field isn't empty
var str = document.form1.hidIDs.value;	// Store the contents of the hidden field in the variable str
var pos = str.indexOf(ThisID);				// Search str to see if this ID is allready in it.
if (pos == -1) {							// If the position returned is -1 it isn't allredy in there,  
document.form1.hidIDs.value = document.form1.hidIDs.value + ", " + (ThisID)  
} 											// so add ", " and this ID to what is already in hidIDs 
}											// to create a list like this *2*, *5*, *8* etc.	
}
//-->
</script>
</head>
<body>
<font size="2" face="Arial, Helvetica, sans-serif"><%= Request.QueryString("Message") %></font>
<br>
<form name="form1" method="POST" action="testupdate.asp">
<table width="100%" border="0" cellpadding="0" cellspacing="0" bgcolor="#666699" style="border-collapse: collapse" bordercolor="#111111">
  <tr>
    <td width="973">
    <table width="1254" border="0" cellpadding="0" cellspacing="1" bgcolor="#EEEFF2" style="border-collapse: collapse" bordercolor="#111111" height="54">
      <tr>
        <td width="11" nowrap height="24"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">ID</font></strong></td>
        <td width="98" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">Cust 
        No</font></strong></td>
        <td width="231" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">Name</font></strong></td>
        <td width="35" nowrap height="24"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">Jan08</font></strong></td>
        <td width="35" nowrap height="24"><strong><font color="#333333" size="1" face="Arial, Helvetica, sans-serif">Feb08</font></strong></td>
        <td width="36" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">Mar08</font></strong></td>
        <td width="33" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">1st 
        QTR</font></strong></td>
        <td width="33" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">Apr08</font></strong></td>
        <td width="37" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">May08</font></strong></td>
        <td width="43" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">
        June08</font></strong></td>
        <td width="34" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">2nd 
        Qtr</font></strong></td>
        <td width="38" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">
        July08</font></strong></td>
        <td width="37" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">Aug08</font></strong></td>
        <td width="40" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">
        Sept08</font></strong></td>
        <td width="34" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">3rd 
        Qtr</font></strong></td>
        <td width="34" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">Oct08</font></strong></td>
        <td width="38" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">Nov08</font></strong></td>
        <td width="37" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">Dec08</font></strong></td>
        <td width="34" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">4th 
        Qtr</font></strong></td>
        <td width="431" nowrap height="24"><strong>
        <font face="Arial, Helvetica, sans-serif" size="1" color="#333333">Total</font></strong></td>
      </tr>
      <% 
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) 
%>
<% intID =(Recordset1.Fields.Item("ID").Value) ' Store the current RecordID in a variable %>
      <tr>
        <td nowrap width="11" height="12"><font size="1" face="Arial, Helvetica, sans-serif"><%= intID %></font><font size="1" face="Arial"><input name="hidID<%= intID %>" type="hidden" value="<%= intID %>" size="5"></font></td>
        <td nowrap width="98" valign="bottom" height="12"><font size="1" face="Arial"><%=(Recordset1.Fields.Item("Cust_No").Value)%><p></td>
        <td nowrap width="231" height="12"><font size="1" face="Arial"><%=(Recordset1.Fields.Item("Customer_Name").Value)%></td>
        <td nowrap width="35" height="12"><font size="1" face="Arial">
        <input name="txtText<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Jan08").Value)%>" size="3"></font></td>
        <td nowrap width="35" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>1" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Feb08").Value)%>" size="3"></font></td>
        <td nowrap width="36" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("March08").Value)%>" size="3"></font></td>
        <td nowrap width="33" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Qtr_1_2008").Value)%>" size="3"></font></td>
        <td nowrap width="33" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Apr08").Value)%>" size="3"></font></td>
        <td nowrap width="37" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("May08").Value)%>" size="3"></font></td>
        <td nowrap width="43" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("June08").Value)%>" size="3"></font></td>
        <td nowrap width="34" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Qtr_2_2008").Value)%>" size="3"></font></td>
        <td nowrap width="38" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("July08").Value)%>" size="3"></font></td>
        <td nowrap width="37" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Aug08").Value)%>" size="3"></font></td>
        <td nowrap width="40" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Sept08").Value)%>" size="3"></font></td>
        <td nowrap width="34" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Qtr_3_2008").Value)%>" size="3"></font></td>
        <td nowrap width="34" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Oct_08").Value)%>" size="3"></font></td>
        <td nowrap width="38" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Nov_08").Value)%>" size="3"></font></td>
        <td nowrap width="37" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Dec_08").Value)%>" size="3"></font></td>
        <td nowrap width="34" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Qtr_4_2008").Value)%>" size="3"></font></td>
        <td nowrap width="431" height="12"><font size="1" face="Arial">
        <input name="txtNum<%= intID %>" type="text" onChange="RecUpdate('<%= intID %>')" value="<%=(Recordset1.Fields.Item("Total").Value)%>" size="3"></font></td>
      </tr>
      <tr>
        <td nowrap width="1368" height="15" colspan="20">
        <hr color="#000000" size="1" noshade></td>
      <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>
      
        </tr>
    </table></td>
  </tr>
</table>
<br>
<input name="hidIDs" type="text" size="40">
<font size="2" face="Arial, Helvetica, sans-serif"><=== This would be hidden</font><br>
<br>
<font size="2" face="Arial, Helvetica, sans-serif">
<input type="submit" name="Submit" value="Update">
</font>
</form>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>


< Message edited by rdouglass -- 4/1/2008 19:14:59 >

(in reply to rdouglass)
Spooky

 

Posts: 26597
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Update multiple records - 4/1/2008 19:18:27   
Test write the SQL to ensure you are sending what you think you are sending to the database : 

strSQL = "UPDATE Beverage  SET Jan08 = " & strText & ",Feb08 = " & strText & ",March08 = " & strText & ",Qtr_1_2008 = " & strText & ",Apr08 = " & strText & ",May08 = " & strText & ",June08 = " & strText & ",Qtr_2_2008 = " & strText & ",July08 = " & strText & ",Aug08 = " & strText & ",Sept08 = " & strText & ",Qtr_3_2008 = " & strText & ",Oct_08 = " & strText & ",Nov_08 = " & strText & ",Dec_08 = " & strText & ",Qtr_4_2008 = " & strText & ",Total = " & strText & "   WHERE ID = " & arrIDs(i)

Response.write strSQL &"<br>"

		'set commUpdate = Server.CreateObject("ADODB.Command")
		'commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../machines.mdb") & ";Persist Security Info=False"
		'commUpdate.CommandText = strSQL	
		'commUpdate.CommandType = 1
		'commUpdate.CommandTimeout = 0
		'commUpdate.Prepared = true
		'commUpdate.Execute()


Its getting repeated as you use "strText " for all values.




_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to jgerding13)
rdouglass

 

Posts: 9167
From: Biddeford, ME USA
Status: offline

  		RE: Update multiple records - 4/1/2008 19:23:38   
quote:

But what ever value I put in Jan08 box gets replicated to all months


Yes, you are using the same variable (strText) for each field. I don't think that's what you want. You may be going at this from the wrong angle and again I don't know your whole project so I'm afraid to suggest something that may either 1) send you down the wrong road, or 2) spend a lot of time going back and forth with Q & A.

Should each field (Jan08, Feb08, etc.) be using each array item individually (arrIDs(i))? To be honest, I'm not at all confident your logic is in the right place. I certainly don't want to lead you astray but I also am a little reluctant to suggest maybe you're uncomfortable with some concepts?

Looping arrays for instance? That would be a "must have" prerequisite for this script. Unfortunately, I won't re-iterate how to use arrays and stuff. There are hundreds of examples available thru Google. But you probably should have at least a working knowledge of arrays for this script.

Hope it helps.

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to jgerding13)
Page:   [1]

All Forums >> Web Development >> ASP and Database >> Update multiple records 		Page: [1]
Jump to: 1





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Back to top

  Microsoft Corporation in no way endorses or is affiliated with "OutFront."
  OutFront is published and edited by Thomas Brunt.
  440 Great Circle Dr., St. Matthews, SC 29135.
  © 2008 All Rights Reserved
  Click here to view our privacy statement.