Update Database with Zero if ASP.Net GridView Column is Null or Empty using C# and VB.Net

Last Reply 5 months ago By pandeyism

Posted 5 months ago
INTIME OUTTIME Hours Days
2018-05-26 09:22 2018-05-26 21:22 12 1
2018-05-26 09:22   0 0
  2018-05-26 21:22 0 0

i want to use if else condition in gridview on update button click when INTIME or OUTTIME does not available in gridview then Hours and Day will be equal to 0 behind update button coding here i tried but not working properly as i want 

        protected void update_Click(object sender, EventArgs e)
        {
            System.Globalization.CultureInfo enUS = new System.Globalization.CultureInfo("en-US");

            foreach (GridViewRow row in this.GVEAtt.Rows)
            {
                var date2 = (row.FindControl("txtINTime") as TextBox).Text;
                var date3 = (row.FindControl("txtOuttime") as TextBox).Text;                
                DateTime INTime;
                if (!string.IsNullOrEmpty(date2))
                {
                    INTime = DateTime.ParseExact(date2, "M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture);
                }
                else
                {
                    INTime = System.DateTime.Now;
                }
                DateTime Outtime;
                if (!string.IsNullOrEmpty(date3))
                {
                    Outtime = DateTime.ParseExact(date3, "M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture);
                }
                else
                {
                    Outtime = System.DateTime.Today;
                }

                var command = new SqlCommand();
                command.CommandText = "UPDATE EmployeeAttedance SET  INTime=@INTime,Duty=@Duty,Outtime=@Outtime,OT=@OT,Days=@Days,Hours=@Hours, OTAmount=@OTAmount ,OTRate=@OTRate,Time=@Time,Late=@Late,LTime=@LTime WHERE AttdID=@AttdID";
                con = new SqlConnection("Data Source=DESKTOP-5PJ76B9;Integrated Security=SSPI;Initial Catalog=AttendanceManagement");
                command.Connection = con;
                command.Parameters["@INTime"].Value = DateTime.Parse(date2);
                command.Parameters["@Outtime"].Value = DateTime.Parse(date3);
                con.Open();
                command.ExecuteNonQuery();
            }
        }

Please guide ...

Posted 5 months ago

Hey akhter,

Please refer below sample.

HTML

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="InTime" HeaderText="InTime" />
        <asp:BoundField DataField="OutTime" HeaderText="OutTime" />
        <asp:TemplateField HeaderText="Hours">
            <ItemTemplate>
                <asp:TextBox ID="txtHoures" runat="server" Text='<%#Eval("Hours") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Days">
            <ItemTemplate>
                <asp:TextBox ID="txtDay" runat="server" Text='<%#Eval("Days") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<asp:Button Text="Update" runat="server" OnClick="Update" />

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM TestDemotest", con))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    gvCustomers.DataSource = dt;
                    gvCustomers.DataBind();
                }
            }
        }
    }
}

protected void Update(object sender, EventArgs e)
{
    foreach (GridViewRow row in gvCustomers.Rows)
    {
        string name = row.Cells[0].Text;
        string inTime = row.Cells[1].Text;
        string OutTime = row.Cells[2].Text;
        TextBox txtHours = row.FindControl("txtHoures") as TextBox;
        TextBox txtDay = row.FindControl("txtDay") as TextBox;
        int hours = 0, day = 0;
        if (inTime != "&nbsp;" && OutTime != "&nbsp;")
        {
            hours = Convert.ToInt32(txtHours.Text);
            day = Convert.ToInt32(txtDay.Text);
        }
        else if (inTime == "&nbsp;" || OutTime == "&nbsp;")
        {
            txtHours.Text = hours.ToString();
            txtDay.Text = day.ToString();
        }
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("Update TestDemotest set Hours = @hours,Days=@days WHERE name = @name", con))
            {
                cmd.Parameters.AddWithValue("@hours", hours);
                cmd.Parameters.AddWithValue("@days", day);
                cmd.Parameters.AddWithValue("@name", name);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

VB.Net

Protected Sub Update(ByVal sender As Object, ByVal e As EventArgs)
    For Each row As GridViewRow In gvCustomers.Rows
        Dim name As String = row.Cells(0).Text
        Dim inTime As String = row.Cells(1).Text
        Dim OutTime As String = row.Cells(2).Text
        Dim txtHours As TextBox = TryCast(row.FindControl("txtHoures"), TextBox)
        Dim txtDay As TextBox = TryCast(row.FindControl("txtDay"), TextBox)
        Dim hours As Integer = 0, day As Integer = 0

        If inTime <> "&nbsp;" AndAlso OutTime <> "&nbsp;" Then
            hours = Convert.ToInt32(txtHours.Text)
            day = Convert.ToInt32(txtDay.Text)
        ElseIf inTime = "&nbsp;" OrElse OutTime = "&nbsp;" Then
            txtHours.Text = hours.ToString()
            txtDay.Text = day.ToString()
        End If

        Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
            Using cmd As SqlCommand = New SqlCommand("Update TestDemotest set Hours = @hours,Days=@days WHERE name = @name", con)
                cmd.Parameters.AddWithValue("@hours", hours)
                cmd.Parameters.AddWithValue("@days", day)
                cmd.Parameters.AddWithValue("@name", name)
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    Next
End Sub

Screenshot

Updated Database Table.

name InTime OutTime Hours Days
Mudassar 5/26/2018 9:22 5/26/2018 21:22 12 1
john 5/26/2018 9:22   0 0
robert   5/26/2018 21:22 0 0