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

error "cannot open the action query"

P: 69
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. private void button1_Click(object sender, EventArgs e)
  3.         {
  4.      txt1 = textBox1.Text;
  5.             txt2 = textBox2.Text;
  6.             String connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Userinfo.accdb";
  7.             OleDbConnection connection = new OleDbConnection(connectionString);
  8.                        OleDbCommand command = new OleDbCommand();           
  9.  
  10.  
  11.               command.CommandType = CommandType.TableDirect;
  12.               command.CommandText = "INSERT INTO userinfo (Username, Pwd) VALUES (" + txt1 + " , " + txt2 + ")";
  13.               command.Connection = connection;
  14.               connection.Open();
  15.               command.ExecuteNonQuery(); // getting in this line .
  16.               connection.Close();
  17.  
  18.  
any idea why is this error ?

Thank you.
Dinesh.
Nov 18 '08 #1
Share this Question
Share on Google+
14 Replies


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

Plater
Expert 5K+
P: 7,872
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
Nov 18 '08 #3

P: 69
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.
Nov 19 '08 #4

MrMancunian
Expert 100+
P: 569
Instead of

Expand|Select|Wrap|Line Numbers
  1. command.CommandText = "INSERT INTO userinfo (Username, Pwd) VALUES (" + txt1 + " , " + txt2 + ")";
  2.  
use

Expand|Select|Wrap|Line Numbers
  1. command.InsertCommand = "INSERT INTO userinfo (Username, Pwd) VALUES (" + txt1 + " , " + txt2 + ")"; 
  2.  
Steven
Nov 19 '08 #5

P: 69
Instead of

Expand|Select|Wrap|Line Numbers
  1. command.CommandText = "INSERT INTO userinfo (Username, Pwd) VALUES (" + txt1 + " , " + txt2 + ")";
  2.  
use

Expand|Select|Wrap|Line Numbers
  1. command.InsertCommand = "INSERT INTO userinfo (Username, Pwd) VALUES (" + txt1 + " , " + txt2 + ")"; 
  2.  
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.
Nov 19 '08 #6

MrMancunian
Expert 100+
P: 569
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
Nov 19 '08 #7

P: 69
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.

Expand|Select|Wrap|Line Numbers
  1. private void button1_Click(object sender, EventArgs e)
  2.         {
  3.          txt1 = textBox1.Text;
  4.             txt2 = textBox2.Text;
  5.             txt3 = textBox1.Text;
  6.              txt4 = textBox2.Text;
  7.             OleDbConnection cnJetDB = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Userinfo.accdb");
  8.             cnJetDB.Open();
  9.             OleDbDataAdapter oleDa = new OleDbDataAdapter();
  10.             OleDbCommand cmdInsert = new OleDbCommand("INSERT INTO userinfo (Username, Pwd,Openid,ServerUrl)" + "VALUES (" + txt1 + "," + txt2 + "," + txt3 + "," + txt4 + ")", cnJetDB);
  11.  
  12.             oleDa.InsertCommand = cmdInsert;
  13.  
  14.             cmdInsert.ExecuteNonQuery();
  15.             cnJetDB.Close();
  16.  
any idea steven ?..

Thank you.
Dinesh
Nov 19 '08 #8

Plater
Expert 5K+
P: 7,872
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.
Nov 19 '08 #9

10K+
P: 13,264
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.
Nov 19 '08 #10

P: 69
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
Expand|Select|Wrap|Line Numbers
  1.             OleDbCommand cmdInsert = new OleDbCommand("INSERT INTO userinfo (Username, Pwd,Openid,ServerUrl)" + "VALUES (" + txt1 + "," + txt2 + "," + txt3 + "," + txt4 + ")", cnJetDB);
  2.  
then iam getting error in the executenonquery line.

thank you.

Dinesh.
Nov 19 '08 #11

10K+
P: 13,264
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.
Nov 19 '08 #12

Plater
Expert 5K+
P: 7,872
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
Expand|Select|Wrap|Line Numbers
  1. "INSERT INTO userinfo (Username, Pwd) VALUES ('" + txt1 + "' , '" + txt2 + "')";
  2.  
Nov 19 '08 #13

Curtis Rutland
Expert 2.5K+
P: 3,256
OK, I think the problem is that you are trying to insert into varchar fields without using single quotes around your values.

Try this:
Expand|Select|Wrap|Line Numbers
  1. string cmdText = String.Format("INSERT INTO userinfo (Username, Pwd,Openid,ServerUrl) VALUES ('{0}','{1}','{2}','{3}')",txt1,txt2,txt3,txt4);
  2. OleDbCommand cmdInsert = new OleDbCommand(cmdText,cnJetDB);
  3.  
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.
Nov 19 '08 #14

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

Try this:
Expand|Select|Wrap|Line Numbers
  1. string cmdText = String.Format("INSERT INTO userinfo (Username, Pwd,Openid,ServerUrl) VALUES ('{0}','{1}','{2}','{3}')",txt1,txt2,txt3,txt4);
  2. OleDbCommand cmdInsert = new OleDbCommand(cmdText,cnJetDB);
  3.  
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.
Nov 19 '08 #15

Post your reply

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