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