getting a dataset of affected records,using an ExecuteNONQuery OleDbCommand
-
Hi to everyone! I need to get the autonumeric field assigned to a new record, because I want to use it on a different table.
ExecuteNonQueryretrieves the number of affected rows. But how can I get a dataset result of the affected rows, as the one using anExecuteQuerycommand? Thanks for everyones time! :doh: daniel sovino -
Hi to everyone! I need to get the autonumeric field assigned to a new record, because I want to use it on a different table.
ExecuteNonQueryretrieves the number of affected rows. But how can I get a dataset result of the affected rows, as the one using anExecuteQuerycommand? Thanks for everyones time! :doh: daniel sovino -
You can ask he database for the last created key. Depending on what database you are using, you use different functions: MySQL:
select last_insert_id()MS SQL:select scope_identity()Access:select @@identity--- Year happy = new Year(2007);
-
You can ask he database for the last created key. Depending on what database you are using, you use different functions: MySQL:
select last_insert_id()MS SQL:select scope_identity()Access:select @@identity--- Year happy = new Year(2007);
Ok, but how do I get this done? I had lots of troubles working with access database and the dataset wizard to store querys (http://forums.microsoft.com/MSDN/showpost.aspx?postid=1121807&siteid=1), so I opted to run my queries with code. I used one method with the Insert command, and if the result set is higher than 0, I call another method with a Scalar Command, to search for the ID. Then, with the retrieved ID, i call the third method to Insert the record ID on the second table.... :zzz: The code described below worked well, everyonce in a while (no clue yet why sometimes i'm able to get the ID and sometimes I get 0, probably something going on with the query). So that's why I want a safer way to get this done. I searched a little bit for "select @@identity" (http://support.microsoft.com/kb/816112), and I found out that it behaves as a Scalar command as well. So in theory, i should just change my query string to get it done. It doesnt work at all. Does the "select @@identity" statement work only when you handle it on a OleDbDataAdapter.RowUpdated Event? Thanks for the help!
string file = "......"; private void InsertAudio() { // creation of queries, source, connections, etc.... OleDbCommand cmd = new OleDbCommand(sql, conexion); int resultSet; try { conexion.Open(); resultSet = cmd.ExecuteNonQuery(); if (resultSet > 0) SearchIDofCreatedRow(); } catch (OleDbException ode) { MessageBox.Show(ode.ToString(), "Error de Conexion con base de datos"); } finally {conexion.Close();} } private void SearchIDofCreatedRow() { // creation of queries, source, connections, etc.... OleDbCommand cmd = new OleDbCommand("SELECT ID FROM audios WHERE file='" + file + "'", conexion); try { conexion.Open(); int ID = Convert.ToInt32(cmd.ExecuteScalar()); AddCategoryList(ID); } //catch/finally..... } private void AddCategoryList(int ID) { // //Insert into the second table with the retrieved data.... // }daniel sovino
-
Question. If the affected Rows are more than 1 do the above statements return one ID or more?
-
Ok, but how do I get this done? I had lots of troubles working with access database and the dataset wizard to store querys (http://forums.microsoft.com/MSDN/showpost.aspx?postid=1121807&siteid=1), so I opted to run my queries with code. I used one method with the Insert command, and if the result set is higher than 0, I call another method with a Scalar Command, to search for the ID. Then, with the retrieved ID, i call the third method to Insert the record ID on the second table.... :zzz: The code described below worked well, everyonce in a while (no clue yet why sometimes i'm able to get the ID and sometimes I get 0, probably something going on with the query). So that's why I want a safer way to get this done. I searched a little bit for "select @@identity" (http://support.microsoft.com/kb/816112), and I found out that it behaves as a Scalar command as well. So in theory, i should just change my query string to get it done. It doesnt work at all. Does the "select @@identity" statement work only when you handle it on a OleDbDataAdapter.RowUpdated Event? Thanks for the help!
string file = "......"; private void InsertAudio() { // creation of queries, source, connections, etc.... OleDbCommand cmd = new OleDbCommand(sql, conexion); int resultSet; try { conexion.Open(); resultSet = cmd.ExecuteNonQuery(); if (resultSet > 0) SearchIDofCreatedRow(); } catch (OleDbException ode) { MessageBox.Show(ode.ToString(), "Error de Conexion con base de datos"); } finally {conexion.Close();} } private void SearchIDofCreatedRow() { // creation of queries, source, connections, etc.... OleDbCommand cmd = new OleDbCommand("SELECT ID FROM audios WHERE file='" + file + "'", conexion); try { conexion.Open(); int ID = Convert.ToInt32(cmd.ExecuteScalar()); AddCategoryList(ID); } //catch/finally..... } private void AddCategoryList(int ID) { // //Insert into the second table with the retrieved data.... // }daniel sovino
The @@identity variable is local to the database session, and if you create another connection you get a new database session. You have to get the id using the same database connection that you used to insert the record. Why do you create a new connection in SearchIDofCreatedRow? When you call it you already have an open connection, pass that along in the call.
--- Year happy = new Year(2007);
-
The original question said about rows. The only way I can think except from triggers is to have a column something like just updated. Then query for those and just before finishing set the columns to false.
Sarafian wrote:
The original question said about rows.
Well, you don't always get what you ask for. :)
Sarafian wrote:
The only way I can think except from triggers is to have a column something like just updated. Then query for those and just before finishing set the columns to false.
An alternative would be to first get the id:s of the records to update, then use them to update the records.
--- Year happy = new Year(2007);