Check whether Time range exists in database or not using C# and VB.Net in ASP.Net

Last Reply 17 days ago By pandeyism

Posted 18 days ago

Hello Sir,

I'm trying to check whether the given time range is already present into the database or not before insert a new one.

here if the database consist of time range like 11:00pm to 2:am and trying to insert the same then the same values are getting inserted rather displaying an already available message.

Where as for other time range its displaying the message available before insert.

namefrom_dateto_datefrom_timeto_time
abc 01/01/2019 01/15/2019 6:00 AM 10:00 AM
xyz 01/01/2019 01/15/2019 10:00 PM 1:00 AM
abc 01/16/2019 01/30/2019 3:00 PM 5:00 PM
Posted 17 days ago Modified on 10 days ago

Hi democloud,

Take reference to below code and change your code.

HTML

FromDate :
<asp:TextBox ID="txtFromDate" runat="server"></asp:TextBox>
ToDate :
<asp:TextBox runat="server" ID="txtToDate"></asp:TextBox>
<br />
<asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="Insert" />
<br />
<asp:GridView ID="gvDate" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="FROMTime" HeaderText="FROMTime" />
        <asp:BoundField DataField="TOTime" HeaderText="TOTime" />
    </Columns>
</asp:GridView>

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)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        SqlConnection con = new SqlConnection(constr);
        SqlCommand cmd = new SqlCommand("SELECT * FROM TestCheckDate", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        gvDate.DataSource = dt;
        gvDate.DataBind();
    }
}

protected void Insert(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand("SELECT * FROM TestCheckDate WHERE FROMTime = @FROMTime AND TOTime = @ToDate", con);
    cmd.Parameters.AddWithValue("@FROMTime", Convert.ToDateTime(txtFromDate.Text.Trim()).ToShortTimeString());
    cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text.Trim()).ToShortTimeString());
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);

    if (dt.Rows.Count <= 0)
    {
        cmd = new SqlCommand("INSERT INTO DateTable(FromDate,ToDate) VALUES(@FromDate,@ToDate)");
        cmd.Parameters.AddWithValue("@FromDate", txtFromDate.Text);
        cmd.Parameters.AddWithValue("@ToDate", txtToDate.Text);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Recurd Inserted !');", true);
    }
    else
    {
        ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Already Exist Record !');", true);
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim con As SqlConnection = New SqlConnection(constr)
        Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM TestCheckDate", con)
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        gvDate.DataSource = dt
        gvDate.DataBind()
    End If
End Sub

Protected Sub Insert(ByVal sender As Object, ByVal e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim con As SqlConnection = New SqlConnection(constr)
    Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM TestCheckDate WHERE FROMTime = @FROMTime AND TOTime = @ToDate", con)
    cmd.Parameters.AddWithValue("@FROMTime", Convert.ToDateTime(txtFromDate.Text.Trim()).ToShortTimeString())
    cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text.Trim()).ToShortTimeString())
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    Dim dt As DataTable = New DataTable()
    da.Fill(dt)

    If dt.Rows.Count <= 0 Then
        cmd = New SqlCommand("INSERT INTO DateTable(FromDate,ToDate) VALUES(@FromDate,@ToDate)")
        cmd.Parameters.AddWithValue("@FromDate", txtFromDate.Text)
        cmd.Parameters.AddWithValue("@ToDate", txtToDate.Text)
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        ScriptManager.RegisterStartupScript(Me, Me.GetType(), "Message", "alert('Recurd Inserted !');", True)
    Else
        ScriptManager.RegisterStartupScript(Me, Me.GetType(), "Message", "alert('Already Exist Record !');", True)
    End If
End Sub

Screenshot