c# .net INSERT into Access Database

[evilash]

Hi,

am new to c# so any help here would be greatly appreciated. Am trying to insert into my access database called Users.mdb located in my app data folder. My current code is as follows:

OleDbConnection oleDbConnection = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + Server.MapPath("~/app_data/users.mdb"));
OleDbCommand oleDbCommand = new OleDbCommand();
oleDbCommand.Connection = oleDbConnection;
oleDbCommand.CommandText = "INSERT INTO users (Username, Password)VALUES ('" + TextBox1.Text + "', '" + TextBox2.Text + "')";
oleDbConnection.Open();
oleDbCommand.ExecuteNonQuery();
oleDbConnection.Close();

I have Using System.Data.OleDb at the top of my page class as well, but it returns the following 'Syntax error in INSERT INTO statement'. Am not sure what is wrong, as i am struggling to see anything wrong with the syntax.

Thanks in advance

[Curtis Rutland]

Hi,

am new to c# so any help here would be greatly appreciated. Am trying to insert into my access database called Users.mdb located in my app data folder. My current code is as follows:

OleDbConnection oleDbConnection = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + Server.MapPath("~/app_data/users.mdb"));
OleDbCommand oleDbCommand = new OleDbCommand();
oleDbCommand.Connection = oleDbConnection;
oleDbCommand.CommandText = "INSERT INTO users (Username, Password)VALUES ('" + TextBox1.Text + "', '" + TextBox2.Text + "')";
oleDbConnection.Open();
oleDbCommand.ExecuteNonQuery();
oleDbConnection.Close();

I have Using System.Data.OleDb at the top of my page class as well, but it returns the following 'Syntax error in INSERT INTO statement'. Am not sure what is wrong, as i am struggling to see anything wrong with the syntax.

Thanks in advance

Is the table name "users"? I see that the DB name is that, but are you sure that is the table name?
If that's not it, it could be something you are putting in your text boxes. Does it do it every time? Here's a good way to debug your SQL statements:
Comment out the open, execute, and close lines. If it is a console app, Console.Write(oleDbCommand.CommandText); If it is a windows app, you can use Console.Write, and you can see the result in the Output window in Visual Studio. If you aren't using VS, use a MessageBox, or replace the text in one of your TextBoxes with your query text.

This way, you can see the full SQL statement that you are passing to your DB engine. Copy your query text directly into an access query and see what happens. Post the text if you still can't figure it out.

[evilash]

Yeah users is the name of the Database and the table name. Sorry to be blunt, but everything you said after kinda flew right past my head as i am a bit of a newb. Thanks

[Curtis Rutland]

Yeah users is the name of the Database and the table name. Sorry to be blunt, but everything you said after kinda flew right past my head as i am a bit of a newb. Thanks

Sorry about that, we'll take it slow.

Is this a console app (command prompt), a windows app (forms and windows), or an ASP.NET (website)?

Depending on the answer to that question, you can debug your SQL statement in different ways. Because it seems as though you have a problem with the INSERT statement, not your program.

Console App: change your code to this:
//oleDbConnection.Open();
//oleDbCommand.ExecuteNonQuery();
//oleDbConnection.Close();
Console.WriteLine(oleDbCommand.CommandText);

(By the way, // is a line comment, in case you didn't know. The compiler will skip every thing after that to the end of the line.)

This will output your full query text to the screen, so you can see if there is anything wrong with the query that you are passing.

Windows App::
you could do the same thing, but you would have to find the "output window" in Visual Studio. Another option would be to add a label called lQueryText to the form, and then change your code to this:
//oleDbConnection.Open();
//oleDbCommand.ExecuteNonQuery();
//oleDbConnection.Close();
lQueryText.Text = oleDbCommand.CommandText;

This will write the command text to a label that you have added to your form.

ASP.NET app
Change your code to this:
//oleDbConnection.Open();
//oleDbCommand.ExecuteNonQuery();
//oleDbConnection.Close();
Response.Write(oleDbCommand.CommandText);

This will write the command text to the web page (at the very top).

The whole point of this is to see the completed query you are going to pass to Access. That way, you can see if anything is wrong with the query itself, and not the program.

[Plater]

If you have single quotes or other special characters in your textboxes, your code will fail.
You would need to do a quote replace function for those (escape a single quote with 2 single quotes)

[anayet]

Please change your table name to anything instead of "users" and "user". I thing this could solve your problem.

[Lembhe Neha]

Syntax error in insert into statement using oledb connection from ms acess database

[heemanshubhalla]

insertion in Access database using .net Visual Studio

This link has working example with source code available to download along with database

http://geeksprogrammings.blogspot.co...ing-vbnet.html