Error - Insert data into Access database in c#

Oyetoke Toby

Pls, am having an error code when inserting data into Access database. It keeps saying there's sytank error in my INSERT INTO statement. Can any one help me to solve this.

Here is the code

try {
OleDbConnection connection = new OleDbConnection(@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\Users\DELL\Documents\EmployeesData.accdb;
Persist Security Info = false;");
connection.Open();
OleDbCommand cmd = new OleDbCommand("insert into EmployeeInfo (UserName, Password)values('" + UserText.Text + "', '" + PassText.Text + "')", connection);
cmd.ExecuteNonQuery();
MessageBox.Show("Inserted");
}
catch (Exception ex)
{
MessageBox.Show("Failed" + ex.ToString());
}

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bikesh Srivastava10y ago
Accepted answer
Dear,
Before implementing c# code to insert value in ms-access ,Check your access schema ,
for now:-
You tried to add a new record but did not enter a value in the field that contains the primary key.
You tried to add a Null value to a primary key field.
You executed a query that tried to put a Null value in a primary key field.
you've to pass value for primary key column .
ketan borsdiya10y ago
Check your table schema identity column is auto increment?
and check all fields which are allow null?
Vignesh Mani10y ago
Hi friend Please check you table column name. Check spelling in your column Name.
https://msdn.microsoft.com/en-us/library/ms971485.aspx
http://www.c-sharpcorner.com/UploadFile/3bff37/insert-data-into-access-database-and-retrieve-them-into-drop/
Oyetoke Toby10y ago
Bikesh Srivastava
How am i suppose to do that
ketan borsdiya10y ago
Hi,
Replace your code with below sentence,
OleDbCommand cmd = new OleDbCommand("insert into EmployeeInfo (UserName, [Password]) values ('" + UserText.Text + "', '" + PassText.Text + "')", connection);
Oyetoke Toby10y ago
not working and started giving me another error code saying "Index or primary key cannot contain a Null value". Any clue pls
Bikesh Srivastava10y ago
Hi Toby,
Problem is your some column is reserved word. that's why you are getting error.
Please check use like this code:-
Suppose your Id_code column is reserved word then use like this [Id_code ];
INSERT into tbl_name (name,[Id_code]) ;
It may be resolve your problem,if yes tick answered.
Nitin Sontakke10y ago
Do one simple thing.
Go to MS-Access and in new query, type the command and test it there first.
Also, store the entire query to a string first, for example,
String query = "insert into ....";
In debug mode, take the contents of query variable and paste them in MS-Access and see if it works.
What i am trying to say is, forget C#, let it first work independently.
Manas Mohapatra10y ago
Small change need in code to add space in query:
OleDbCommand cmd=new OleDbCommand("insert into EmployeeInfo(UserName,Password)
values('"+UserText.Text+"','"+PassText.Text+"')",connection);
Note: Please try to use parameterised query when you are using inline query. It helps to avoid SQL injection hack.
using(OleDbCommandcmd=conn.CreateCommand())
{
   //createcommandwithplaceholders
   cmd.CommandText=
   "INSERT INTO EmployeeInfo "+
   "([UserName],[Password]) "+
   "VALUES(@username,@Password)";
   //add named parameters
   cmd.Parameters.AddRange(newOleDbParameter[]
   {
      newOleDbParameter("@username",UserText.Text),
      newOleDbParameter("@Password",PassText.Text)
   });
   //execute
   cmd.ExecuteNonQuery();
}
Oyetoke Toby10y ago
it doesn't solve it. It keeps giving me the same error
Vineet Kumar10y ago
Needs a space before 'values'. The correct syntax will be as below :
OleDbCommand cmd = new OleDbCommand("insert into EmployeeInfo (UserName, Password) values('" + UserText.Text + "', '" + PassText.Text + "')", connection);