Display record greater than or equal to current date in ASP.Net DataList using C# and VB.Net

Last Reply 2 months ago By chetan

Posted 2 months ago

i wanted to display only upcoming events like 2019 events or for example say event from 29th december to 31st december2018.

i dont wanted to display the events which is already passed like from 21st december to 23rd december.Date is already passed.

i have the code as follows: 

    protected void showexhilist()
    {
        MySqlConnection conn = null;
        try
        {
            string connStr = ConfigurationManager.ConnectionStrings["ogpeConnectionString"].ToString();
            conn = new MySqlConnection(connStr);
            conn.Open();
            string sqlnews = null;
            sqlnews = "SELECT * FROM ogpe_exhibitions  where status=1 order by start_date desc";
            MySqlCommand cmd = new MySqlCommand(sqlnews, conn);
            MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            DataTable dt = new DataTable();
            dt.Load(dr);
            exiblist.DataSource = dt;
            exiblist.DataBind();
            conn.Close();
        }

        catch (Exception ex)
        {

        }
        finally
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }

how to achieve it?

http://ogpeafrica.com/Exhibitions.aspx?pageid=1

2018 events dates which is already over should not be shown.What changes are required in query?

any help will be apprecoiated.

Posted 2 months ago

Hey chetan,

Please refer below sample.

HTML

<asp:DataList ID="DataList1" runat="server" RepeatDirection="Horizontal" RepeatColumns="1">
    <ItemTemplate>
        <div style="border: 2px solid #ccc;">
            <b>EventId:</b>
            <asp:Label ID="Label1" Text='<%# Eval("EventId") %>' runat="server" />
            <br />
            <b>Event Name:</b>
            <asp:Label ID="Label2" Text='<%# Eval("EventName") %>' runat="server" />
            <br />
            <b>Upcoming Event Date:</b>
            <asp:Label ID="Label3" Text='<%#Eval("EventDate","{0:dd/MM/yyyy}") %>' runat="server" />
            <br />
        </div>
    </ItemTemplate>
</asp:DataList>

Namespaces

C#

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

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindListView();
    }
}
private void BindListView()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "SELECT EventId, EventName, EventDate FROM EventTest WHERE EventDate > = @EventDate";
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@EventDate", DateTime.Now.ToString("yyy-MM-dd"));
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                DataList1.DataSource = dt;
                DataList1.DataBind();
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindListView()
    End If
End Sub

Private Sub BindListView()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand()
            cmd.CommandText = "SELECT EventId, EventName, EventDate FROM EventTest WHERE EventDate > = @EventDate"
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@EventDate", DateTime.Now.AddYears(-60))
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                DataList1.DataSource = dt
                DataList1.DataBind()
            End Using
        End Using
    End Using
End Sub

Screenshot


Posted 2 months ago

Hi Sir,

i have used the following query and it worked.

sqlnews = "SELECT * FROM ogpe_exhibitions  where status=1 and DATE(start_date) > DATE(NOW()) order by start_date";