how to update multiple rows in table?

[santosh1]

Currently I do the multiple rows update by using the dataset and looping
the update SQL query the number of times the rows exist in the dataset. Below is an example. This seems odd to me, Is there a better way I can update multiple rows in the table? Thanks in advance.


Private Function updateEmployee(ByVal ds As DataSet)
Dim irow As DataRow
Dim strSQL As String
Dim myCommand As OleDbCommand
Dim conn As OleDbConnection
Dim intRowsAffected As Integer
dim name, phone, email as string

conn = New OleDbConnection(ConnectionString)
conn.Open()
Try
For Each irow In ds.Tables(0).Rows

name = irow.Item("Name&quot
phone = irow.Item("Phone&quot
email = irow.Item("email&quot

strSQL = "UPDATE Employee_Detail SET" & " "
strSQL += "EmployeeName= " & PrepareStr(name) & ", "
strSQL += "Phone = " & PrepareStr(phone) & ", "
strSQL += "Email = " & PrepareStr(email) & " "
strSQL += "WHERE (((EmployeeID)=" & PrepareStr(employeeID) & &quot) "

myCommand = New OleDbCommand(strSQL, conn)
intRowsAffected = myCommand.ExecuteNonQuery()
Next

Finally
conn.Close()
conn.Dispose()
myCommand.Dispose()
End Try
End function

 

[chiph]

Yes, you can combine multiple updates into one SQL statement, separating them with the semi-colon operator.

The downside is that if any one of them fail (foreign key constraint violation, etc) then you won't be able to tell which one failed. You'd have to have a strategy of then looping through the 10 (or however) many you batched up to find which one fails, and try and get the other 9 in too.

BTW, your query will run much faster if you use ADO.NET parameters, as the database engine will be able to cache your parsed statement, and not check it for syntax errors every time you submit it. You will also not be vulnerable to a common hacking attack, *and* won't have any problems with single-quotes in the data (no need for your PrepareStr function). See faq709-1526 for more info.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first