problem retrieving info from one table to insert it into another

[ago2486]

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 ();
                     }

[OriginalGriff]

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!

[ago2486]

Ok my apologies, this is the error I get
System.Data.Oledb.Ole Exception (0x80040E14): Character found after the end of the Sql instruction

using (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();

                }

[OriginalGriff]

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!

[OriginalGriff]

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!

[ago2486]

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();

                }

[ago2486]

and I wanted to clarify that I am on an access database

[OriginalGriff]

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!

[ago2486]

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();
                }

[OriginalGriff]

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!

[ago2486]

:doh: :doh: :doh: :doh: you are right sir and I promise you find the solution and you ride it

[OriginalGriff]

:laugh:

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!

[Luc Pattyn]

it probably is too early to stop. :rolleyes:

Luc Pattyn [My Articles] Nil Volentibus Arduum

[OriginalGriff]

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!

[ago2486]

yes yes sir I am still looking for, I will find it, I assure you sir

:doh: :doh: :doh: :doh:

[ago2486]

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

[OriginalGriff]

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!

[ago2486]

Hello sir and all my excuses for yesterday I had a connection problem that's why I disconnected but by dint of fighting all night I could find the solution regaré and tell me if the code is clean. I must say I am glad I did ...

:-D :-D :-D :-D

using (OleDbCommand cmd1 = sql_con.CreateCommand())
{

                    cmd1.CommandText = "INSERT INTO Commandes (montant\_com) VALUES (@montant\_com)";
                    cmd1.Parameters.AddWithValue("@montant\_com", TxtTotalCmd.Text);
                    cmd1.ExecuteNonQuery();
                    OleDbCommand cmr = sql\_con.CreateCommand();
                    cmr.CommandText = "SELECT @@IDENTITY AS LastId";
                    
                    
                    int LastId = Convert.ToInt32(cmr.ExecuteScalar());
                    TxtNunCmd.Text = LastId.ToString();
                    //sql\_cmd = new OleDbCommand(CommandText, sql\_con);
                    
                  
                }

[OriginalGriff]

Yes, that's fine - you don't need the "AS LastID" because ExecuteScalar doesn't return labels, but it doesn't matter if you add it.

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!