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

Last Reply 27 days ago By chetan

Posted 27 days 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 27 days 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 27 days 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";