OleDbCommand.ExecuteNonQuery "Insert Into Select From"

Hello,

I am working with OleDb to perform a bulk insert from a temporary table to permanent database table, both of which belong to the same database. I am wanting to use "INSERT INTO <permanent_table> SELECT * FROM <temp_table>". The query I run in MS Access to perform this bulk load does process correctly, but not in code using OleDb.

After properly setting up my OleDbConnection object, opening it, and properly creating my OleDbCommand object, my question is this: when I call "ExecuteNonQuery()" no rows are returned and no data shows up in the permanent table. My connection opens perfectly and the OleDbCommand object is created successfully. Furthermore the query I am using in my command object(s), when run against a .mdb database works perfectly.

Any suggestions, this is really stumping me.

Thanks,

dotBomb

Answer this question

OleDbCommand.ExecuteNonQuery "Insert Into Select From"

MrMan
I think it works for sample.mdb under \bin\debug or \bin\release because you launch your application from there. If you copy your executable file together with sample.mdb to another directory and launch your application from there, it should also work.

If you don't want to copy .mdb file to the same directory of your executable file, what you can do is to specify the path of .mdb file. For example, c:\xx\yy\zz.mdb. Then put it in your connection string. Sample as following:

OleDbConnection oledbconnection3 = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\windows\\system32\\SAMPLE.MDB; User ID=admin; Password=");
oledbconnection3.Open();

It should also work corrrectly.

Please let me know if you have any questions.

Thanks

Bei
kingdech
Have you tried my simple code in your machine If not, could you please give it a try and let me know the result.

If my code works in your machine, then could you please provide more detail about the table you created, your connection string and the insertIntoQuery. Without knowing these, I am not able to figure out which your code doesn't work.

Thanks
Bei
Jo&#227;o Lopes
I don't think it is a "feature" of VS2005 since I also use it.

Could you please try my repro below and tell me the result First please copy sample.mdb to the directory I refer to which is c:\windows\system32.

OleDbConnection oledbconnection3 = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\windows\\system32\\SAMPLE.MDB; User ID=admin; Password=");
oledbconnection3.Open();
OleDbCommand oledbcommand1 = oledbconnection3.CreateCommand();
oledbcommand1.CommandText = "create table temp2(co1 int)";
Int32 int321 = oledbcommand1.ExecuteNonQuery(); // 0
oledbcommand1.CommandText = "insert into temp2 values(1)";
Int32 int322 = oledbcommand1.ExecuteNonQuery(); // 1
oledbcommand1.CommandText = "create table per2(co1 int)";
Int32 int323 = oledbcommand1.ExecuteNonQuery(); // 0
oledbcommand1.CommandText = "INSERT INTO per2 SELECT * FROM temp2 ";
Int32 int324 = oledbcommand1.ExecuteNonQuery(); // 1
oledbcommand1.CommandText = "select * from per2";
OleDbDataReader oledbdatareader1 = oledbcommand1.ExecuteReader(CommandBehavior.Default);
Boolean boolean1 = oledbdatareader1.Read(); // true
Object[] objectarray1 = new Object[1];
Int32 int325 = oledbdatareader1.GetValues(objectarray1); // 1

Thanks

Bei
Charles W
Source code as following:

OleDbConnection oledbconnection2 = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=SAMPLE.MDB; User ID=admin; Password=");
oledbconnection2.Open();
OleDbCommand oledbcommand2 = oledbconnection2.CreateCommand();
oledbcommand2.CommandText = "create table temp1(co1 int)";
Int32 int326 = oledbcommand2.ExecuteNonQuery(); // 0
oledbcommand2.CommandText = "insert into temp1 values(1)";
Int32 int327 = oledbcommand2.ExecuteNonQuery(); // 1
oledbcommand2.CommandText = "create table per1(co1 int)";
Int32 int328 = oledbcommand2.ExecuteNonQuery(); // 0
oledbcommand2.CommandText = "INSERT INTO per1 SELECT * FROM temp1 ";
Int32 int329 = oledbcommand2.ExecuteNonQuery(); // 1
oledbcommand2.CommandText = "select * from per1";
OleDbDataReader oledbdatareader2 = oledbcommand2.ExecuteReader(CommandBehavior.Default);
Boolean boolean2 = oledbdatareader2.Read(); // true
Object[] objectarray2 = new Object[1];
Int32 int3210 = oledbdatareader2.GetValues(objectarray2); // 1

Thanks

Bei
t0x1

Hi,

First the NonQuery method does not returns records. It will return the number of records modified by the query.

If you want to get the records use ExecuteReader or Fill a dataset.

Regards,
MrDewMan

guy,

I'm not expecting the ExecuteNonQuery() method to return to me anything but an integer, so I am confused by your response. Please see my source excerpt.

What I'm really trying to figure out is why my VS2005 project is attempting to find the access database in the .\bin\Debug (or .\bin\Release) folder, depending on what configuration I'm building, when it exists elsewhere on the development machine.

Thank you,

dotBomb
Venkata Subba Rao

Bei,

I know. I created a sample.mdb to match your example. Yes, you are correct. Once I put the sample.mdb file in the ./bin/Debug or ./bin/Release folder it works with no issue. Is this a new feature of VS2005 My deployed executable won't look for the database in the application path when it is deployed, will it

Many thanks,

Dan
BertBeck

Bei,

Thank you again for your reply. My connection string *does* reference the *absolute path and filename* of the access database, and it has always done so since the beginning of the project and this error still occurs unless I place the MS Access database into ./bin/Debug or ./bin/Release folder (depending on configuration I'm running). This is why this error is so odd. I believe it may be a new "feature" of VS2005, do you know

Also, I'm concerned what will happen during deployment and subsequent execution on customer machines.

Many thanks,

Dan
cehottle

As a sanity check, try dumping out connection string prior to execution, like so:

MessageBox.Show(cmdPopObj.Connection.ConnectionString);

int numRowsAffected = cmdPopObj.ExecuteNonQuery();

I seem to recall that there is some feature where VS.NET will use connection string from myapp.config file to try to be deployment friendly, so it will fix up the connection string.
Mr Big

Bei

I am connecting to a Microsoft Access Database (.mdb). The connection string I use in creating the connection object is the same as I would use for any select or update query. Perhaps you could provide a sample of your code for comparison purposes The query I am using for the CommandText property of the command object is just the same query I am using in the Access Query Window. It is this:

INSERT INTO HISTDATA_TMP_3
SELECT *
FROM HISTDATA
WHERE HDVarname='1_Cr_Wet' And HDDateTime>#6/9/2005# And HDDateTime<=#7/20/2005#;

Many thanks,

dotBomb
TheMilkMan
What backend do you connect to I just tried using oledb to run against SQL and .mdb files. All work correctly.

Thanks
Bei
Bhushan Akole

Hi Bei,

Here is my code:

System.Data.OleDb.OleDbConnection popTableConn = new System.Data.OleDb.OleDbConnection(Form1.cStringHD);

popTableConn.Open();

System.Data.OleDb.OleDbCommand cmdPopObj = popTableConn.CreateCommand();

cmdPopObj.CommandText = insertIntoQuery;

try

{

int numRowsAffected = cmdPopObj.ExecuteNonQuery();

System.Windows.Forms.MessageBox.Show("Number of rows added: " + numRowsAffected);

}

catch (System.Exception dbExceptionOnPop)

{

returnValue = false;

System.Windows.Forms.MessageBox.Show(dbExceptionOnPop.Message, "ReportView Database Table Message");

}

popTableConn.Close();

The result I get when the 'ExecuteNonQuery()' method is called is the following:

Could not find '<path_of_project's_debug_folder><name_of_database>'.

This is very odd considering the path of the database is given explicitly in the connection string and the connection string works for all of my other "INSERT" and "UPDATE" queries. The database is not located in the VS2005 project's debug folder. Why the funny error, do you think

Many thanks,

dotBomb
GregDude
Actually my sample.mdb is not the sample file from VS2005. I created one myself and named it sample.mdb. Once you put the .mdb file in the path, you can use it. Otherwise the application just can not find it, right

Thanks

Bei
Blainy

Bei,

Your example works only when SAMPLE.mdb is in the .\bin\Debug or .\bin\Release folder of my sample project depending on whatever configuration I'm building. Is this behavior from VS2005 a bug Surely the project cannot expect the data source to be present in the application directory after deployment

Many thanks for any direction,

Dan
OleDbCommand.ExecuteNonQuery "Insert Into Select From"

Answer this question