By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,460 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

c# .net INSERT into Access Database

P: 4
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
Apr 30 '08 #1
Share this Question
Share on Google+
7 Replies


Curtis Rutland
Expert 2.5K+
P: 3,256
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.
Apr 30 '08 #2

P: 4
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
Apr 30 '08 #3

Curtis Rutland
Expert 2.5K+
P: 3,256
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.
Apr 30 '08 #4

Plater
Expert 5K+
P: 7,872
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)
Apr 30 '08 #5

P: 1
Please change your table name to anything instead of "users" and "user". I thing this could solve your problem.
Jun 1 '10 #6

P: n/a
Syntax error in insert into statement using oledb connection from ms acess database
Sep 30 '10 #7

P: 4
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
Feb 14 '14 #8

Post your reply

Sign in to post your reply or Sign up for a free account.