i can't to insert a new row
-
I'm tring to add a row at my database .mdb but i get the follow error: "Sintax error in the INSERT INTO instruction" why? what do i wrong? this is the schema of my database: Nome: text 15 Domanda: text 20 Risposta: text 15 Email: text 20 this is the code: System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(); conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data source= myDatabase.mdb;"; try { conn.Open(); OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT * FROM myTable", conn); OleDbCommandBuilder myCmd = new OleDbCommandBuilder(myAdapter); DataSet myDataSet = new DataSet("myTable"); myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; myAdapter.Fill(myDataSet, "myTable"); DataRow myRow = myDataSet.Tables["myTable"].NewRow(); myRow["Nome"] = mNuovoUtente.mName; myRow["Domanda"] = mNuovoUtente.mQuestion; myRow["Risposta"] = mNuovoUtente.mAnswer; myRow["Email"] = mNuovoUtente.mEmail; myDataSet.Tables["TabellaUtente"].Rows.Add(myRow); myAdapter.Update(myDataSet, "myTable"); catch(Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); }
What is the syntax for your myAdapter.UpdateCommand and myAdapter.InsertCommand? Torin Blair
'In the immortal words of Socrates - "I drank what?".' -
What is the syntax for your myAdapter.UpdateCommand and myAdapter.InsertCommand? Torin Blair
'In the immortal words of Socrates - "I drank what?".' -
In order for you to be able to update or insert from your dataadapter, you need to specify an insert SQL statement in the OleDbDataAdapter.InsertCommand, and an update SQL statement in the OleDbDataAdapter.UpdateCommand. If you used the wizard to create your adapter, you probably already have these statements. In debug, or by writing out to a label on the screen, you should output both of these properties and see if their SQL Syntax is correct. This will allow you see the sql commands you are using with the database. These strings are probably also listed in the WinForms or WebForms generated code if you are using Visual Studio .NET Torin Blair
'In the immortal words of Socrates - "I drank what?".' -
In order for you to be able to update or insert from your dataadapter, you need to specify an insert SQL statement in the OleDbDataAdapter.InsertCommand, and an update SQL statement in the OleDbDataAdapter.UpdateCommand. If you used the wizard to create your adapter, you probably already have these statements. In debug, or by writing out to a label on the screen, you should output both of these properties and see if their SQL Syntax is correct. This will allow you see the sql commands you are using with the database. These strings are probably also listed in the WinForms or WebForms generated code if you are using Visual Studio .NET Torin Blair
'In the immortal words of Socrates - "I drank what?".' -
If you drag a data adapter to a form, then it will open a wizard for you. No worries you can do this the manual way.
OleDbCommand cmdUpdate = new OleDbCommand(_SQLCOMMAND_); OleDbCommand cmdInsert = new OleDbCommand(_SQLCOMMAND_); myAdapter.UpdateCommand = cmdUpdate; myAdapter.InsertCommand = cmdInsert;And that's how it's done. For a more detailed explanation you can look at the .NET sdk documentation or this example from csharpcorner. http://www.csharpfriends.com/Articles/getArticle.aspx?articleID=103[^] Torin Blair
'In the immortal words of Socrates - "I drank what?".' -
If you drag a data adapter to a form, then it will open a wizard for you. No worries you can do this the manual way.
OleDbCommand cmdUpdate = new OleDbCommand(_SQLCOMMAND_); OleDbCommand cmdInsert = new OleDbCommand(_SQLCOMMAND_); myAdapter.UpdateCommand = cmdUpdate; myAdapter.InsertCommand = cmdInsert;And that's how it's done. For a more detailed explanation you can look at the .NET sdk documentation or this example from csharpcorner. http://www.csharpfriends.com/Articles/getArticle.aspx?articleID=103[^] Torin Blair
'In the immortal words of Socrates - "I drank what?".'Hi, tojamismis. Thanks for the answer... Another user has wrote me this: the first problem is that your table doesn't show a primary key. Without it, .Update() won't work. Second, you, apparently, don't have an Update command associated with the DataAdapter. Third, if the Primary Key information is missing, you set the .MissingSchemaAction, but never called .FillSchema before you called .Fill In your opinion what other things have i miss?
-
Hi, tojamismis. Thanks for the answer... Another user has wrote me this: the first problem is that your table doesn't show a primary key. Without it, .Update() won't work. Second, you, apparently, don't have an Update command associated with the DataAdapter. Third, if the Primary Key information is missing, you set the .MissingSchemaAction, but never called .FillSchema before you called .Fill In your opinion what other things have i miss?
-
Hi, tojamismis. Thanks for the answer... Another user has wrote me this: the first problem is that your table doesn't show a primary key. Without it, .Update() won't work. Second, you, apparently, don't have an Update command associated with the DataAdapter. Third, if the Primary Key information is missing, you set the .MissingSchemaAction, but never called .FillSchema before you called .Fill In your opinion what other things have i miss?
I can't think of anything else. It is true that you need these additional things if you aren't using a strongly typed dataset. But that should be it. Torin Blair
'In the immortal words of Socrates - "I drank what?".' -
I don't know the structure of your tables so I can't write any of the sql statements. Torin Blair
'In the immortal words of Socrates - "I drank what?".' -
Hi, tojamismis. Thanks for the answer... Another user has wrote me this: the first problem is that your table doesn't show a primary key. Without it, .Update() won't work. Second, you, apparently, don't have an Update command associated with the DataAdapter. Third, if the Primary Key information is missing, you set the .MissingSchemaAction, but never called .FillSchema before you called .Fill In your opinion what other things have i miss?
That is correct, without strongly typed datasets you should add this line right before you call myAdapter.Fill.
myAdapter.FillSchema(ds, SchemaType.Source, "MyTable");Torin Blair
'In the immortal words of Socrates - "I drank what?".' -
I don't know the structure of your tables so I can't write any of the sql statements. Torin Blair
'In the immortal words of Socrates - "I drank what?".'