error "cannot open the action query"

[sarabonn]

hallo

Iam trying to insert a row into a access database using visual c# and iam getting an error " cannot open the action query". here is my code

 
private void button1_Click(object sender, EventArgs e)
        {
     txt1 = textBox1.Text;
            txt2 = textBox2.Text;
            String connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Userinfo.accdb";
            OleDbConnection connection = new OleDbConnection(connectionString);
                       OleDbCommand command = new OleDbCommand();           
 
 
              command.CommandType = CommandType.TableDirect;
              command.CommandText = "INSERT INTO userinfo (Username, Pwd) VALUES (‘" + txt1 + "’ , ‘" + txt2 + "’)";
              command.Connection = connection;
              connection.Open();
              command.ExecuteNonQuery(); // getting in this line .
              connection.Close();
 
 

any idea why is this error ?

Thank you.
Dinesh.

[Curtis Rutland]

Please enclose your posted code in [CODE] [/CODE] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [CODE] [/CODE] tags in future.

MODERATOR

[Plater]

Your CommandType should be
CommandType.Text

With TableDirect, I believe you would just make CommandText ="MyTableName" and it would return the contents of the entire table

[sarabonn]

Your CommandType should be
CommandType.Text

With TableDirect, I believe you would just make CommandText ="MyTableName" and it would return the contents of the entire table

hallo plater,

If i use the CommanType.Text it gives me error "No value given for one or more required parameters." in the line Command.ExecuteNonQuery();.

Thank you.

Dinesh.

[MrMancunian]

Instead of

command.CommandText = "INSERT INTO userinfo (Username, Pwd) VALUES (‘" + txt1 + "’ , ‘" + txt2 + "’)";
 

use

command.InsertCommand = "INSERT INTO userinfo (Username, Pwd) VALUES (‘" + txt1 + "’ , ‘" + txt2 + "’)"; 
 

Steven

[sarabonn]

Instead of

command.CommandText = "INSERT INTO userinfo (Username, Pwd) VALUES (‘" + txt1 + "’ , ‘" + txt2 + "’)";
 

use

command.InsertCommand = "INSERT INTO userinfo (Username, Pwd) VALUES (‘" + txt1 + "’ , ‘" + txt2 + "’)"; 
 

Steven

hallo steven,

Command is an object oldbcommand and it doesn't have the command.insertcommand or do you mean oledbdataadapter.InsertCommand ?..

thank you,

dinesh.

[MrMancunian]

hallo steven,

Command is an object oldbcommand and it doesn't have the command.insertcommand or do you mean oledbdataadapter.InsertCommand ?..

thank you,

dinesh.

Yes, sorry, I meant the DataAdapter.InsertCommand

Steven

[sarabonn]

Yes, sorry, I meant the DataAdapter.InsertCommand

Steven

hallo steven,

I changed the code like this with the dataadapter.insertcommand but iam getting a error "No value given for one or more required parameters." in the executenonquery() line. Iam sure about the table details.

private void button1_Click(object sender, EventArgs e)
        {
         txt1 = textBox1.Text;
            txt2 = textBox2.Text;
            txt3 = textBox1.Text;
             txt4 = textBox2.Text;
            OleDbConnection cnJetDB = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Userinfo.accdb");
            cnJetDB.Open();
            OleDbDataAdapter oleDa = new OleDbDataAdapter();
            OleDbCommand cmdInsert = new OleDbCommand("INSERT INTO userinfo (Username, Pwd,Openid,ServerUrl)" + "VALUES (" + txt1 + "," + txt2 + "," + txt3 + "," + txt4 + ")", cnJetDB);
 
            oleDa.InsertCommand = cmdInsert;
 
            cmdInsert.ExecuteNonQuery();
            cnJetDB.Close();
} 
 

any idea steven ?..

Thank you.
Dinesh

[Plater]

I would say your problem is using the illegal quote character in your string. try using the regular ' single quote and see if that works for you.

[r035198x]

I would say your problem is using the illegal quote character in your string. try using the regular ' single quote and see if that works for you.

I don't see any quotes around the text values there at all legal or otherwise. My colleagues say that I'm getting old so my eyes may be the problem.

[sarabonn]

I would say your problem is using the illegal quote character in your string. try using the regular ' single quote and see if that works for you.

hallo plater,

i have changed the code and the line which u say to change to single quote as follows

            OleDbCommand cmdInsert = new OleDbCommand("INSERT INTO userinfo (Username, Pwd,Openid,ServerUrl)" + "VALUES (" + txt1 + "," + txt2 + "," + txt3 + "," + txt4 + ")", cnJetDB);
 

then iam getting error in the executenonquery line.

thank you.

Dinesh.

[r035198x]

When passing text to the database, you need to pass it surrounded by single quotes. Suppose your value is stored in a variable called text, you would pass in "'"+ text + "'". Have a look at other people's code and you will see all those quotes all over the place. Not very elegant I might add. Better use Parameters and let the drivers handle that for you.

[Plater]

Edit: ok, so yeah, the single quotes you used were the left-single-quote and right-single-quote. You need the non-directional-single quote

"INSERT INTO userinfo (Username, Pwd) VALUES ('" + txt1 + "' , '" + txt2 + "')";
 

[Curtis Rutland]

OK, I think the problem is that you are trying to insert into varchar fields without using single quotes around your values.

Try this:

string cmdText = String.Format("INSERT INTO userinfo (Username, Pwd,Openid,ServerUrl) VALUES ('{0}','{1}','{2}','{3}')",txt1,txt2,txt3,txt4);
OleDbCommand cmdInsert = new OleDbCommand(cmdText,cnJetDB);
 

I've used the String.Format method here. Notice how much cleaner and easier it is to understand than that mess of string concatenation you had before.

[sarabonn]

OK, I think the problem is that you are trying to insert into varchar fields without using single quotes around your values.

Try this:

string cmdText = String.Format("INSERT INTO userinfo (Username, Pwd,Openid,ServerUrl) VALUES ('{0}','{1}','{2}','{3}')",txt1,txt2,txt3,txt4);
OleDbCommand cmdInsert = new OleDbCommand(cmdText,cnJetDB);
 

I've used the String.Format method here. Notice how much cleaner and easier it is to understand than that mess of string concatenation you had before.

Thank you so much moderator. You are right the mess i have made to the string concatenation. thank you once again. now the problem is solved.. I have one more doubt?. I have this database "userinfo.accdb" in my c:\userinfo.accdb". I have added this to my project. so when i use the executable version of my project ( i mean the exe file the bin folder) in another computer should i also carry this userinfo.accdb. if so how can change the datasource of the database in the .exe version of the project.

Dinesh.