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

[EDN Admin]

I need some help to make a Order Form in C#. My development environment is:<br/>
<br/>
Microsoft Visual Studio 2010 Ultimate Sql Server Express Edition 2005 Programming Language C# Sample Database = NorthWind (Tables=Orders and OrderDetails)<br/>
<br/>
Ive 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:<br/>
<br/>
In the form load event Ive the following code:
<pre class="prettyprint 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, Quantity) VALUES (@PrdID, @Up,@Qty)");

//commandDet.Parameters.Add("@OrdID", SqlDbType.NVarChar, 15, "OrderID").Value = txtOrdID.Text; ;
commandDet.Parameters.Add("@PrdId", SqlDbType.NVarChar, 5, "ProductID");
commandDet.Parameters.Add("@Up", SqlDbType.VarChar, 50, "UnitPrice");
commandDet.Parameters.Add("@Qty", SqlDbType.VarChar, 20, "Quantity");
sqlDataDet.InsertCommand = commandDet;

commandDet = new SqlCommand("UPDATE OrderDetails SET ProductID = @PrdID, UnitPrice = @Up, Quantity = @Qty WHERE OrderID = @OrdID");
commandDet.Parameters.Add("@OrdID", SqlDbType.NVarChar, 15, "OrderID").Value = txtOrdID.Text; ;
commandDet.Parameters.Add("@PrdId", SqlDbType.NVarChar, 5, "ProductID");
commandDet.Parameters.Add("@Up", SqlDbType.VarChar, 50, "UnitPrice");
commandDet.Parameters.Add("@Qty", SqlDbType.VarChar, 20, "Quantity");
sqlDataDet.UpdateCommand = commandDet;

sqlComBldMaster = new SqlCommandBuilder(sqlDataMaster);
sqlComBldDet = new SqlCommandBuilder(sqlDataDet);

dt = new DataTable();
dtDet = new DataTable();


dt.Clear();
dtDet.Clear();


sqlDataMaster.FillSchema(dt, SchemaType.Source);
sqlDataDet.FillSchema(dtDet, SchemaType.Source);

dtDet.Columns["OrderID"].AutoIncrement = true;
dtDet.Columns["OrderID"].AutoIncrementSeed = -1;
dtDet.Columns["OrderID"].AutoIncrementStep = -1;
ds = new DataSet();
ds.Tables.Add(dt);
ds.Tables.Add(dtDet);
ds.EnforceConstraints = false;



DataRelation rel = new DataRelation("OrdersRel", ds.Tables["Orders"].Columns["OrderID"], ds.Tables["OrderDetails"].Columns["OrderID"]);
ds.Relations.Add(rel);

bs = new BindingSource();
bsDet = new BindingSource();

bs.DataSource = ds;
bs.DataMember = "Orders";

bsDet.DataSource = ds;
bsDet.DataMember = "OrderDetails";


dgInvDet.AutoGenerateColumns = false;

dgInvDet.Columns["ProductID"].DataPropertyName = "ProductID";
ProductID.DataSource = dm.GetData("Select * from Products order by ProductName");
ProductID.DisplayMember = "ProductName";
ProductID.ValueMember = "ProductID";
dgInvDet.Columns["UnitPrice"].DataPropertyName = "UnitPrice";
dgInvDet.Columns["Quantity"].DataPropertyName = "Quantity";

dgInvDet.DataSource = bsDet;



}


public void SetComb()
{
cmbCust.DataSource = dm.GetData("Select * from Customers order by CompanyName");
cmbCust.DisplayMember = "CompanyName";
cmbCust.ValueMember = "CustomerId";
cmbCust.Text = "";



}[/code]
<br/>
"Dm.GetData" is the Data Access class method created for the purpose of just retrieving reocrds...
And in the Save button click event:
<pre class="prettyprint private void btnSave_Click(object sender, EventArgs e)
{


dt.EndInit();
rec = sqlDataMaster.Update(ds.Tables[0]);
rec += sqlDataDet.Update(ds.Tables[1]);
//recDet = sqlDataDet.Update(dt);

ds.AcceptChanges();


MessageBox.Show(rec + " record(s) applied...." );

ds.EnforceConstraints = true;

}[/code]
<br/>
What I need is to save the Data to Sql Server in respective table (Orders and OrderDetails) which my code cant seems to do it and it shows error that Foreign Key cannot be null...Because OrderDetails table also needs OrderID which is Foreign Key, and I
am unable to understand how can I get the OrderID, as it is Auto generated after data is inserted in Database.

Please help me on this problem to save the data in database with this foreign key issue...
Any help will be much appreciated.
Thanks
Ahmed



View the full article