SQL Update command with VB.NET
-
Hello everybody. I have a weird error message coming up every time i try to use the Update command with a access database. The message says that there is a syntax error with the SQL command. I search the Internet and the syntax of my command seems to be right. Here is the code: ============================================================================ Public Sub updateRecord(ByVal entryToChange As String, ByVal oldString As String, ByVal newString As String) Dim SQLString As String Dim strConnection As String = OleDbConnection1.ConnectionString Dim objTransaction As OleDbTransaction Dim connect As New OleDbConnection(strConnection) SQLString = "UPDATE tblIns SET " & entryToChange & " = '" & newString & "' WHERE Ins = '" & oldString & "'" Dim cmd As New OleDbCommand(SQLString, connect) connect.Open() Try cmd.ExecuteNonQuery() Catch ex As Exception MsgBox(ex.ToString) End Try connect.Close() connect.Dispose() Me.Close() End Sub ============================================================================ I have a value that changes (newString) and a value that i know it's the same (oldString). entryToChange is the entry that i'm trying to change (eg Date, or FirstName etc) So basicly i'm trying to change a value of a record where the oldString stays the same. But i get the error "Syntax error with UPDATE command". What is going on? And is there a way to update a record when the entry that is changing is not the same all the time? Meaning in one record the first name may change and all other entries (eg last name, address etc) stays the same, in the other the last name may change and the rest of the entries will stay the same. In VB6 i used to use something like: ============================================================== DatIns.Recordset.Edit DatIns.Recordset.Fields("Date").Value = lbldate.Caption ============================================================== but in vb.NET i can't find something like this. Thank you for your help, and Merry Christmas to all. Still trying to find the way -- modified at 18:27 Tuesday 20th December, 2005
-
Hello everybody. I have a weird error message coming up every time i try to use the Update command with a access database. The message says that there is a syntax error with the SQL command. I search the Internet and the syntax of my command seems to be right. Here is the code: ============================================================================ Public Sub updateRecord(ByVal entryToChange As String, ByVal oldString As String, ByVal newString As String) Dim SQLString As String Dim strConnection As String = OleDbConnection1.ConnectionString Dim objTransaction As OleDbTransaction Dim connect As New OleDbConnection(strConnection) SQLString = "UPDATE tblIns SET " & entryToChange & " = '" & newString & "' WHERE Ins = '" & oldString & "'" Dim cmd As New OleDbCommand(SQLString, connect) connect.Open() Try cmd.ExecuteNonQuery() Catch ex As Exception MsgBox(ex.ToString) End Try connect.Close() connect.Dispose() Me.Close() End Sub ============================================================================ I have a value that changes (newString) and a value that i know it's the same (oldString). entryToChange is the entry that i'm trying to change (eg Date, or FirstName etc) So basicly i'm trying to change a value of a record where the oldString stays the same. But i get the error "Syntax error with UPDATE command". What is going on? And is there a way to update a record when the entry that is changing is not the same all the time? Meaning in one record the first name may change and all other entries (eg last name, address etc) stays the same, in the other the last name may change and the rest of the entries will stay the same. In VB6 i used to use something like: ============================================================== DatIns.Recordset.Edit DatIns.Recordset.Fields("Date").Value = lbldate.Caption ============================================================== but in vb.NET i can't find something like this. Thank you for your help, and Merry Christmas to all. Still trying to find the way -- modified at 18:27 Tuesday 20th December, 2005
-
What is the value of SQLString when cmd.ExecuteNonQuery() executes? -- modified at 8:41 Wednesday 21st December, 2005
The SQLString ends up looking like this: SQLString = "UPDATE tblIns SET Date= '21/12/05' WHERE Job_Number = '0909090' which looks like it's correct. But i still get an error message of "Syntax error". What am i doing wrong? Still trying to find the way
-
The SQLString ends up looking like this: SQLString = "UPDATE tblIns SET Date= '21/12/05' WHERE Job_Number = '0909090' which looks like it's correct. But i still get an error message of "Syntax error". What am i doing wrong? Still trying to find the way