0

Using SQLExpress --

I have a table Orders that contains a PK of order_id. I have a second table Items that contains model and quantity along with a FK of order_id.

I am using a gridview to collect model/quantity data for an order and then I want to take that gridview data row by row and insert a new row into Orders, then insert it into the Items table, using the same order_id for each row of the Gridview. Problem is that the order_id doesn't exist until the first Insert is done.

After researching I had hoped I'd found a solution but it is not working as expected. I don't think the title of my question makes sense but I have no idea how to word it. Thanks in advance!

UPDATE: I have tried the edits suggested in the comments and referenced the link provided but still have not gotten my answer.

Below is my code:

Gridview Code:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" HeaderStyle-HorizontalAlign="Center" HeaderText="Assigned" HorizontalAlign="Center" Width="346px">
<Columns>
    <asp:BoundField DataField="Manuf" HeaderText="Manuf" />
    <asp:BoundField DataField="Model" HeaderText="Model" />
    <asp:BoundField DataField="Qty" HeaderText="Qty" />
    <asp:BoundField DataField="Notes" HeaderText="Notes" />
</Columns>
</asp:GridView>

Code behind to call Stored Procedure:

 protected void CreateOrder_Click(object sender, EventArgs e)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["HMTDBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                foreach (GridViewRow g1 in GridView1.Rows)
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = "sp_ProdDataInsert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@customer_id", TextBox1.Text);
                    cmd.Parameters.AddWithValue("@manuf_name", g1.Cells[0].Text);
                    cmd.Parameters.AddWithValue("@model_name", g1.Cells[1].Text);
                    cmd.Parameters.AddWithValue("@qty", g1.Cells[2].Text);
                    cmd.Parameters.AddWithValue("@notes", g1.Cells[3].Text);
                    cmd.Parameters.AddWithValue("@track_num", TextBox2.Text);
                    cmd.Parameters.AddWithValue("@pckg_num", TextBox3.Text);

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();

                    MessageBox.Show("New Order Created!");
                    Response.Redirect("NewOrder.aspx");
                }
            }
        }

Stored Procedure Code:

ALTER PROCEDURE [dbo].[sp_ProdDataInsert]

@customer_id INT,
@manuf_name NVARCHAR (50),
@model_name NVARCHAR (50),
@qty NUMERIC,
@notes NVARCHAR (255),
@track_num NVARCHAR (50),
@pckg_num NUMERIC,
@order_id int OUTPUT AS



BEGIN
INSERT INTO Orders
    (customer_id, track_num, pckg_num)
VALUES
    (@customer_id, @track_num, @pckg_num)

SELECT @order_id = coalesce(MAX(order_id),0)+1 FROM Orders (UPDLOCK)
INSERT INTO Items
    (manuf, model, qty, notes, order_id)
VALUES
    (@manuf_name, @model_name, @qty, @notes, @order_id)


END
0

I think that if you just change this line it should work for you.

SET @order_id = SCOPE_IDENTITY();

Your method of grabbing the max id and adding 1 is not safe for concurrent access, scope_identity() is the correct way of getting the ID of the just inserted row.

  • I made the alteration and received this error: System.Data.SqlClient.SqlException: 'Procedure or function 'sp_ProdDataInsert' expects parameter '@order_id', which was not supplied.' – Kalen Kaczor Oct 4 '19 at 18:09
  • Sorry, I didn't look at the application code side very closely (also not my strong side). You aren't adding a parameter to deal with the @order_id. Maybe this will help? stackoverflow.com/questions/290652/… – Jonathan Fite Oct 4 '19 at 18:13
  • I've reviewed the link but it doesn't seem to be what I'm looking for. I don't want to retrieve the output via ado.net because I want to just hand it off to the second part of my stored procedure (or linked to a second stored procedure via my first) that will take the output parameter and use it as an input parameter. It's starting to sound like I'll need to make two separate stored procedures, which is fine, but I'd rather avoid additional C# code between the two and have them both execute on one button click? Thanks! – Kalen Kaczor Oct 6 '19 at 12:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.