Better Way for Updating Multiple Rows.

[Fly]

Which is the best way to update multiple rows from a html table.<br>
Please give suggession and code sample.

 

[jfriestman]

There are actually several ways to update a Database table with ASP - two that come to mind quickly are<br>
1. using the Execute method on the Connection object and passing an UPDATE sql statement to the DB or<br>
2. using the Update method on the Recordset object passing new field values.<br>
<br>
You asked for the best but you didn't specify your criteria... Best (easiest) is using the Connection object's execute method, best (fastest) is probably using the Recordset object. That's just what I've read, I haven't benchmarked it.<br>
<br>
Anyway, I've provided a code example below that uses the first method I describe, Connection.Execute.<br>
<br>
This requires the nwind.mdb to be copied into your C:\temp folder, or modify the Connection string (dsn variable) to point to the location of your Nwind.mdb.<br>
<br>
<br>
<br>
&lt;%@ language="VBScript"%&gt;<br>
&lt;% Option Explicit %&gt;<br>
<br>
&lt;%<br>
<br>
Sub UpdateDatabase(sql, dsn)<br>
On Error Resume Next<br>
Dim oCn<br>
Set oCn = Server.Createobject("ADODB.Connection")<br>
oCn.Open dsn<br>
oCn.Execute sql<br>
oCn.Close<br>
Set oCn = Nothing<br>
If Err.number &lt;&gt; 0 Then<br>
Response.Write "&lt;br&gt;" & Err.Number & "&nbsp;" & Err.Description & "&lt;br&gt;" & sql & "&lt;br&gt;"<br>
End If<br>
End Sub<br>
<br>
%&gt;<br>
<br>
&lt;html&gt;<br>
&lt;head&gt;<br>
&lt;title&gt;&lt;/title&gt;<br>
&lt;/head&gt;<br>
&lt;body&gt;<br>
<br>
&lt;%<br>
Dim dsn, sql<br>
dsn="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\temp\nwind.mdb"<br>
sql = "update customers set city = 'Berlin2' where city = 'Berlin'"<br>
UpdateDatabase sql, dsn<br>
%&gt;<br>
<br>
&lt;/body&gt;<br>
&lt;/html&gt;

 

[srinuk123]

hello all<br>
i have a question<br>
if the client wants to update a selective rows freom the sql server database how should we go about it. that too if we have to show a record by record to the client and ask him to update it. please suggest me the code also.<br>
i will be very thankful.<br>

 

[macr0]

each record should have a unique ID. Use a self-submitting Form to get the User's request. the ASP code (using ADO and MSDE) is this<br>
<br>
If Request.Form(&quot;btnSubmit&quot = &quot;Submit&quot; Then<br>
Dim objConn, objRs, StrSql, IdFromForm<br>
IdFromForm = Request.Form(&quot;RequestedID&quot<br>
<br>
StrSql = &quot;select * from dbase where ID like '&quot; &_<br>
IdFromForm & &quot;'&quot;<br>
<br>
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot<br>
Set objRs = Server.CreateObject(&quot;ADODB.Recordset&quot<br>
<br>
objConn.Open &quot;Provider=SQLOLEDB;User ID=sa; &quot; &_<br>
&quot;Initial File Name=C:\data\base\source.mdf&quot;<br>
objRs.Open StrSql, objConn, 3, 3<br>
If Not objRs.EOF Then<br>
'Here the updating to the fields go<br>
<br>
End If<br>
objRs.update<br>
objRs.close<br>
objConn.close<br>
End If<br>
<br>
If you have multiple rows to update, use a loop and make sure your cursor is dynamic.<br>
<br>
The most efficent way to show record by record update is to parse all of the updates into an Array that is relative to the RS(I really don't know how you want to display Record by Record updating). and then update the recordset. Something like this.<br>
<br>
If Not objRs.BOF Then<br>
objRs.MoveFirst<br>
End If<br>
numrows = ubound(DataArray, 2)<br>
For i = 0 To numrows<br>
uniqueId = DataArray(5,i) 'Whatever the ID is<br>
If CInt(uniqueId) = CInt(objRs.Fields(&quot;ID&quot) Then<br>
objRs.Fields(&quot;FirstField&quot = DataArray(1,i)<br>
objRs.Fields(&quot;SecondField&quot = DataArray(2,i)<br>
'////.....etc<br>
End If<br>
objRs.MoveNext<br>
Next<br>
objRs.Update<br>
<br>
<br>
<br>
Hope This Helped,<br>
Jeremy Lowery MCP<br>