insert into ms access database

Question

I use the following code to insert a record from one database to another but it doesn't work. I tried the query in MS-ACCESS 2007 and it works fine but it doesn't work when called programmatically from my C# code?

string query_insert = "INSERT INTO Questionnaires_Table(BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees) "
+ "SELECT BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees "
+ "FROM Questionnaires_Table IN '" + dialog.FileName + "' Where Branch_ID = " + textBox1.Text ;

dbConnDest.Open();

OleDbDataAdapter dAdapter = new OleDbDataAdapter();
OleDbCommand cmd_insert = new OleDbCommand(query_insert, dbConnDest);

dAdapter.InsertCommand = cmd_insert;

textBox2.Text = query_insert.ToString();

dbConnDest.Close();

When I take the the content of query_insert in ms access, it works fine.

What exception do you get? Does the executing account have access to the external database? — D Stanley, Mar 25 '13 at 15:06
**warning** your code is vulnerable to sql injection attacks. — Daniel A. White, Mar 25 '13 at 15:07

score 3 · Answer 1 · answered Mar 25 '13 at 15:06

3

I think you need to use

cmd_insert.executeNonQuery()

answered Mar 25 '13 at 15:06

Crowlix

1,269
9
19

when i try this got an exception of syntax error in INSERT INTO – user2183831 Mar 25 '13 at 15:12
That means your sql syntax is wrong. In this situation, I'd put up some breakpoints to determine the value of query_insert and paste it into your ms access db to see if it works – Crowlix Mar 25 '13 at 15:14
I see, then try using textbox2.Text = cmd_insert.executeScalar() – Crowlix Mar 25 '13 at 15:18
And like HansUp said, you should remove the comma in the SELECT statement – Crowlix Mar 25 '13 at 15:20
Error 1 Cannot implicitly convert type 'object' to 'string'. An explicit conversion exists (are you missing a cast?) – user2183831 Mar 25 '13 at 15:23
my bad. textbox2.Text = cmd_insert.executeScalar().toString() – Crowlix Mar 25 '13 at 15:25
with executenonquery and executescalar i got an exception for insert into syntax – user2183831 Mar 25 '13 at 15:32

score 1 · Answer 2 · answered Mar 25 '13 at 15:17

1

Remove the comma after the last field name in the SELECT list.

"SELECT BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees"

answered Mar 25 '13 at 15:17

HansUp

95,961
11
77
135

score 0 · Answer 3 · answered Mar 25 '13 at 15:12

0

dAdapter.Update(); should do the trick

answered Mar 25 '13 at 15:12

you created an insert statement but you didn't execute it. An example can be read here: [link](http://stackoverflow.com/questions/5874887/using-oledbdataadapter-and-dataset-to-update-access-mdb) You can leave out the DataTable related code if you want. – Mar 25 '13 at 15:27

score 0 · Answer 4 · answered Mar 25 '13 at 18:25

0

This seems suspect:

 " Where Branch_ID = " + textBox1.Text ;

Does textBox1 contain a numeric ID? Does the ID that is entered exist in the source database?

I would 1) do a check that the ID exists and warn the user if it doesn't, and 2) change the query to use paramters instead of concatenating SQL.

What would happen if your company opened a branch with the ID of

"1; DROP TABLE Branches"

answered Mar 25 '13 at 18:25

D Stanley

149,601
11
178
240

i use parameters but still got the same problem – user2183831 Mar 25 '13 at 18:40

insert into ms access database

4 Answers4

Linked