inserting data into Access using C#??
-
Hi, I am trying to learn ADO.net using C# and I keep running into problems trying to insert data into my Access Database: data.mdb. here is my code:
// Database Variables private string connectionStr = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source=""C:\data.mdb"";Jet OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1"; private string selectStr = "Select id, date, day, time, comments, timeout, status, process from data"; private System.Data.DataSet myDataSet; private System.Data.OleDb.OleDbConnection myConnection; private System.Data.OleDb.OleDbDataAdapter myDataAdapter; private System.Data.OleDb.OleDbCommandBuilder myCmdBuilder; private System.Data.OleDb.OleDbCommand mySelectCmd; public Schedule() { this.InitilizeDb(); this.PopulateDataSet(); } private OleDbConnection ConnectDb() { return new OleDbConnection(connectionStr); } public void InitilizeDb() { myDataAdapter = new OleDbDataAdapter(); myCmdBuilder = new OleDbCommandBuilder(myDataAdapter); myDataSet = new DataSet(); } private void PopulateDataSet() { // get the connection object myConnection = (OleDbConnection) this.ConnectDb(); // Initilize the Select Command mySelectCmd = new OleDbCommand(selectStr, myConnection); // Define that the Select Command is an SQL statement mySelectCmd.CommandType = CommandType.Text; try { myConnection.Open(); myDataAdapter.SelectCommand = mySelectCmd; // Populate the DataSet from the "data" table myDataAdapter.Fill(myDataSet,"data"); } finally { myConnection.Close(); } } public string InsertSchedule(string date, string day, string time, string process, string comments, string timeout, string status) { // get the connection object myConnection = (OleDbConnection) this.ConnectDb(); try { myConnection.Open(); // create a new row to insert the data into DataRow newRow = myDataSet.Tables["data"].NewRow(); // initilize the new row newRow["id"] = 3; newRow["date"] = date;Because you haven't specified a INSERT
SqlCommand. TheSqlDataAdapterisn't magical - you need to provide SELECT, INSERT, UPDATE, and DELETESqlCommands for full functionality. Specifying just a SELECTSqlCommanddoesn't generate the others. If you're unsure about how to do this, read about theSqlCommandandSqlDataAdapterclasses in the SDK documentation, or use drop aSqlDataAdapteronto your form or control in VS.NET and run through the wizard. Be sure to look at what source it generates though to fully understand what is going on.-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
-
Because you haven't specified a INSERT
SqlCommand. TheSqlDataAdapterisn't magical - you need to provide SELECT, INSERT, UPDATE, and DELETESqlCommands for full functionality. Specifying just a SELECTSqlCommanddoesn't generate the others. If you're unsure about how to do this, read about theSqlCommandandSqlDataAdapterclasses in the SDK documentation, or use drop aSqlDataAdapteronto your form or control in VS.NET and run through the wizard. Be sure to look at what source it generates though to fully understand what is going on.-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
Oic, I thought the commandbuilder class is suppose to auto generate the insert, update and delete commands? so even if i have set the commandbuilder to my data adapter, I must still initilize my insert, update and delete commands? I will also take a look at the SDK documentation.. thanks.
-
Oic, I thought the commandbuilder class is suppose to auto generate the insert, update and delete commands? so even if i have set the commandbuilder to my data adapter, I must still initilize my insert, update and delete commands? I will also take a look at the SDK documentation.. thanks.
I missed that line, sorry. The
SqlCommandBuildermay have trouble with your SELECT statement. Another possibility is with the table mappings of theSqlDataAdapter. You're setting your table name as "data". Does that actually map to the name of the table from which you're selecting records? These should match and you might have to set up a table mapping using theSqlDataAdapter.TableMappingsproperty. This fairly straight-forward.-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
-
I missed that line, sorry. The
SqlCommandBuildermay have trouble with your SELECT statement. Another possibility is with the table mappings of theSqlDataAdapter. You're setting your table name as "data". Does that actually map to the name of the table from which you're selecting records? These should match and you might have to set up a table mapping using theSqlDataAdapter.TableMappingsproperty. This fairly straight-forward.-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
the dataset table name is "data" which is the same as the one from the source table name. i got the idea from MSDN that table mapping is use when your data source and dataset names are different? So if I have used the same name do I still need tablemapping? thanks
-
the dataset table name is "data" which is the same as the one from the source table name. i got the idea from MSDN that table mapping is use when your data source and dataset names are different? So if I have used the same name do I still need tablemapping? thanks
Yes, unless you use a strongly-typed
DataSet. In most cases, the SELECTSqlCommand(with one or multiple result sets) loads tables by default named "Table", "Table1", "Table2", ..., "TableN". YourTableMappingsshould map these table names to your actual tables in your database.-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
-
Yes, unless you use a strongly-typed
DataSet. In most cases, the SELECTSqlCommand(with one or multiple result sets) loads tables by default named "Table", "Table1", "Table2", ..., "TableN". YourTableMappingsshould map these table names to your actual tables in your database.-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
-
humm.. still wondering y i got the exception thrown when i try to perform an update to the database. just wondering if you are able to point me or share with me some sample that has insert, delete and update without the use of stored procedure?
I did actually. Drag a
SqlDataAdapterto your form or control and go through the wizard, specifying that new commands should be created instead of using new or existing stored procs. Take a look at what's generated in your source file. You can also read the SDK documentation forSqlDataAdapter, which does have examples in various class and method documentation.-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
-
humm.. still wondering y i got the exception thrown when i try to perform an update to the database. just wondering if you are able to point me or share with me some sample that has insert, delete and update without the use of stored procedure?
If you do have to browse to find the assemblies and all you find are xml files of the managed assemblies then go here for the latest DirectX 9 managed download: http://msdn.microsoft.com/library/default.asp?url=/downloads/list/directx.asp[^] Just follow the instructs and viola you should have the assemblies. You'll know right away if you've got them installed correctly because when you fire up VS.Net you'll see the green directx logo along with your other languages on the splash screen. Have fun! Best, Jerry
The only way of discovering the limits of the possible is to venture a little past them into the impossible.--Arthur C. Clark
-
If you do have to browse to find the assemblies and all you find are xml files of the managed assemblies then go here for the latest DirectX 9 managed download: http://msdn.microsoft.com/library/default.asp?url=/downloads/list/directx.asp[^] Just follow the instructs and viola you should have the assemblies. You'll know right away if you've got them installed correctly because when you fire up VS.Net you'll see the green directx logo along with your other languages on the splash screen. Have fun! Best, Jerry
The only way of discovering the limits of the possible is to venture a little past them into the impossible.--Arthur C. Clark
Wrong thread :)
-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
-
Wrong thread :)
-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
Oh damn! Sorry!
The only way of discovering the limits of the possible is to venture a little past them into the impossible.--Arthur C. Clark