David Green

David Green

  • NA
  • 68
  • 1.6k

asp.net webform. Passing the @@identity to another protected void

Nov 27 2022 2:14 PM

I have 2 simple inserts to 2 tables. I want to take the new row ID from the first insert and pass that to a second insert to make both inserts work together
But I get get the value to pass
Any help you be apprecaited as its an old site but not easily converted to modern arhitecture

 

 protected void allocateBTN_Click(object sender, EventArgs e)
    {

        InsertPayment();
        //Response.Redirect("CustomerDetails.aspx?CustID=" + Request.QueryString["CustID"]);
        
    }

    protected void InsertPayment()
    {
        //Data connection
        SqlConnection conn;
        SqlCommand comm;
        // Read the connection string
        string connectionString = ConfigurationManager.ConnectionStrings["StoreConnection"].ConnectionString;
        conn = new SqlConnection(connectionString);

        comm = new SqlCommand("INSERT INTO Payments (CustID, BookingID, Date, Amount, PaymentType, Approved)" +
        "VALUES (@CustID, @BookingID, @Date, @Amount, @PaymentType, 'Yes'); SELECT @@IDENTIY();", conn);
        //Add command Parameters
        comm.Parameters.AddWithValue("@CustID", Request.QueryString["CustID"].ToString());
        comm.Parameters.AddWithValue("@BookingID", Request.QueryString["BookingID"].ToString());
        comm.Parameters.AddWithValue("@Date", Convert.ToDateTime(PayDateTextBox.Text));
        comm.Parameters.AddWithValue("@Amount", AmountTB.Text);
        comm.Parameters.AddWithValue("@PaymentType", dd_Card.SelectedItem.Text);

        try
        {
            // open connection
            conn.Open();
            // execute
            comm.ExecuteNonQuery();
            // Reload page if query ok
            int PayID = (int)comm.ExecuteScalar();
            InsertTrust(PayID);

        }
        catch
        {
            // display error if fails
            // dbErrorMessage.Text = "Error Submitting News Article";
        }
        finally
        {
            //Close conneciton
            conn.Close();
        }

    }

    protected void InsertTrust(int PayID)
    {
        //Data connection
        SqlConnection conn;
        SqlCommand comm;
        // Read the connection string
        string connectionString = ConfigurationManager.ConnectionStrings["StoreConnection"].ConnectionString;
        conn = new SqlConnection(connectionString);

        comm = new SqlCommand("INSERT INTO TrustBalance (PaymentID, BookingID, CustID, ProductID, Date, Item, Amount, PlusMinus, PaymentType)" +
        "VALUES (@PaymentID @BookingID, @CustID, @ProductID, @Date, @Item, @Amount, 'Plus', @PaymentType)", conn);
        //Add command Parameters
        comm.Parameters.AddWithValue("PaymentID", PayID);
        comm.Parameters.AddWithValue("@BookingID", Request.QueryString["BookingID"].ToString());
        comm.Parameters.AddWithValue("@CustID", Request.QueryString["CustID"].ToString());
        comm.Parameters.AddWithValue("@ProductID", Request.QueryString["ProductID"].ToString());
        comm.Parameters.AddWithValue("@Date", DateTime.Now);
        comm.Parameters.AddWithValue("@Item", TourTextBox.Text + ' ' + NameTextBox.Text);
        comm.Parameters.AddWithValue("@Amount", AmountTB.Text);
        comm.Parameters.AddWithValue("@PaymentType", dd_Card.SelectedItem.Text);

        try
        {
            // open connection
            conn.Open();
            // execute
            comm.ExecuteNonQuery();
            // Reload page if query ok

        }
        catch
        {
            // display error if fails
            // dbErrorMessage.Text = "Error Submitting News Article";
        }
        finally
        {
            //Close conneciton
            conn.Close();
        }
    }


Answers (13)