Thursday, 29 March 2012

Inserting and deleting and update records in MSSQL Server database using .net C#

Check duplicate before Inserting record:
SqlConnection scon = new SqlConnection("Data Source=FAISALN;Initial Catalog=Shipping;Integrated Security=True");
        scon.Open();
        SqlCommand scmd = new SqlCommand("select PO from PO_Table where PO=@PO " , scon);
        scmd.Parameters.Add("PO", SqlDbType.Int).Value = TextBox1.Text;
        scmd.ExecuteScalar();

        if (scmd.ExecuteScalar()== null)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da.InsertCommand = new SqlCommand("insert into PO_Table values (@PO, @Style_Ref, @Price, @Season, @Order_Qty, @PO_Ship_Date, @PO_Shipping_Month, @PO_Shipping_Mode, @PO_Value, @PO_Destination, @Vendor, @Buyer)", scon);
            da.InsertCommand.Parameters.Add("@PO", SqlDbType.Int).Value = TextBox1.Text;
            da.InsertCommand.Parameters.Add("@Style_Ref", SqlDbType.NVarChar).Value = TextBox2.Text;
            da.InsertCommand.Parameters.Add("@PO_Destination", SqlDbType.NVarChar).Value = DropDownList3.Text;
            da.InsertCommand.Parameters.Add("@Price", SqlDbType.Float).Value = TextBox3.Text;
            da.InsertCommand.Parameters.Add("@Season", SqlDbType.NVarChar).Value = DropDownList2.Text;
            da.InsertCommand.Parameters.Add("@Order_Qty", SqlDbType.Int).Value = TextBox4.Text;
            da.InsertCommand.Parameters.Add("@PO_Ship_Date", SqlDbType.DateTime).Value = TextBox5.Text;
            da.InsertCommand.Parameters.Add("@PO_Shipping_Month", SqlDbType.Char).Value = TextBox6.Text;
            da.InsertCommand.Parameters.Add("@PO_Shipping_Mode", SqlDbType.Char).Value = TextBox7.Text;
            da.InsertCommand.Parameters.Add("@PO_Value", SqlDbType.Int).Value = TextBox8.Text;
            da.InsertCommand.Parameters.Add("@Vendor", SqlDbType.NVarChar).Value = DropDownList5.Text;
            da.InsertCommand.Parameters.Add("@Buyer", SqlDbType.NVarChar).Value = DropDownList4.Text;
            da.InsertCommand.ExecuteNonQuery();
            scon.Close();
            TextBox1.Text = "";
            TextBox2.Text = "";
            TextBox3.Text = "";
            TextBox4.Text = "";
            TextBox5.Text = "";
            TextBox6.Text = "";
            TextBox7.Text = "";
            TextBox8.Text = "";
            string strScript = "<script language='JavaScript'>alert('PO Enter Successfully')</script>";
            Page.RegisterStartupScript("PopUp", strScript);    
           
        }  
        else
        {
            string strScript = "<script language='JavaScript'>alert('PO Already Exist')</script>";
            Page.RegisterStartupScript("PopUp", strScript);         
           
           
        }      
            scon.Close();     


Clear fields after insterting records:
 TextBox1.Text = "";
        TextBox2.Text = "";
        TextBox3.Text = "";
        TextBox4.Text = "";
        TextBox5.Text = "";
        TextBox6.Text = "";
        TextBox7.Text = "";
        TextBox8.Text = "";


Data Search and fill the fields:
SqlConnection scon = new SqlConnection("Data Source=FAISALN;Initial Catalog=Shipping;Integrated Security=True");
        scon.Open();
        SqlCommand scmd = new SqlCommand("select PO from PO_Table where PO=@PO ", scon);
        scmd.Parameters.Add("PO", SqlDbType.Int).Value = TextBox1.Text;
        scmd.ExecuteScalar();
       
        if (scmd.ExecuteScalar() == null)
       
        {
            string strScript = "<script language='JavaScript'>alert('PO Not Found, Please Re-enter Your PO No')</script>";
            Page.RegisterStartupScript("PopUp", strScript);
            scon.Close();
       }
           
        else
        {
            SqlConnection scon1 = new SqlConnection("Data Source=FAISALN;Initial Catalog=Shipping;Integrated Security=True");
            SqlCommand scmd1 = new SqlCommand("select * from PO_Table where PO=@PO", scon1);
            scmd1.Parameters.Add("PO", SqlDbType.Int).Value = TextBox1.Text;
            SqlDataAdapter sda = new SqlDataAdapter(scmd1);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            TextBox1.Text = ds.Tables[0].Rows[0][0].ToString();
            TextBox2.Text = ds.Tables[0].Rows[0][1].ToString();
            TextBox3.Text = ds.Tables[0].Rows[0][2].ToString();
            TextBox4.Text = ds.Tables[0].Rows[0][4].ToString();
            TextBox5.Text = ds.Tables[0].Rows[0][5].ToString();
            TextBox6.Text = ds.Tables[0].Rows[0][6].ToString();
            TextBox7.Text = ds.Tables[0].Rows[0][7].ToString();
            TextBox8.Text = ds.Tables[0].Rows[0][8].ToString();
            DropDownList2.Text = ds.Tables[0].Rows[0][3].ToString();
            DropDownList3.Text = ds.Tables[0].Rows[0][9].ToString();
            DropDownList5.Text = ds.Tables[0].Rows[0][10].ToString();
            DropDownList4.Text = ds.Tables[0].Rows[0][11].ToString();
            scon1.Close();
                      
Update Data with aleart:
SqlConnection use = new SqlConnection(" Data Source=FAISALN;Initial Catalog=Shipping;Integrated Security=True ");
        SqlDataAdapter da = new SqlDataAdapter();
        da.UpdateCommand = new SqlCommand("update PO_Table set PO=@PO, Vendor=@Vendor, Buyer=@Buyer, Style_Ref=@Style_Ref, Price=@Price, Season=@Season, Order_Qty=@Order_Qty, PO_Ship_Date=@PO_Ship_Date, PO_Shipping_Month=@PO_Shipping_Month, PO_Shipping_Mode=@PO_Shipping_Mode, PO_Value=@PO_Value, PO_Destination=@PO_Destination where PO=@PO", use);
        da.UpdateCommand.Parameters.Add("@PO", SqlDbType.Int).Value = TextBox1.Text;
        da.UpdateCommand.Parameters.Add("@Style_Ref", SqlDbType.NVarChar).Value = TextBox2.Text;
        da.UpdateCommand.Parameters.Add("@PO_Destination", SqlDbType.NVarChar).Value = DropDownList3.Text;
        da.UpdateCommand.Parameters.Add("@Price", SqlDbType.Money).Value = TextBox3.Text;
        da.UpdateCommand.Parameters.Add("@Season", SqlDbType.NVarChar).Value = DropDownList2.Text;
        da.UpdateCommand.Parameters.Add("@Order_Qty", SqlDbType.Int).Value = TextBox4.Text;
        da.UpdateCommand.Parameters.Add("@PO_Ship_Date", SqlDbType.DateTime).Value = TextBox5.Text;
        da.UpdateCommand.Parameters.Add("@PO_Shipping_Month", SqlDbType.Char).Value = TextBox6.Text;
        da.UpdateCommand.Parameters.Add("@PO_Shipping_Mode", SqlDbType.Char).Value = TextBox7.Text;
        da.UpdateCommand.Parameters.Add("@PO_Value", SqlDbType.Int).Value = TextBox8.Text;
        da.UpdateCommand.Parameters.Add("@Vendor", SqlDbType.NVarChar).Value = DropDownList5.Text;
        da.UpdateCommand.Parameters.Add("@Buyer", SqlDbType.NVarChar).Value = DropDownList4.Text;
        use.Open();
        da.UpdateCommand.ExecuteNonQuery();
        use.Close();
        TextBox1.Text = "";
        TextBox2.Text = "";
        TextBox3.Text = "";
        TextBox4.Text = "";
        TextBox5.Text = "";
        TextBox6.Text = "";
        TextBox7.Text = "";
        TextBox8.Text = "";
        string strScript = "<script language='JavaScript'>alert('Record Update')</script>";
        Page.RegisterStartupScript("PopUp", strScript);
Delete Record:

 SqlConnection use = new SqlConnection(" Data Source=FAISALN;Initial Catalog=Shipping;Integrated Security=True ");
        SqlDataAdapter da = new SqlDataAdapter();
        da.DeleteCommand = new SqlCommand("delete from PO_Table where PO=@PO", use);
        da.DeleteCommand.Parameters.Add("@PO", SqlDbType.Int).Value = TextBox1.Text;
        da.DeleteCommand.Parameters.Add("@Style_Ref", SqlDbType.NVarChar).Value = TextBox2.Text;
        da.DeleteCommand.Parameters.Add("@PO_Destination", SqlDbType.NVarChar).Value = DropDownList3.Text;
        da.DeleteCommand.Parameters.Add("@Price", SqlDbType.Money).Value = TextBox3.Text;
        da.DeleteCommand.Parameters.Add("@Season", SqlDbType.NVarChar).Value = DropDownList2.Text;
        da.DeleteCommand.Parameters.Add("@Order_Qty", SqlDbType.Int).Value = TextBox4.Text;
        da.DeleteCommand.Parameters.Add("@PO_Ship_Date", SqlDbType.DateTime).Value = TextBox5.Text;
        da.DeleteCommand.Parameters.Add("@PO_Shipping_Month", SqlDbType.Char).Value = TextBox6.Text;
        da.DeleteCommand.Parameters.Add("@PO_Shipping_Mode", SqlDbType.Char).Value = TextBox7.Text;
        da.DeleteCommand.Parameters.Add("@PO_Value", SqlDbType.Int).Value = TextBox8.Text;
        da.DeleteCommand.Parameters.Add("@Vendor", SqlDbType.NVarChar).Value = DropDownList5.Text;
        da.DeleteCommand.Parameters.Add("@Buyer", SqlDbType.NVarChar).Value = DropDownList4.Text;
        use.Open();
        da.DeleteCommand.ExecuteNonQuery();
        use.Close();
        TextBox1.Text = "";
        TextBox2.Text = "";
        TextBox3.Text = "";
        TextBox4.Text = "";
        TextBox5.Text = "";
        TextBox6.Text = "";
        TextBox7.Text = "";
        TextBox8.Text = "";
Required Feild Validation:
<asp:RequiredFieldValidator runat="server" id="RFV" ControlToValidate="TextBox1" ErrorMessage="PO No Is Required" display="Static">
        </asp:RequiredFieldValidator>
Record Delete Alert

<asp:Button ID="Button5" Text="Delete" runat="server" Width="100px" onclick="Button5_Click" onclientclick="return confirm ('Are You Sure You Want To Delete This PO')" /> 

No comments:

Post a Comment