problem retrieving info from one table to insert it into another
-
good evening gentlemen and thank you to you who brought me help but I am continuing my project and I have a problem retrieving info from one table to insert it into another.
I have three tables which are related
1.product (article_ID, design, price_U, Qty)
2.order (com_number, com_date, com_amount)
3.detail_command (num_det, num_com, ID_article, qute_det)
I can't fill the detail_commende table. It must be said that I am lost. can you put me on the track please
I may have made a serious mistake in my codes.private void BtnValider_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Voulez vous valider la facture?", "VALIDATION DE LA FACTURE", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
try
{setConnection(); sql\_con.Open(); using (OleDbCommand cmd1 = sql\_con.CreateCommand()) { cmd1.CommandText = "INSERT INTO Commandes (montant\_com) VALUES (@montant\_com)"; cmd1.Parameters.AddWithValue("@montant\_com", TxtTotalCmd.Text); //sql\_cmd = new OleDbCommand(CommandText, sql\_con); cmd1.ExecuteNonQuery(); //ADODB.Recordset dbrst = new ADODB.Recordset(); } long Qte\_prod, numCmd; string ref\_prod; using (OleDbCommand cmd2 = sql\_con.CreateCommand()) { //setConnection(); cmd2.CommandText = "SELECT MAX(num\_com) AS dernier\_num FROM Commandes"; // cmd2.Connection.Open(); // sql\_con.Open(); object result = cmd2.ExecuteScalar(); if (result is null || Convert.IsDBNull(result)) { numCmd = 0; } else { numCmd = Convert.ToInt64(result); TxtNunCmd.Text = numCmd.ToString(); //LoadDB(); //object result1 = cmd3.ExecuteScalar(); for (int i = 0; i < dataGridView1.First off, you cannot assume that the last INSERT you did is the last INSERT done by the system - if you have multiple users then their INSERTS can happen at any time. So instead of a separate SELECT to return the maximum value issue a combination command which does the INSERT and returns the value of the IDENTITY field:
INSERT INTO ...; SELECT SCOPE_IDENTITY();
And it will return the IDENTITY vlaue associated with your INSERT as a scalar value. This is assuming that the
commandestable you speak of is actually the detail_command you describe at the top of your question - but if it isn't, then that would explain why you are having trouble with getting it to work ... Oh, and BTW: I think we've mentioned that you shouldn't recycle SqlConnections before - create them in ausingblock and they will be Disposed correctly when you are finished with them. If you must faff with them like you are, then don't close the connection in the main code and thecatchblock - add afinallyblock to yourtryand close it there."I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
First off, you cannot assume that the last INSERT you did is the last INSERT done by the system - if you have multiple users then their INSERTS can happen at any time. So instead of a separate SELECT to return the maximum value issue a combination command which does the INSERT and returns the value of the IDENTITY field:
INSERT INTO ...; SELECT SCOPE_IDENTITY();
And it will return the IDENTITY vlaue associated with your INSERT as a scalar value. This is assuming that the
commandestable you speak of is actually the detail_command you describe at the top of your question - but if it isn't, then that would explain why you are having trouble with getting it to work ... Oh, and BTW: I think we've mentioned that you shouldn't recycle SqlConnections before - create them in ausingblock and they will be Disposed correctly when you are finished with them. If you must faff with them like you are, then don't close the connection in the main code and thecatchblock - add afinallyblock to yourtryand close it there."I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
thank you for your answer but I wanted to point out that (num_com) is auto-incremente. I would like to know if that doesn't count?
Yes, that's the whole point. That is what an IDENTITY field in SQL Server is. And when you use autoincremented fields you can't rely on the MAX value to be the one you inserted: you do your INSERT, then two dozen other users add their own before you issue your SELECT. YOur MAX then fetches the ID for a totally different row, and you start to get intermittent problems that are fiendishly difficult to duplicate, track down, fix, and prove. Using SCOPE_IDENTITY returns the last autoincremented value used by that connection, and will never return values from different users.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Yes, that's the whole point. That is what an IDENTITY field in SQL Server is. And when you use autoincremented fields you can't rely on the MAX value to be the one you inserted: you do your INSERT, then two dozen other users add their own before you issue your SELECT. YOur MAX then fetches the ID for a totally different row, and you start to get intermittent problems that are fiendishly difficult to duplicate, track down, fix, and prove. Using SCOPE_IDENTITY returns the last autoincremented value used by that connection, and will never return values from different users.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
OK well sir and I just found her in my research, are I adapting well to my code?
using (OleDbCommand cmd1 = sql_con.CreateCommand ())
{
cmd1.CommandText = "INSERT INTO Orders (amount_com) VALUES (@montant_com)";
cmd1.CommandText = "SELECT @@ IDENTITY";
int id = Convert.ToInt32 (cmd1.ExecuteScalar ());
cmd1.Parameters.AddWithValue ("@ amount_com", TxtTotalCmd.Text);
cmd1.ExecuteNonQuery ();
} -
OK well sir and I just found her in my research, are I adapting well to my code?
using (OleDbCommand cmd1 = sql_con.CreateCommand ())
{
cmd1.CommandText = "INSERT INTO Orders (amount_com) VALUES (@montant_com)";
cmd1.CommandText = "SELECT @@ IDENTITY";
int id = Convert.ToInt32 (cmd1.ExecuteScalar ());
cmd1.Parameters.AddWithValue ("@ amount_com", TxtTotalCmd.Text);
cmd1.ExecuteNonQuery ();
}Stop guessing. Start thinking. Look at your code and tell me what is wrong with it. Where have we seen that problem before?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Stop guessing. Start thinking. Look at your code and tell me what is wrong with it. Where have we seen that problem before?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
Ok my apologies, this is the error I get
System.Data.Oledb.Ole Exception (0x80040E14): Character found after the end of the Sql instructionusing (OleDbCommand cmd1 = sql_con.CreateCommand())
{cmd1.CommandText = "INSERT INTO Commandes (montant\_com) VALUES (@montant\_com);" + "SELECT SCOPE\_IDENTITY()"; int id; cmd1.Parameters.AddWithValue("@montant\_com", TxtTotalCmd.Text); cmd1.ExecuteNonQuery(); id = (int)cmd1.ExecuteScalar(); TxtNunCmd.Text = id.ToString(); } -
Ok my apologies, this is the error I get
System.Data.Oledb.Ole Exception (0x80040E14): Character found after the end of the Sql instructionusing (OleDbCommand cmd1 = sql_con.CreateCommand())
{cmd1.CommandText = "INSERT INTO Commandes (montant\_com) VALUES (@montant\_com);" + "SELECT SCOPE\_IDENTITY()"; int id; cmd1.Parameters.AddWithValue("@montant\_com", TxtTotalCmd.Text); cmd1.ExecuteNonQuery(); id = (int)cmd1.ExecuteScalar(); TxtNunCmd.Text = id.ToString(); }That's better!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Ok my apologies, this is the error I get
System.Data.Oledb.Ole Exception (0x80040E14): Character found after the end of the Sql instructionusing (OleDbCommand cmd1 = sql_con.CreateCommand())
{cmd1.CommandText = "INSERT INTO Commandes (montant\_com) VALUES (@montant\_com);" + "SELECT SCOPE\_IDENTITY()"; int id; cmd1.Parameters.AddWithValue("@montant\_com", TxtTotalCmd.Text); cmd1.ExecuteNonQuery(); id = (int)cmd1.ExecuteScalar(); TxtNunCmd.Text = id.ToString(); }OK, the edited version makes a little more sense. You need to split your command into two: Do the INSERT, then build a new Command to do the SELECT (some DB engines don't like command chaining). As long as you use the same connection and you don't close it in between the INSERT and SELECT, you'll be fine.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
OK, the edited version makes a little more sense. You need to split your command into two: Do the INSERT, then build a new Command to do the SELECT (some DB engines don't like command chaining). As long as you use the same connection and you don't close it in between the INSERT and SELECT, you'll be fine.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
I just did it but I have an error: the function 'SCOPE_IDENTITY' not defined in the expression
using (OleDbCommand cmd1 = sql\_con.CreateCommand()) { cmd1.CommandText = "INSERT INTO Commandes (montant\_com) VALUES (@montant\_com)"; cmd1.CommandText = "SELECT SCOPE\_IDENTITY()"; cmd1.Parameters.AddWithValue("@montant\_com", TxtTotalCmd.Text); cmd1.ExecuteNonQuery(); int id = Convert.ToInt32(cmd1.ExecuteScalar()); TxtNunCmd.Text = id.ToString(); } -
OK, the edited version makes a little more sense. You need to split your command into two: Do the INSERT, then build a new Command to do the SELECT (some DB engines don't like command chaining). As long as you use the same connection and you don't close it in between the INSERT and SELECT, you'll be fine.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Ah. That makes a difference. Access doesn't support SCOPE_IDENTITY: You need to use @@IDENTITY instead.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Ah. That makes a difference. Access doesn't support SCOPE_IDENTITY: You need to use @@IDENTITY instead.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
it’s already done sir but what I don't understand textbox does not recover the id, it displays zero
using (OleDbCommand cmd1 = sql_con.CreateCommand())
{cmd1.CommandText = "INSERT INTO Commandes (montant\_com) VALUES (@montant\_com)"; cmd1.CommandText = "SELECT @@IDENTITY"; cmd1.Parameters.AddWithValue("@montant\_com", TxtTotalCmd.Text); //sql\_cmd = new OleDbCommand(CommandText, sql\_con); cmd1.ExecuteNonQuery(); id = (int)cmd1.ExecuteScalar(); TxtNunCmd.Text = id.ToString(); } -
it’s already done sir but what I don't understand textbox does not recover the id, it displays zero
using (OleDbCommand cmd1 = sql_con.CreateCommand())
{cmd1.CommandText = "INSERT INTO Commandes (montant\_com) VALUES (@montant\_com)"; cmd1.CommandText = "SELECT @@IDENTITY"; cmd1.Parameters.AddWithValue("@montant\_com", TxtTotalCmd.Text); //sql\_cmd = new OleDbCommand(CommandText, sql\_con); cmd1.ExecuteNonQuery(); id = (int)cmd1.ExecuteScalar(); TxtNunCmd.Text = id.ToString(); }Do you know what the wonderful thing about banging your head on the desk is? Desk *BANG* Desk *BANG* Desk *BANG* Desk *BANG* Desk *BANG* Desk *BANG* It feels wonderful when you stop ... We've been here before. Yesterday in fact. Look at your code. What can you see that is wrong?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Do you know what the wonderful thing about banging your head on the desk is? Desk *BANG* Desk *BANG* Desk *BANG* Desk *BANG* Desk *BANG* Desk *BANG* It feels wonderful when you stop ... We've been here before. Yesterday in fact. Look at your code. What can you see that is wrong?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
:doh: :doh: :doh: :doh: you are right sir and I promise you find the solution and you ride it
:laugh:
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
Do you know what the wonderful thing about banging your head on the desk is? Desk *BANG* Desk *BANG* Desk *BANG* Desk *BANG* Desk *BANG* Desk *BANG* It feels wonderful when you stop ... We've been here before. Yesterday in fact. Look at your code. What can you see that is wrong?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
it probably is too early to stop. :rolleyes:
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
it probably is too early to stop. :rolleyes:
Luc Pattyn [My Articles] Nil Volentibus Arduum
You had to say that, didn't you? :laugh: Desk *BANG* Desk *BANG* Desk *BANG* ...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
You had to say that, didn't you? :laugh: Desk *BANG* Desk *BANG* Desk *BANG* ...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
You had to say that, didn't you? :laugh: Desk *BANG* Desk *BANG* Desk *BANG* ...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
using (OleDbCommand cmd1 = sql_con.CreateCommand())
{cmd1.CommandText = "INSERT INTO Commandes (montant\_com) VALUES (@montant\_com)"; OleDbCommand cmr = sql\_con.CreateCommand(); cmr.CommandText = "SELECT @@IDENTITY AS LastId"; cmd1.Parameters.AddWithValue("@montant\_com", TxtTotalCmd.Text); id = (int)cmr.ExecuteScalar(); TxtNunCmd.Text = id.ToString(); cmd1.ExecuteNonQuery(); }but i can't get the id
-
using (OleDbCommand cmd1 = sql_con.CreateCommand())
{cmd1.CommandText = "INSERT INTO Commandes (montant\_com) VALUES (@montant\_com)"; OleDbCommand cmr = sql\_con.CreateCommand(); cmr.CommandText = "SELECT @@IDENTITY AS LastId"; cmd1.Parameters.AddWithValue("@montant\_com", TxtTotalCmd.Text); id = (int)cmr.ExecuteScalar(); TxtNunCmd.Text = id.ToString(); cmd1.ExecuteNonQuery(); }but i can't get the id
Stop guessing, and start thinking. When is the ID actually created? What line of code causes that to happen?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
