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