none
Updating Records in Sql Server Database with C#.net RRS feed

  • Question

  • Hello all I have two c# asp.net pages that I am inserting data into which goes to a sql server database. They are Receiving and Shipping. Once a user enters the data in Receiving which goes to the Receiving table and go to input it on the Shipping page i need the data that they input from the Shipping page to modify the amounts that are in the Receiving Page to keep consistent numbers and also add the data to the Shipping table. My code is posted below:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data.SqlClient;
    using System.Data;
    
    
    
        public partial class Shipping : System.Web.UI.Page
        {
            DataSet ds1;
            SqlDataAdapter da;
            int MaxRows = 0;
            int inc = 0;
    
    
    
            
            protected void MessageBox(string msg)
            {
                Label lbl = new Label();
                lbl.Text = "<script language='javascript'>" + Environment.NewLine + "window.alert('" + msg + "')</script>";
                Page.Controls.Add(lbl);
            }
            protected void Page_Load(object sender, EventArgs e)
            {
                PopulateCust();
                PopulateMatType();
    
            public void PopulateMatType()
            {
                
                DataTable dt = new DataTable();
                string id = string.Empty;
                string newName = string.Empty;
    
                SqlConnection thisConnection = new SqlConnection("Data Source=MAINSERVER;Initial Catalog=CW-IS;User ID=cwadmin;Password=#conceptswest#");
    
                thisConnection.Open();
    
                string commandString = "select * from[CW-IS].dbo.[Moisture Master] ORDER BY [Product Type] ";
                SqlCommand sqlCmd = new SqlCommand(commandString, thisConnection);
                SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
    
                sqlDa.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        id = dt.Rows[i]["Product Type"].ToString();
                        newName = id;
                        ddlMatType.Items.Add(new ListItem(id));
    
                        thisConnection.Close();
                    }
                }
            }
    
            public void UpdateRec()
            {
                SqlConnection thisConnection = new SqlConnection("Data Source=MAINSERVER;Initial Catalog=CW-IS;User ID=cwadmin;Password=#conceptswest#");
    
                SqlCommand nonqueryCommand1 = thisConnection.CreateCommand();
    
                thisConnection.Open();
    
                nonqueryCommand1.CommandText = "UPDATE [CW-IS].dbo.Recieving SET [QTY] = @QTY, [Pallet_Number] = @Pallet_Number, [Pallet_Weight] = @Pallet_Weight, [Total] = @Total WHERE [Customer] = @Customer";
    
                nonqueryCommand1.Parameters.Add("@Customer", SqlDbType.NVarChar, 50);
                nonqueryCommand1.Parameters.Add("@QTY", SqlDbType.NVarChar, 50);
                nonqueryCommand1.Parameters.Add("@Pallet_Number", SqlDbType.NVarChar, 50);
                nonqueryCommand1.Parameters.Add("@Pallet_Weight", SqlDbType.NVarChar, 50);
                nonqueryCommand1.Parameters.Add("@Total", SqlDbType.NVarChar, 50);
    
                nonqueryCommand1.Parameters["@Customer"].Value = ddlCust.SelectedValue;
                nonqueryCommand1.Parameters["@QTY"].Value = txtQTY.Text;
                nonqueryCommand1.Parameters["@Pallet_Number"].Value = txtNumofPall.Text;
                nonqueryCommand1.Parameters["@Pallet_Weight"].Value = txtWgtPerPallet.Text;
                nonqueryCommand1.Parameters["@Total"].Value = txtTotal.Text;
    
                nonqueryCommand1.ExecuteNonQuery();
    
                thisConnection.Close();
            }
    
    
            public void PopulateCust()
            {
                
                DataTable dt = new DataTable();
                string id = string.Empty;
                string name = string.Empty;
                string newName = string.Empty;
    
                SqlConnection thisConnection = new SqlConnection("Data Source=MAINSERVER;Initial Catalog=CW-IS;User ID=cwadmin;Password=#conceptswest#");
    
                thisConnection.Open();
    
                string commandString = "select * from[CW-IS].dbo.CUST_CODES";
                SqlCommand sqlCmd = new SqlCommand(commandString, thisConnection);
                SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
    
                sqlDa.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        id = dt.Rows[i]["CURRENT"].ToString();
                        name = dt.Rows[i]["NAME"].ToString();
                        newName = id + "--" + name;
                        ddlCust.Items.Add(new ListItem(newName, id));
    
                        thisConnection.Close();
    
                    }
                }
            }
    
    
            protected void btnInsertData_Click(object sender, EventArgs e)
            {
                
    
                SqlConnection thisConnection = new SqlConnection("Data Source=MAINSERVER;Initial Catalog=CW-IS;User ID=cwadmin;Password=#conceptswest#");
    
                SqlCommand nonqueryCommand = thisConnection.CreateCommand();
    
                thisConnection.Open();
    
                nonqueryCommand.CommandText = "Insert into [CW-IS].dbo.Shipping (Date, Customer, Material_Type, Item_Number, Lot_Number, BOL_Number, Location, QTY, Facility, PKG, Pallet_Number, Pallet_Weight, Weight_Options, Total) Values(@Date, @Customer, @Material_Type, @Item_Number, @Lot_Number, @BOL_Number, @Location, @QTY, @Facility, @PKG, @Pallet_Number, @Pallet_Weight, @Weight_Options, @Total)";
    
                nonqueryCommand.Parameters.Add("@Date", SqlDbType.NVarChar, 50);
                nonqueryCommand.Parameters.Add("@Customer", SqlDbType.NVarChar, 50);
                nonqueryCommand.Parameters.Add("@Material_Type", SqlDbType.NVarChar, 50);
                nonqueryCommand.Parameters.Add("@Location", SqlDbType.NVarChar, 50);
                nonqueryCommand.Parameters.Add("@Lot_Number", SqlDbType.NVarChar, 50);
                nonqueryCommand.Parameters.Add("@BOL_Number", SqlDbType.NVarChar, 50);
                nonqueryCommand.Parameters.Add("@Item_Number", SqlDbType.NVarChar, 50);
                nonqueryCommand.Parameters.Add("@QTY", SqlDbType.NVarChar, 50);
                nonqueryCommand.Parameters.Add("@Facility", SqlDbType.NVarChar, 50);
                nonqueryCommand.Parameters.Add("@PKG", SqlDbType.NVarChar, 50);
                nonqueryCommand.Parameters.Add("@Pallet_Number", SqlDbType.NVarChar, 50);
                nonqueryCommand.Parameters.Add("@Pallet_Weight", SqlDbType.NVarChar, 50);
                nonqueryCommand.Parameters.Add("@Weight_Options", SqlDbType.NVarChar, 50);
                nonqueryCommand.Parameters.Add("@Total", SqlDbType.NVarChar, 50);
    
    
                nonqueryCommand.Parameters["@Date"].Value = txtDateIn.Text;
                nonqueryCommand.Parameters["@Customer"].Value = ddlCust.SelectedValue;
                nonqueryCommand.Parameters["@Material_Type"].Value = ddlMatType.Text;
                nonqueryCommand.Parameters["@Lot_Number"].Value = txtLotNum.Text;
                nonqueryCommand.Parameters["@BOL_Number"].Value = txtBOLNum.Text;
                nonqueryCommand.Parameters["@Item_Number"].Value = txtItemNum.Text;
                nonqueryCommand.Parameters["@Location"].Value = txtLoc.Text;
                nonqueryCommand.Parameters["@QTY"].Value = txtQTY.Text;
                nonqueryCommand.Parameters["@Facility"].Value = ddlFac.SelectedValue;
                nonqueryCommand.Parameters["@PKG"].Value = ddlPKG.SelectedValue;
                nonqueryCommand.Parameters["@Pallet_Number"].Value = txtNumofPall.Text;
                nonqueryCommand.Parameters["@Pallet_Weight"].Value = txtWgtPerPallet.Text;
                nonqueryCommand.Parameters["@Weight_Options"].Value = ddlWeightOptions.SelectedValue;
                nonqueryCommand.Parameters["@Total"].Value = txtTotal.Text;
    
    
                nonqueryCommand.ExecuteNonQuery();
                MessageBox("Submission Successful.");
                txtDateIn.Text = "";
                ddlCust.ClearSelection();
                ddlMatType.ClearSelection();
                txtItemNum.Text = "";
                txtLotNum.Text = "";
                txtBOLNum.Text = "";
                txtLoc.Text = "";
                txtLoc.Text = "";
                txtQTY.Text = "";
                ddlFac.ClearSelection();
                ddlPKG.ClearSelection();
                txtNumofPall.Text = "";
                txtWgtPerPallet.Text = "";
                ddlWeightOptions.ClearSelection();
                txtTotal.Text = "";
    
    
                    thisConnection.Close();
    
                    UpdateRec();
            }
    
    
    
    
    
            protected void txtWgtPerPallet_TextChanged(object sender, EventArgs e)
            {
                txtTotal.Text = (Convert.ToDecimal(txtNumofPall.Text.ToString()) * Convert.ToDecimal(txtWgtPerPallet.Text.ToString())).ToString();
            }
    }

    Any help would be greatly appreicated.

    Thank you,

    Joshua Sizemore

    Wednesday, August 14, 2013 3:03 PM

Answers

  • Hi JSizemore072711,

    According to your description, do you want to insert data to Shipping table from Shippingpage and update the Receiving table? Then you want show the latest data in Receiving Page?

    Based on your code, I suggest that you can clear the each TextBox in the UpdateRec method instead of in btnInsertData_Click method. Because the value in TextBox which you also need use in UpdateRec method. At last, you can retrieve data from Receiving table in the Receiving Page and show.

    If I miss understood you, please describe your question in more detail.

    Thanks

    Best Regards


    Starain Chen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, August 15, 2013 12:27 PM
    Moderator

All replies

  • Hi JSizemore072711;

    You have posted this question in the wrong forum. You will get better response if you post to the following forum:

    ADO.NET Managed Providers


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Wednesday, August 14, 2013 5:33 PM
  • Hi JSizemore072711,

    According to your description, do you want to insert data to Shipping table from Shippingpage and update the Receiving table? Then you want show the latest data in Receiving Page?

    Based on your code, I suggest that you can clear the each TextBox in the UpdateRec method instead of in btnInsertData_Click method. Because the value in TextBox which you also need use in UpdateRec method. At last, you can retrieve data from Receiving table in the Receiving Page and show.

    If I miss understood you, please describe your question in more detail.

    Thanks

    Best Regards


    Starain Chen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, August 15, 2013 12:27 PM
    Moderator