Insert Data to Master/Detail table in Sqlserver 2005 from C# with Foreign key in Detail table

ahmed_one

I need some help to make a Order Form in C#. My development environment is: Microsoft Visual Studio 2010 Ultimate Sql Server Express Edition 2005 Programming Language C# Sample Database = NorthWind (Tables=Orders and OrderDetails) I've create a Form for order dataentry, which contain Textbox for OrderID, Combobox for Customer, DateTimePickers for OrderDate and ShippedDate and a DataGridView which contains Cokumns OrderID=ReadOnly, ProductID, UnitPrice & Quantity: In the form load event I've the following code:

private void Inv2_Load(object sender, EventArgs e)
{

    SetComb();
    connectionString = ConfigurationManager.AppSettings\["connectionString"\];
    sqlConnection = new SqlConnection(connectionString);

    qryOrd = "Select OrderID, CustomerID, OrderDate, ShippedDate from Orders";
    qryOrdDet = "Select OrderID, ProductID, UnitPrice, Quantity from OrderDetails";

    sqlConnection.Open();
    sqlDataMaster = new SqlDataAdapter(qryOrd, sqlConnection);
    sqlDataDet = new SqlDataAdapter(qryOrdDet, sqlConnection);

    //SET MASTER INSERT/UPDATES
    command = new SqlCommand("INSERT INTO Orders ( CustomerID, OrderDate, ShippedDate) VALUES (@CustID, @OrdDt, @ShipDt) SELECT SCOPE\_IDENTITY();");

    command.Parameters.Add("@OrdID", SqlDbType.NVarChar, 15);
    command.Parameters.Add("@CustID", SqlDbType.VarChar, 15);
    command.Parameters\["@CustID"\].Value = cmbCust.SelectedText;

    command.Parameters.Add("@OrdDt", SqlDbType.DateTime);
    command.Parameters\["@OrdDt"\].Value = dtOrdDt.Text;

    command.Parameters.Add("@ShipDt", SqlDbType.DateTime);
    command.Parameters\["@ShipDt"\].Value =dtShipDt.Text;



    sqlDataMaster.InsertCommand = command;
    //string id = command.ExecuteScalar().ToString();

    command = new SqlCommand("UPDATE Orders SET CustomerID = @CustID, OrderDate = @OrdDt, ShippedDate = @ShipDt WHERE OrderID = @OrdID");

    command.Parameters.Add("@OrdID", SqlDbType.NVarChar, 15, "OrderID").Value = txtOrdID.Text; 
    command.Parameters.Add("@CustID", SqlDbType.VarChar, 15, "CustomerID").Value = cmbCust.Text;
    command.Parameters.Add("@OrdDt", SqlDbType.DateTime).Value = dtOrdDt.Text;
    command.Parameters.Add("@ShipDt", SqlDbType.DateTime).Value = dtShipDt.Text;
    sqlDataMaster.UpdateCommand = command;


    //SET DETAILS INSERT/UPDATES
    commandDet = new SqlCommand("INSERT INTO OrderDetails (ProductID, UnitPrice

Lost User

ahmed_one wrote:

and I am unable to understand how can I get the OrderID, as it is Auto generated after data is inserted in Database.

You could select the last identity[^].

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

ahmed_one

Thanks for reply, My problem is OrderID is not generated because of error that child table is not saved..the whole save procedure is failed because of this and compiler shows the error.

Lost User

ahmed_one wrote:

My problem is OrderID is not generated because of error that child table is not saved..

Save the Order first, fetch the identity, save the child-records.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]