Problem with Updating record

[snow1]

Hi, I have an update form where user can update any record in the database by filling in new values in textboxes. I have succeeded in updating ONE FIELD already, however, i want to be able to update many fields in just one update statement. I know the statement should be "UPDATE TABLENAME SET FIELD1=value1, FIELD2= Value2... WHERE ...." but it just won't work. can anyone help me? below is my code (for updating a single field):

Public Function UpdateData(ByVal TableName As String, ByVal Field As String, ByVal NewValue As String, ByVal WhereField As String, ByVal WhereValue As String)
Dim Str As String
Dim ObjConnection As New OleDb.OleDbConnection
ObjConnection = MyConnection()
Str = "UPDATE " & TableName & " SET " & Field & "=" & NewValue & " WHERE " & WhereField & "=" & WhereValue
MsgBox(Str)
Dim ObjCommand As New OleDb.OleDbCommand
ObjCommand.Connection = ObjConnection
ObjCommand.CommandType = CommandType.Text
ObjCommand.CommandText = Str
Try
ObjCommand.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try

End Function

Private Sub cmdSubmit_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click

Dim LastName As String = "'" & txtLastName1.Text & "'"
Dim FirstName As String = "'" & txtFirstName1.Text & "'"
Dim MiddleName As String = "'" & txtMiddleName1.Text & "'"
Try
Processor.UpdateData("Employee", "LastName", LastName, "UserID", Global.UserName) Catch ex As Exception
MsgBox(ex.Message)
End Try


End Sub

 

[fpineda101]

I think you are missing single quotes around your varchar values...

Str = "UPDATE " & TableName & " SET " & Field & "= '" & NewValue & "' WHERE " & WhereField & "= '" & WhereValue & "'"

 

[fpineda101]

When updating multiple fields, you follow this syntax...

UPDATE table_name
SET column1 = value1,
column2 = value2,
column3 = value3
WHERE this_column = this_condition

You simply add a comma at the end of each value being updated except for the last one

 

[TechGnome]

I'm too tired to write another post on the dangers of concatenating string for SQL use. Parameterized Queries are the way to go. Especialy in .NET.

Here's a thread where it gets discussed at some length. http://vbforums.com/showthread.php?p=2490199 - yes I know it's on a different board, but the information is relevant. And, yes, I know that the thread talks about VB6 ADO, but the information on concatenated SQL strings vs a parameterized query is still valid. So the code sample won't work either... concept is still the same: Create a SQL string with the placeholders in it, then append parameters to the command object and set their values. LAstly, execute the statement (if it's an action query - insert, delete, update) or fill the datatable (if it's a select) - or you could go with a data reader if you like.

One of these days I need to get around to writing up an article on this stuff....

-tg