Click here to Skip to main content
15,885,216 members
Home / Discussions / C#
   

C#

 
AnswerRe: need help please Pin
Mycroft Holmes8-Dec-12 1:02
professionalMycroft Holmes8-Dec-12 1:02 
GeneralRe: need help please Pin
Gondzer8-Dec-12 1:09
Gondzer8-Dec-12 1:09 
GeneralRe: need help please Pin
Mycroft Holmes8-Dec-12 1:41
professionalMycroft Holmes8-Dec-12 1:41 
AnswerRe: need help please Pin
vr9999999998-Dec-12 1:04
vr9999999998-Dec-12 1:04 
GeneralRe: need help please Pin
Gondzer8-Dec-12 1:17
Gondzer8-Dec-12 1:17 
AnswerRe: need help please Pin
OriginalGriff8-Dec-12 2:03
mveOriginalGriff8-Dec-12 2:03 
AnswerRe: need help please Pin
Jibesh9-Dec-12 22:03
professionalJibesh9-Dec-12 22:03 
QuestionInsert Data to Master/Detail table in Sqlserver 2005 from C# with Foreign key in Detail table Pin
ahmed_one7-Dec-12 20:31
ahmed_one7-Dec-12 20:31 
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, 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 = "";



    }


"Dm.GetData" is the Data Access class method created for the purpose of just retrieving reocrds...

And in the Save button click event:

C#
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;

    }



What I need is to save the Data to Sql Server in respective table (Orders and OrderDetails) which my code can't 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
AnswerRe: Insert Data to Master/Detail table in Sqlserver 2005 from C# with Foreign key in Detail table Pin
Eddy Vluggen8-Dec-12 2:49
professionalEddy Vluggen8-Dec-12 2:49 
GeneralRe: Insert Data to Master/Detail table in Sqlserver 2005 from C# with Foreign key in Detail table Pin
ahmed_one8-Dec-12 22:55
ahmed_one8-Dec-12 22:55 
GeneralRe: Insert Data to Master/Detail table in Sqlserver 2005 from C# with Foreign key in Detail table Pin
Eddy Vluggen8-Dec-12 23:59
professionalEddy Vluggen8-Dec-12 23:59 
QuestionContinuous listening of COM port for detecting MODEM?? Pin
shubhamjoshi7-Dec-12 19:59
shubhamjoshi7-Dec-12 19:59 
AnswerRe: Continuous listening of COM port for detecting MODEM?? Pin
vr9999999998-Dec-12 1:08
vr9999999998-Dec-12 1:08 
GeneralRe: Continuous listening of COM port for detecting MODEM?? Pin
Mycroft Holmes8-Dec-12 1:42
professionalMycroft Holmes8-Dec-12 1:42 
AnswerRe: Continuous listening of COM port for detecting MODEM?? Pin
Andy41111-Dec-12 1:51
Andy41111-Dec-12 1:51 
Questionreturning the largest int variable Pin
RichB377-Dec-12 12:54
RichB377-Dec-12 12:54 
AnswerRe: returning the largest int variable Pin
PIEBALDconsult7-Dec-12 14:15
mvePIEBALDconsult7-Dec-12 14:15 
GeneralRe: returning the largest int variable Pin
RichB377-Dec-12 15:00
RichB377-Dec-12 15:00 
GeneralRe: returning the largest int variable Pin
Brisingr Aerowing7-Dec-12 17:03
professionalBrisingr Aerowing7-Dec-12 17:03 
AnswerRe: returning the largest int variable Pin
Dave Kreskowiak7-Dec-12 18:41
mveDave Kreskowiak7-Dec-12 18:41 
GeneralRe: returning the largest int variable Pin
RichB377-Dec-12 18:51
RichB377-Dec-12 18:51 
AnswerRe: returning the largest int variable Pin
CommDev8-Dec-12 23:52
CommDev8-Dec-12 23:52 
QuestionManual serialization of a class that contains the same class Pin
nadavst7-Dec-12 11:34
nadavst7-Dec-12 11:34 
AnswerRe: Manual serialization of a class that contains the same class Pin
Richard MacCutchan7-Dec-12 22:02
mveRichard MacCutchan7-Dec-12 22:02 
GeneralRe: Manual serialization of a class that contains the same class Pin
nadavst7-Dec-12 23:23
nadavst7-Dec-12 23:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.