problem retrieving info from one table to insert it into another

[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!