- Remove From My Forums
-
Question
-
I using Visual Basic 2010 and MS Access 2010 database. My OS is Windows7. In my earlier complaint I had my database stored on root drive C: I changed that and the program worked fine. Using the exact same code I wrote another program using a different
database, now I get an error stating «Syntax error in UPDATE statement», the only thing I did was change the name of the database. Again, Please Help.Public Class Form1 Dim inc As Integer Dim MaxRows As Integer Dim con As New OleDb.OleDbConnection Dim dbProvider As String Dim dbSource As String Dim ds As New DataSet Dim da As New OleDb.OleDbDataAdapter Dim sql As String Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" dbSource = "Data Source = C:DataBaseIIAddressBook1.mdb" con.ConnectionString = dbProvider & dbSource con.Open() sql = "SELECT * FROM tblContacts" da = New OleDb.OleDbDataAdapter(sql, con) da.Fill(ds, "Addressbook1") 'MsgBox("Database is Open") con.Close() MaxRows = ds.Tables("AddressBook1").Rows.Count inc = -1 'MsgBox("Database is Close") End Sub Private Sub NavigateRecords() txtFirstName.Text = ds.Tables("AddressBook1").Rows(inc).Item(1) txtLastName.Text = ds.Tables("AddressBook1").Rows(inc).Item(2) txtAddress1.Text = ds.Tables("AddressBook1").Rows(inc).Item(3) txtAddress2.Text = ds.Tables("AddressBook1").Rows(inc).Item(4) txtAddress3.Text = ds.Tables("AddressBook1").Rows(inc).Item(5) txtPostCode.Text = ds.Tables("AddressBook1").Rows(inc).Item(6) End Sub Private Sub btnNext_Click(sender As System.Object, e As System.EventArgs) Handles btnNext.Click If inc <> MaxRows - 1 Then inc = inc + 1 NavigateRecords() Else MsgBox("No More Rows") End If End Sub Private Sub btnPrevious_Click(sender As System.Object, e As System.EventArgs) Handles btnPrevious.Click If inc > 0 Then inc = inc - 1 NavigateRecords() Else MsgBox("First Record") End If End Sub Private Sub btnLast_Click(sender As System.Object, e As System.EventArgs) Handles btnLast.Click If inc <> MaxRows - 1 Then inc = MaxRows - 1 NavigateRecords() End If End Sub Private Sub btnFirst_Click(sender As System.Object, e As System.EventArgs) Handles btnFirst.Click If inc <> 0 Then inc = 0 NavigateRecords() End If End Sub Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click Dim cb As New OleDb.OleDbCommandBuilder(da) ds.Tables("AddressBook1").Rows(inc).Item(1) = txtFirstName.Text ds.Tables("AddressBook1").Rows(inc).Item(2) = txtLastName.Text ds.Tables("AddressBook1").Rows(inc).Item(3) = txtAddress1.Text ds.Tables("AddressBook1").Rows(inc).Item(4) = txtAddress2.Text ds.Tables("AddressBook1").Rows(inc).Item(5) = txtAddress3.Text ds.Tables("AddressBook1").Rows(inc).Item(6) = txtPostCode.Text Try da.Update(ds, "AddressBook1") Catch ex As Exception MessageBox.Show(ex.ToString) End Try MsgBox("Data updated") End Sub Exception: --------------------------- --------------------------- System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at DataBaseII.Form1.btnUpdate_Click(Object sender, EventArgs e) in C:DataBaseIIDataBaseIIForm1.vb:line 99 --------------------------- OK ---------------------------
-
Edited by
Tuesday, September 13, 2011 6:19 AM
-
Edited by
Answers
-
Not so strange as this code you show is for Access 2003 (I see you have edited your question, Paul, Andrew and Mike see this direct and probably even I would have seen that direct)
dbProvider = «PROVIDER=Microsoft.Jet.OLEDB.4.0;»
So all of us have assumed it was Jet (Access 2003)
Change your connectionstring accoording to Access 2007/2010
http://www.connectionstrings.com/access-2007
Success
Cor-
Edited by
Cor Ligthert
Tuesday, September 13, 2011 8:17 AM -
Proposed as answer by
Mike Feng
Wednesday, September 14, 2011 7:03 AM -
Marked as answer by
Mike Feng
Tuesday, September 27, 2011 11:07 AM
-
Edited by
See more:
hi
i have «(Syntax error in UPDATE statement.)» when I updating the record in access database by c# source code:
OleDbConnection connect = new OleDbConnection(@"path.mdb"); OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "UPDATE Table2 set check=2 whene key=asd "; cmd.Connection = connect; connect.Open(); cmd.ExecuteNonQuery(); connect.Close();
thank you for solution my problems
Your keyword is incorrect .
It should be where instead of whene.
Also When you are using string in where clause, it should be written in single quotes.
like ‘asd’ instead of asd
cmd.CommandText = "UPDATE Table2 set check='2' where key='asd'";
Please check following line and update it.
Please define the datatype of field check
and key
.
cmd.CommandText = "UPDATE Table2 set check='2' where key='asd' ";
Thanks
Ashish
why are you giving check value in quotes.Try giving lik this in your code.
cmd.CommandText = "UPDATE Table2 set check=2 where key='"+richTextBox2.Text+"' ";
I had the same problem long back, i added textbaox1.text.tostring(); it works fine.
in your code also use richTextBox2.Text.tostring();
I am also getting same error syntax error in update command. . .I am using 64 bit win 8.1 operating system. . . all other commands are executing except update. . please try to solve my problem. .
String str = «update transaction_tab set desc = ‘updated’ where tcode = ‘JV'»;
Cmd = oledbCommand(str,con);
Cmd.ExecuteNonQuery();
Updated 18-Jun-15 19:54pm
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
Forum Rules |
|
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Contact US
Thanks. We have received your request and will respond promptly.
Log In
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips Forums!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts - Keyword Search
- One-Click Access To Your
Favorite Forums - Automated Signatures
On Your Posts - Best Of All, It’s Free!
*Tek-Tips’s functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Students Click Here
Syntax error in update statementSyntax error in update statement(OP) 21 Dec 04 09:20 Hi all. I’m a bit stuck on this problem i’m having with my update-query. I’ve tried my best to write an sql query to update the fields of one table with the value of fields in another table, but i keep on receiving this annoying «syntax error in update statement». Is there somebody who can help me out with this issue please? This is what i’ve accomplished so far: UPDATE SortedFullExcelList p Uhm…any idea’s? Red Flag SubmittedThank you for helping keep Tek-Tips Forums free from inappropriate posts. |
Join Tek-Tips® Today!
Join your peers on the Internet’s largest technical computer professional community.
It’s easy to join and it’s free.
Here’s Why Members Love Tek-Tips Forums:
Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More…
Register now while it’s still free!
Already a member? Close this window and log in.
Join Us Close