Connecting to Access database

Question

I'm trying to connect to database file "crepeDB.accdb"

When I added it through data connection, and works fine when I drag any table to appear as data grid in any form but when I try to connect to the database to insert data it gives me this error:

An unhandled exception of type 'System.NotImplementedException' occurred in Additional information: The method or operation is not implemented.

The code I'm using is as follows:

System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
// TODO: Modify the connection string and include any
// additional required properties for your database.
conn.ConnectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data source=|DataDirectory|\\crepeDB.accdb;");

conn.Open();
string query = "insert into Sales (Sdate,SQuantity) values ('" + dateTimePicker1.Value + "','" + textBox9.Text + "')";
OleDbCommand cmd = new OleDbCommand(query, conn);
cmd.ExecuteNonQuery();

This is the last thing I need to do in my project, would really appreciate any help.

On what line? What is the stack trace displayed in the exception dialog? — Alex K., Feb 20 '17 at 11:41
(This should be rewritten to use a parameterized insert as you currently suffer a SQL Injection vulnerability. http://stackoverflow.com/questions/5893837/using-parameters-inserting-data-into-access-database) — Alex K., Feb 20 '17 at 11:42
yeah, lets add `'); drop database; select * from sys_tables where 'a' = ('a` in the textbox9 :D — Icepickle, Feb 20 '17 at 11:54
While the concept to using parameters is always correct, This kind of Sql Injection cannot work with ms-access 'thanks' to the missing batch update functionality — Steve, Feb 20 '17 at 12:09
here is the exception detailsSystem.NotImplementedException was unhandled Message: An unhandled exception of type 'System.NotImplementedException' occurred in CrepeCity.exe Additional information: The method or operation is not implemented. — Joey Arnanzo, Feb 20 '17 at 12:13
Sorry, but I don't know how to get the stacktrace of the exact line which the problem occurs, any help? — Joey Arnanzo, Feb 20 '17 at 12:23
Put a breakpoint (F9) on the creation of the OleDbConnection. When the breakpoint is hit proceed step by step using F10 and continue until the exception triggers — Steve, Feb 20 '17 at 12:30

score 2 · Accepted Answer · answered Feb 20 '17 at 12:37

2

Do not pass values for your fields concatenating them to form your command, instead use parameters.

int quantity;
if(!Int32.TryParse(textBox9.Text, out quantity))
     MessageBox.Show("Invalid number");
else
{
    using(OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data source=|DataDirectory|\\crepeDB.accdb;"))
    {    
        conn.Open();
        string query = @"insert into Sales (Sdate,SQuantity) 
                         values (@date, @qta)";
        OleDbCommand cmd = new OleDbCommand(query, conn);
        cmd.Parameters.Add("@date", OleDbType.Date).Value =  dateTimePicker1.Value;
        cmd.Parameters.Add("@qta", OleDbType.Integer).Value = quantity;
        cmd.ExecuteNonQuery();
    }
}

This is better because you don't ask someone else to convert your values from a string to the correct datatype. This automatic conversion (in particular with dates) is well know to cause problems when there is some kind of mismatch between the passed string and how the database engine interprets this string

N.B I am assuming the Sdate is a field of type DateTime and SQuantity is a field of type Integer in MS-Access. If not then you can change the OleDbType Int32.TryParse to the correct matching type

answered Feb 20 '17 at 12:37

Steve

213,761
22
232
286

Thanks Steve, I've used your code, but it gives me red underline on OleDbConnection and Add it gives the hint "type used in using statement must be implicitly convertible to System.IDisposable" and on Add it gives "object does not contain a definition for Add" – Joey Arnanzo Feb 20 '17 at 12:40
Uhm, and what error do you get when trying to start the program (I mean the compiler errors) – Steve Feb 20 '17 at 12:41
Severity Code Description Project File Line Suppression State Error CS1674 'OleDbConnection': type used in a using statement must be implicitly convertible to 'System.IDisposable' CrepeCity C:\Users\Lymph47u5\Documents\Visual Studio 2015\Projects\CrepeCity\CrepeCity\frmCashier.cs 1648 Active – Joey Arnanzo Feb 20 '17 at 12:43
Severity Code Description Project File Line Suppression State Error CS1061 'object' does not contain a definition for 'Add' and no extension method 'Add' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?) CrepeCity C:\Users\Lymph47u5\Documents\Visual Studio 2015\Projects\CrepeCity\CrepeCity\frmCashier.cs 1655 Active – Joey Arnanzo Feb 20 '17 at 12:44
That's weird. It seems that inside your code there is a class named OleDbConnection that hides the System.Data.OleDb one. Try to add, before the OleDbConnection, the full namespace spec as _System.Data.OleDb.OleDbConnection_ but that's is a problem to be solved. – Steve Feb 20 '17 at 12:46
Ok, that doesn't give any build error, but the Parameter.Add does give an error Severity Code Description Project File Line Suppression State Error CS1061 'object' does not contain a definition for 'Add' and no extension method 'Add' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?) CrepeCity C:\Users\Lymph47u5\Documents\Visual Studio 2015\Projects\CrepeCity\CrepeCity\frmCashier.cs 1655 Active – Joey Arnanzo Feb 20 '17 at 12:50
Still I believe there is something very wrong in your project. You shouldn't have this problems of mixing systm objects with your own objects. Did you add the _using System.Data.OleDb;_ at the beginning of the file? – Steve Feb 20 '17 at 12:54
Yes, I did put this using System.Data.OleDb; at the begining of the file – Joey Arnanzo Feb 20 '17 at 12:55
Ok, tested and works perfectly, but if you could explain to me what I did wrong so I can avoid it in the future, I would really appreciate it – Joey Arnanzo Feb 20 '17 at 13:03
I really cannot say why the System.Data.OleDb classes are hidden after you put the appropriat using. I think that somewhere in your project there are classes with the same name that got in the way. But to tell you exactly one need to see the whole project/solution – Steve Feb 20 '17 at 13:11

score 0 · Answer 2 · answered Feb 23 '17 at 18:19

It is basically like this . . .

con.Open();
SqlCommand cmd = new SqlCommand(@"insert into tbl_insert values(@name,@email,@add)", con);
cmd.Parameters.AddWithValue("@name", txtname.Text);
cmd.Parameters.AddWithValue("@email", txtemail.Text);
cmd.Parameters.AddWithValue("@add", txtadd.Text);
cmd.ExecuteNonQuery();
con.Close();

Connecting to Access database

2 Answers2