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();
}
}
Aravind GovindarajPosted Nov 27, 2022, 5:58 PM
can you comment comm.ExecuteNonQuery(); and just use execute scalar. Also please change from @@identity to scope identity, scope identity is better than identity.
let me know if its persist :)
Aravind GovindarajPosted Nov 28, 2022, 11:23 AM
It inserts the payment twice due to the below code
comm.ExecuteNonQuery(); // executes so one entry -- comment this line and try
int PayID = (int)comm.ExecuteScalar(); // executes one more entry
let me know if this persists :)
David GreenPosted Nov 28, 2022, 11:22 AM
Fixed and working, thank you @Aravind
David GreenPosted Nov 28, 2022, 11:16 AM
A step closer
It inserts but it also firest the insert payment twice?
David GreenPosted Nov 28, 2022, 11:05 AM
ok so I have aed the @ to the paymentID and the commer, but the 2nd function still doesnt fire, neither does the first
Aravind GovindarajPosted Nov 28, 2022, 9:10 AM
Please check if are you getting payid inside your InsertTrust method if so @ symbol typo is the issue :)
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); // Change it to @PaymentID
Abhishek YadavPosted Nov 28, 2022, 8:20 AM
SqlParameter[] sp = new SqlParameter[3];
add this 3 is no of parametes like 0,1,2,3,4
David GreenPosted Nov 28, 2022, 7:55 AM
I have tried that too, but I cant get it to fire from the first function to the secon
David GreenPosted Nov 27, 2022, 4:59 PM
I thought this created the param to pass?
// execute
comm.ExecuteNonQuery();
// Reload page if query ok
int PayID = (int)comm.ExecuteScalar();
InsertTrust(PayID);
David GreenPosted Nov 27, 2022, 4:49 PM
Thats a stored proceedure?
This is on a web form cs page
Aravind GovindarajPosted Nov 27, 2022, 4:48 PM
Please find the example in link
https://stackoverflow.com/questions/8503167/how-to-get-a-auto-generate-number-in-sql
David GreenPosted Nov 27, 2022, 4:26 PM
Can you show me please?
Aravind GovindarajPosted Nov 27, 2022, 3:49 PM
Create a Out param in first insert and consume the same and pass it to second insert