locked
Get the created identity ID into an INSERT Statement RRS feed

  • Question

  • User852530961 posted

    Hi Community, I would like to ask a question that is about the SQL in visual studio

    I have multiple tables that I have to perform the INSERT and it has an order for inserting the values into the tables. As for now, I was wondering how to get the identity Id that has been created t for my next Insert statement.

    For example, I have inserted records into the 'Order' table:

    Order_Id
    1
    2

    Now, I want to get the value from the Order_Id that has been inserted to my next INSERT statement for my 2nd table (Order_detail etc.)

    Example of INSERT statement that I want to do:

    INSERT INTO [Order_Detail] (Order_id)
    VALUES ((ID that has been inserted in the Order table)

    I tried on using @@IDENTITY but it only gets the latest value that has been inserted in the table.

    How can I achieve that?

    Please help,

    Thank you.

    Friday, January 22, 2021 7:03 AM

All replies

  • User452040443 posted

    Hi,

    Ideally, you should first insert it into the orders table and then insert it into the items table.

    I think that the way you posted it will not be possible to guarantee that the ID used in the items table will be the next one in the orders table considering a multiuser environment.

    And you'll also probably have problem of violation of foreign key items table.

    Hope this help

    Friday, January 22, 2021 12:23 PM
  • User852530961 posted

    Hi Imasps,

    Thank you for your reply,

    Yes, my logic on doing the INSERT is by doing the insert into the Order table first then into my another table (Order_detail etc.). As the Order table's Order_id in the Order_detail table is a foreign key.

    As for now, the steps I was thinking is something like this:

    1. Insert the data into the Order Table
    2. Retrieve the order_id that has been inserted 
    3. Insert the order_id that has been inserted into the Order table into the Order_Detail table

    In summary, the order table must be trigger before moving on doing the insert the data into order detail table.

    Thank you

    Friday, January 22, 2021 12:56 PM
  • User852530961 posted

    Hi everyone, 

    Currently, I am struggling to get the value from the database in my controller. 

     string payment = @"INSERT INTO Payment(Payment_Method,Currency_Type,Total_Amount) 
                                 VALUES('{0}','{1}',{2});";
                int pay = DBUtl.ExecSQL(payment, "Cash", currency,total);
                if (pay == 1)
                {
                    string pid = "SELECT Payment_id FROM Payment WHERE Paid_Date = GETDATE()";
                    int paymentid = Convert.ToInt32(pid);
                        if (cart.Count() != 0)
                {
                    string order = @"INSERT INTO [Order](Order_Name,Order_Description,Order_Quantity,Payment_Id) 
                             VALUES('{0}','{1}',{2},{3})";
                    foreach (var item in cart)
                    {
                            int ord = DBUtl.ExecSQL(order, item.Cart_Service, item.Additional_Notes, item.Quantity,paymentid);

    As you can see my first step is to insert into my Payment table and after that, I want to get the payment_id for inserting as it is a foreign key in my Order table. 

    Now, I am assigned a value and using a SELECT statement with the condition to get the Payment_Id but I am unable to get the payment_id with the method that I am using right now.

    How can do this,

    Please help

    Thank You

    Note: I understand the duplicate post but I really need help on this URGENTLY and I have made "Alert Moderators" to remove the post.

    Sunday, January 24, 2021 9:10 AM
  • User475983607 posted

    Use the SCOPE_IDENTITY() function to get the last identity created.

     string payment = @"INSERT INTO Payment(Payment_Method,Currency_Type,Total_Amount) 
                                 VALUES('{0}','{1}',{2});
                                 SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];";

    Invoke ExecuteScalar in your ADO.NET utility to return the Id. 

    Note: I understand the duplicate post but I really need help on this URGENTLY and I have made "Alert Moderators" to remove the post.

    Getting the identity in TSQL is very common easily found with a Google search.  The key here is realizing your question is related to TSQL syntax not ASP.NET Core.

    Sunday, January 24, 2021 1:20 PM
  • User852530961 posted

    Hi mgebhard,

    Thank you for your reply.

    Is the ExecuteScalar refers to this one:

    using (SqlConnection con=new SqlConnection(@"Your connectionString"))
        {
            using(SqlCommand cmd=new SqlCommand("INSERT INTO Payment(Payment_Method,Currency_Type,Total_Amount) output INSERTED.Payment_id VALUES(@Payment_Method,@Currency_Type,@Total_Amount)",con))
            {
                cmd.Parameters.AddWithValue("@Payment_Method", "Cash");
                cmd.Parameters.AddWithValue("@Currency_Type", currency);
                cmd.Parameters.AddWithValue("@Total_Amount", total);
                con.Open();
            
                int payment_id =Convert.ToInt32(cmd.ExecuteScalar());
            
                if (con.State == System.Data.ConnectionState.Open) 
                    con.Close();
            
                return payment_id ;
            }
    }

    If yes, then my answer will be this method does not work, I tried already and it shows an error "Cannot implicitly convert type 'int' to IActionResult"

    Thank you

    Sunday, January 24, 2021 1:23 PM
  • User475983607 posted

    Kevinyong

    If yes, then my answer will be this method does not work, I tried already and it shows an error "Cannot implicitly convert type 'int' to IActionResult"

    The error is alerting you of a casting error.  It is not clear why you are trying to cast an int to IActionResult.  Are you returning the ID to an AJAX request?

    Anyway, you did not share enough code to figure out where the holes are in your understanding.

    Sunday, January 24, 2021 1:39 PM
  • User852530961 posted

    The error is telling you that you coding a casting error.  It is not clear why you are trying to convert an int to IActionResult.  Are you returning the ID to an AJAX request?

    Anyway, you did not share enough code to figure out where the holes are in your understanding.

    As for now, I have an INSERT statement that inserts into Payment table and the following code is how I do it:

    string payment = @"INSERT INTO Payment(Payment_Method,Currency_Type,Total_Amount) 
                                 VALUES('{0}','{1}',{2});";
                int pay = DBUtl.ExecSQL(payment, "Cash", currency,total);
                if (pay == 1)
                {
                    string pid = "SELECT Payment_id FROM Payment WHERE Paid_Date = GETDATE()";
                    int paymentid = Convert.ToInt32(pid);

    As you can use there is a "pid" where l used to get the 'payment_id' based on the condition that I set. The 'pid' I will be using for inserting into the foreign key which is payment_id in the Order table. 

    The following codes display the insert for the order table:

    string order = @"INSERT INTO [Order](Order_Name,Order_Description,Order_Quantity,Payment_Id) 
                             VALUES('{0}','{1}',{2},{3})";
                    foreach (var item in cart)
                    {
                            int ord = DBUtl.ExecSQL(order, item.Cart_Service, item.Additional_Notes, item.Quantity,paymentid);
    Sunday, January 24, 2021 2:18 PM
  • User475983607 posted

    As for now, I have an INSERT statement that inserts into Payment table and the following code is how I do it:

    string payment = @"INSERT INTO Payment(Payment_Method,Currency_Type,Total_Amount) 
                                 VALUES('{0}','{1}',{2});";
                int pay = DBUtl.ExecSQL(payment, "Cash", currency,total);
                if (pay == 1)
                {
                    string pid = "SELECT Payment_id FROM Payment WHERE Paid_Date = GETDATE()";
                    int paymentid = Convert.ToInt32(pid);

    As you can use there is a "pid" where l used to get the 'payment_id' based on the condition that I set. The 'pid' I will be using for inserting into the foreign key which is payment_id in the Order table. 

    Kevinyong, pid is a SQL script.   This code thrown an exception if pay equals 1.

    if (pay == 1)
    {
        string pid = "SELECT Payment_id FROM Payment WHERE Paid_Date = GETDATE()";
        int paymentid = Convert.ToInt32(pid);

    I recommend learning how to use the Visual Studio debugger to double check the code you wrote matches your expectation. 

    https://docs.microsoft.com/en-us/visualstudio/debugger/debugger-feature-tour?view=vs-2019

    Again, use SCOPE_IDENTITY() to get the newly created row identity.  You'll need to modify your data access code to handle this requirement.  Share you code if you need assistance with the code.

    Sunday, January 24, 2021 3:02 PM
  • User852530961 posted

    Hi mgebhard,

    Thank you for your reply

    Is it ok for me to put my action codes for this question, so you can help me take a look?

    Sunday, January 24, 2021 3:09 PM
  • User-1330468790 posted

    Hi Kevinyong,

     

    Thank you for your question. 

    As @mgebhard said, your question in this thread is related to TSQL, if you want to make the codes working, you have to be aware of few key points in your codes.

     

    Convert 'pid' to 'paymentid':

    string pid = "SELECT Payment_id FROM Payment WHERE Paid_Date = GETDATE()";
                    int paymentid = Convert.ToInt32(pid);

    You should not directly convert a string variable to int as it is not in a right format. I think you need to execute the sql first and then convert the result to INTEGER type.

      

    SqlCommand.ExecuteScalar() return value:

    If you execute the query, it will return the first column of the first row in the result set returned by the query. Additional columns or rows are ignored. If you want to get the last insert index, you definitely could append below query to your INSERT query and get the last index from ExecuteScalar() method.

    SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

    The only thing you need to consider is to deal with the null value if the INSERT is failed and the 'SCOPE_IDENTITY()' will be NULL.

     

    Now, your problem seems a complex since the problem is related to handling the combination of Sql command - (INSERT + SCOPE_IDENTITY() ) + Action method which is might a different topic. 

     

    I suggest you split the problem into different smaller parts.

    If you still have questions about fetching the last insert index and inserting it as a foreign key in another table, feel free to let us know.

    You could try to execute the TSQL with some given parameter until you could get the expected return value. 

    Decoupling the problem would be much helpful to solve a complex problem.

      

    Thank you for understanding.

    Best regards,

    Sean

    Thursday, January 28, 2021 3:21 AM
  • User-252651875 posted

    You can SET IDENTITY_INSERT [OrderItem] OFF

    Wednesday, February 3, 2021 8:13 AM