Insert Update using ASP.Net SqlDataSource control parameters in C#

Last Reply 6 months ago By dharmendr

Posted 6 months ago

 I have a code that i want to update the rows of my gridview.

for update i use sqldatasource control parameters but i  I do not know how to use it correctly.

this is my code:

   <asp:GridView ID="gvEmployees" CssClass="gridStyle" runat="server" Caption="  " DataSourceID="sqlDtSrcEmployees" AutoGenerateColumns="False">
            <AlternatingRowStyle BackColor="#FF99CC" />
            <Columns>
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" SortExpression="EmployeeID" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                <asp:BoundField DataField="HireDate" HeaderText="HireDate" SortExpression="HireDate" />
            </Columns>
            <EditRowStyle BackColor="#FF99CC" />
            <HeaderStyle BackColor="#FF66CC" Font-Bold="True" Font-Names="Calibri" ForeColor="White" />
            <RowStyle BackColor="#FFCCFF" Font-Names="Calibri" HorizontalAlign="Center" />
        </asp:GridView>

 

 

  <asp:SqlDataSource ID="sqlDtSrcEmployees" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString2 %>"
            InsertCommand="INSERT INTO [Employees] ([LastName], [FirstName], [HireDate]) VALUES (@LastName, @FirstName, @HireDate)"
            SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [HireDate] FROM [Employees]"
            OnInserting="sqlDtSrcEmployees_Inserting"
            UpdateCommand="Update Employees SET [LastName]=@LastName, [FirstName]=@FirstName,[HireDate]=@HireDate  WHERE id=@id"
            OnUpdated="sqlDtSrcEmployees_Updated" OnUpdating="sqlDtSrcUpdate_Updating" >
              
            <InsertParameters>
                <asp:ControlParameter ControlID="txtLastName" Name="LastName" PropertyName="Text"
                    Type="String" />
                <asp:ControlParameter ControlID="txtFirstName" Name="FirstName" PropertyName="Text"
                    Type="String" />
                <asp:Parameter Name="HireDate" Type="DateTime" />
            </InsertParameters>

            <UpdateParameters>
                <asp:Parameter Name="id" Type="Int32" />
                <asp:ControlParameter ControlID="txtLastName" Name="LastName" PropertyName="Text"
                    Type="String" />
                <asp:ControlParameter ControlID="txtFirstName" Name="FirstName" PropertyName="Text"
                    Type="String" />
                <asp:Parameter  Name="HireDate" Type="DateTime" />
            </UpdateParameters>
        </asp:SqlDataSource>

 

     protected void sqlDtSrcUpdate_Updating(object sender, SqlDataSourceCommandEventArgs e)
        {
            try
            {
                int day = Convert.ToInt32(TxtHireDay.Text);
                int month = Convert.ToInt32(TxtHireMonth.Text);
                int year = Convert.ToInt32(TxtHireYear.Text);

                System.Globalization.PersianCalendar faDate = new System.Globalization.PersianCalendar();
                e.Command.Parameters["@HireDate"].Value = faDate.ToDateTime(year, month, day, 23, 0, 0, 0);
                e.Command.Parameters["@id"].Value = Int32.Parse( TextBox1.Text);
            }
            catch { }
        }

 

 protected void Button2_Click(object sender, EventArgs e)
        {
            sqlDtSrcEmployees.Update();
        }

How can i do it right؟!!

Posted 6 months ago

Hi dorsa,

Check this example. Now please take its reference and correct your code.

HTML

<asp:GridView ID="gvEmployees" runat="server" Caption="کارمندان موجود در پایگاه داده"
    DataSourceID="sqlDtSrcEmployees" AutoGenerateColumns="False" OnRowDataBound="gvEmployees_RowDataBound">
    <Columns>
        <asp:BoundField DataField="Emp_No" HeaderText="EmployeeID" SortExpression="EmployeeID" />
        <asp:BoundField DataField="Emp_Name" HeaderText="Name" SortExpression="Name" />
        <asp:BoundField DataField="Emp_JoiningDate" HeaderText="HireDate" SortExpression="HireDate" />
    </Columns>
</asp:GridView>
<asp:SqlDataSource ID="sqlDtSrcEmployees" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString2 %>"
    InsertCommand="INSERT INTO [Employee] ([Emp_No],[Emp_Name],[Emp_JoiningDate]) VALUES (@id,@Name, @HireDate)"
    SelectCommand="SELECT Emp_No,Emp_Number,Emp_Name,Emp_JoiningDate,Emp_ResignDate,Emp_Status,Emp_CurrentDesignation,Emp_CurrentGross FROM [Employee]"
    UpdateCommand="Update Employee SET [Emp_Name]=@Name, [Emp_JoiningDate]=@HireDate WHERE Emp_No=@id"
    OnInserting="sqlDtSrcEmployees_Inserting" OnUpdating="sqlDtSrcUpdate_Updating">
    <InsertParameters>
        <asp:Parameter Name="id" Type="Int32" />
        <asp:Parameter Name="HireDate" Type="DateTime" />
        <asp:ControlParameter ControlID="txtName" Name="Name" PropertyName="Text" Type="String" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="id" Type="Int32" />
        <asp:ControlParameter ControlID="txtName" Name="Name" PropertyName="Text" Type="String" />
        <asp:Parameter Name="HireDate" Type="DateTime" />
    </UpdateParameters>
</asp:SqlDataSource>
Id:<asp:TextBox runat="server" ID="TextBox1" />
<br />
Name:<asp:TextBox runat="server" ID="txtName" />
<asp:Button Text="Insert" runat="server" OnClick="Button1_Click1" />
<asp:Button ID="Button1" Text="Update" runat="server" OnClick="Button2_Click" />

C#

protected void gvEmployees_RowDataBound(object sender, GridViewRowEventArgs e)
{
    try
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DateTime usDate = (DateTime)DataBinder.Eval(e.Row.DataItem, "Emp_JoiningDate");
            System.Globalization.PersianCalendar faDate = new System.Globalization.PersianCalendar();
            string day = faDate.GetDayOfMonth(usDate).ToString("D2");
            string month = faDate.GetMonth(usDate).ToString("D2");
            string year = faDate.GetYear(usDate).ToString("D4");
            e.Row.Cells[2].Text = string.Format("{0} / {1} / {2}", year, month, day);
        }
    }
    catch { }
}

protected void sqlDtSrcEmployees_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
    try
    {
        int day = Convert.ToInt32(DateTime.Now.Day);
        int month = Convert.ToInt32(DateTime.Now.Month);
        int year = Convert.ToInt32(DateTime.Now.Year);

        System.Globalization.PersianCalendar faDate = new System.Globalization.PersianCalendar();
        e.Command.Parameters["@HireDate"].Value = faDate.ToDateTime(year, month, day, 23, 0, 0, 0);
        e.Command.Parameters["@id"].Value = Int32.Parse(TextBox1.Text);
    }
    catch { }
}
protected void Button1_Click1(object sender, EventArgs e)
{
    sqlDtSrcEmployees.Insert();
}

protected void sqlDtSrcUpdate_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
    try
    {
        int day = Convert.ToInt32(DateTime.Now.Day);
        int month = Convert.ToInt32(DateTime.Now.Month);
        int year = Convert.ToInt32(DateTime.Now.Year);

        System.Globalization.PersianCalendar faDate = new System.Globalization.PersianCalendar();
        e.Command.Parameters["@HireDate"].Value = faDate.ToDateTime(year, month, day, 23, 0, 0, 0);
        e.Command.Parameters["@id"].Value = Int32.Parse(TextBox1.Text);
    }
    catch { }
}
protected void Button2_Click(object sender, EventArgs e)
{
    sqlDtSrcEmployees.Update();
}

Screenshot