Hello Sir,
I'm trying to update a table based on dropdownlist selection in gridview, but the update queery is not working,
its just showing the sweetalert for successfull but the data is not update in DB
Below is the code,
Please help
Thanks
protected void ddlCountries_SelectedIndexChanged(object sender, EventArgs e)
{
DropDownList dropDownList = sender as DropDownList;
GridViewRow row = dropDownList.NamingContainer as GridViewRow;
Label date = (Label)row.FindControl("date");
Label fromTime = (Label)row.FindControl("fromtime");
Label toTime = (Label)row.FindControl("totime");
lblDate.Text = date.Text;
lblTime.Text = fromTime.Text + " " + toTime.Text;
try
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["crm_db"].ConnectionString);
string query = "select * from staff_table where staff_name = '" + dropDownList.SelectedItem.Text + "' and rowstate=" + 1 + " ";
SqlDataAdapter da = new SqlDataAdapter(query, con);
DataTable dt = new DataTable();
string key = "";
da.Fill(dt);
if (dt.Rows.Count > 0)
{
int i = 1;
foreach (DataRow dr in dt.Rows)
{
key = "SweetAlert" + i.ToString();
date_db = Convert.ToDateTime(dr[19].ToString());
fromdate1 = date_db.ToShortDateString();
fromtime = dr[5].ToString();
totime = dr[6].ToString();
ft = Convert.ToDateTime(dr[5].ToString()).TimeOfDay;
et = (Convert.ToDateTime(dr[6].ToString())).TimeOfDay;
pre1 = (Convert.ToDateTime(dr[5].ToString()).AddHours(-1)).TimeOfDay;
post1 = (Convert.ToDateTime(dr[6].ToString()).AddHours(1)).TimeOfDay;
nft = (Convert.ToDateTime(fromTime.Text)).TimeOfDay;
net = (Convert.ToDateTime(toTime.Text)).TimeOfDay;
if (date_db != Convert.ToDateTime(date.Text))
{
if (nft <= pre1 && net <= pre1)
{
//ScriptManager.RegisterStartupScript(this, GetType(), key, "swal('Warning!','Staff is available','warning');", true);
SqlCommand update_date = new SqlCommand("update staff_table set rowstate='1' , staff_name='" + dropDownList.SelectedItem.Text + "' where rowstate='0' and date like '" + Convert.ToDateTime(date.Text) + "'", con);
con.Open();
update_date.ExecuteNonQuery();
con.Close();
ScriptManager.RegisterStartupScript(this, GetType(), key, "swal('Success!','New Alignment is done successfully','success');", true);
}
}
else
{
ScriptManager.RegisterStartupScript(this, GetType(), key, "swal('Warning!','Staff " + dropDownList.SelectedItem.Text + " " + fromTime.Text + " to " + toTime.Text + " is already assigned','warning');", true);
}
i++;
}
}
else
{
// ScriptManager.RegisterStartupScript(this, this.GetType(), "SweetAlert", "swal('Warning!', 'No data!', 'warning');", true);
SqlCommand update_date = new SqlCommand("update staff_table set rowstate='1' , staff_name='" + dropDownList.SelectedItem.Text + "' where rowstate='0' and date like '" + Convert.ToDateTime(date.Text) + "'", con);
con.Open();
update_date.ExecuteNonQuery();
con.Close();
ScriptManager.RegisterStartupScript(this, GetType(), key, "swal('Success!','New Alignment is done successfully','success');", true);
}
}
finally
{
con.Close();
con.Dispose();
}
}
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="OnRowDataBound"
EmptyDataText="No Record Found" class="table table-bordered table-striped table-hover"
OnPageIndexChanging="OnPaging" AllowPaging="True" Width="1024px" CellPadding="3"
BackColor="White" DataKeyNames="alignmentId" BorderColor="#CCCCCC" BorderStyle="None"
RowStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" BorderWidth="1px"
PagerSettings-Mode="NumericFirstLast" PageSize="20">
<Columns>
<asp:TemplateField HeaderText="Staff Name" ItemStyle-Width="120">
<ItemTemplate>
<asp:Label ID="lblname" runat="server" Text='<%# Eval("staff_name") %>' CssClass="form-control"
Visible="false" />
<asp:DropDownList ID="alignstaff" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlCountries_SelectedIndexChanged">
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date" ItemStyle-HorizontalAlign="Left" ItemStyle-Width="140px">
<ItemTemplate>
<asp:Label Text='<%# Eval("date" ,"{0:dd/MM/yyyy}" )%>' runat="server" ID="date" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Time" ItemStyle-HorizontalAlign="Left" ItemStyle-Width="140px">
<ItemTemplate>
<asp:Label Text='<%# Eval("from_time").ToString() == "00:00:00.0000000" ? "" : Eval("from_time") %>'
runat="server" ID="fromtime" />
<asp:Label Text='<%# Eval("to_time").ToString() == "00:00:00.0000000" ? "" : Eval("to_time") %>'
runat="server" ID="totime" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<div class="row">
<div class="form-group col-md-4">
<label>
Select Staff Name</label>
<asp:DropDownList ID="DropDownList1" class="chzn-select form-control" runat="server">
</asp:DropDownList>
</div>
<div class="col-md-4">
<div class="form-group ">
<label>
Date
</label>
<asp:TextBox ID="txt_fromdate" class="form-control Datepicker" placeholder="Enter Date"
runat="server"></asp:TextBox>
<ajax:calendarextender id="CalendarExtender1" runat="server" targetcontrolid="txt_fromdate"
cssclass="" format="dd/MM/yyyy" enabled="True" popupbuttonid="txt_fromdate" />
</div>
<asp:Button ID="btn_generate" runat="server" CssClass="btn btn-danger btn-wd" Text="Search"
OnClick="btn_generate_Click" />
</div>
