I have a gridview with sqldata source in my aspx+Vb +sql web. There is a date field in my table have on dropdownlist mthtxt.text and another dropdownlist yrtxt.text Table name is Clinic_Med_Issue Empno Name Medicine qty effdate MM001 Baiju Citrizine 02 2021-09-16 20:12:53.000 MM002 Sanath Dolo 650mg 02 2021-09-16 18:12:53.000 MM004 Radha Cough Syrup 02 2021-09-14 20:12:53.000 MM003 Krishna Citrizine 02 2021-09-13 20:12:53.000 MM001 Baiju Dolo 650mg 02 2021-09-17 21:00:00.000 I am using sqldata source for a gridview <asp:SqlDataSource ID="gridsource" runat="server" ConnectionString="<%$ ConnectionStrings:baijuep %>" SelectCommand="SELECT * FROM [Clinic_Med_Issue]"> I wanted Select * from Clinic_Med_Issue where Month(effedate) = mth.text and Year(effdate)=yrtxt.text but its not working in sql data source

SELECT MONTH AND YEAR FROM DATE FIELD FOR SQL DATASOURCE FOR A GRID VIEW

Accepted answer

Lan Huang-MSFT 25,231 Microsoft Vendor

You can provide an example or error message that reproduces the problem.

I wrote an example based on your description, I hope it will help you.

 <div>
            <asp:DropDownList ID="mth" runat="server">
                <asp:ListItem>Select mouth</asp:ListItem>
                <asp:ListItem>1</asp:ListItem>
                <asp:ListItem>2</asp:ListItem>
                <asp:ListItem>9</asp:ListItem>
                <asp:ListItem>8</asp:ListItem>
            </asp:DropDownList>
            <asp:DropDownList ID="yrtxt" runat="server">
                <asp:ListItem>Select year</asp:ListItem>
                <asp:ListItem>2021</asp:ListItem>
                <asp:ListItem>2022</asp:ListItem>
            </asp:DropDownList>
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true">
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DBCS %>"
               ></asp:SqlDataSource>
        </div>

  Protected Sub Button1_Click(sender As Object, e As EventArgs)
        SqlDataSource1.SelectCommand = "SELECT  Empno, Name ,Medicine ,qty ,effdate from Clinic_Med_Issue where Month(effdate)  ='" & mth.SelectedItem.ToString() & "' and Year(effdate)  ='" & yrtxt.SelectedItem.Value.ToString() & "'"
        GridView1.DataSource = SqlDataSource1
        GridView1.DataBind()
    End Sub

DEMO

Best regards,
Lan Huang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Baiju EP 141

Hi Lan Huang

Yes, This will work fine for displaying data into the grid as per selection. I was using somethings like this earlier. when i added export to excel option . it was not working with the

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim con2 As New SqlConnection(Str)
        con2.Open()
        Dim qstr2 As String
        qstr2 = "Select * from Clinic_Med_Issue where  MONTH(effdate)='" + mthtxt.Text + "' and YEAR(effdate)='" + yrtxt.Text + "' ORDER BY EFFDATE DESC"
        Dim sqladp2 As New SqlDataAdapter(qstr2, con2)
        Dim dt2 As New DataSet
        sqladp2.Fill(dt2, "Clinic_Med_Issue")
        GridView1.DataSource = dt2
        GridView1.DataBind()
        con2.Close()
    End Sub

it was working fine for me

but when i added the below code to save the gridview data into excel then it was not working while i am using code behind. when i moved to sqldatasource then it was working fine.


    Protected Sub btnExcel_Click(ByVal sender As Object, ByVal e As ImageClickEventArgs) Handles btnExcel.Click
        Response.ClearContent()
        Response.Buffer = True
        Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "Baiju's Emp Summary.xls"))
        Response.ContentType = "application/ms-excel"
        Dim sw As New StringWriter()
        Dim htw As New HtmlTextWriter(sw)
        GridView1.AllowPaging = False
        GridView1.DataBind()
        'Change the Header Row back to white color
        GridView1.HeaderRow.Style.Add("background-color", "white")
        'Applying stlye to gridview header cells
        For i As Integer = 0 To GridView1.HeaderRow.Cells.Count - 1
            GridView1.HeaderRow.Cells(i).Style.Add("background-color", "white")
        Next
        Dim j As Integer = 1
        'This loop is used to apply stlye to cells based on particular row
        For Each gvrow As GridViewRow In GridView1.Rows
            GridView1.BackColor = Color.White
            If j <= GridView1.Rows.Count Then
                If j Mod 2 <> 0 Then
                    For k As Integer = 0 To gvrow.Cells.Count - 1
                        gvrow.Cells(k).Style.Add("background-color", "white")
                    Next
                End If
            End If
            j += 1
        Next
        GridView1.RenderControl(htw)
        Response.Write(sw.ToString())
        Response.[End]()
    End Sub

I am something in the export to excel code

with your code also i am not getting the gridview data into excel. its blank as shown below.

Screenshot 2023-02-18 124106

at the same time i can see the data in gridview.

pleas help to fix this iusse.

Baiju EP 141

Thanks for your prompt reply. Mr Lan Huang

I was using some code behind similar to your code. But when i added the export to excel code then i was not getting the data into excel even though there was data displayed in excel.

I used this code to export to excel


    Protected Sub btnExcel_Click(ByVal sender As Object, ByVal e As ImageClickEventArgs) Handles btnExcel.Click
        Response.ClearContent()
        Response.Buffer = True
        Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "Baiju's Emp Summary.xls"))
        Response.ContentType = "application/ms-excel"
        Dim sw As New StringWriter()
        Dim htw As New HtmlTextWriter(sw)
        GridView1.AllowPaging = False
        GridView1.DataBind()
        'Change the Header Row back to white color
        GridView1.HeaderRow.Style.Add("background-color", "white")
        'Applying stlye to gridview header cells
        For i As Integer = 0 To GridView1.HeaderRow.Cells.Count - 1
            GridView1.HeaderRow.Cells(i).Style.Add("background-color", "white")
        Next
        Dim j As Integer = 1
        'This loop is used to apply stlye to cells based on particular row
        For Each gvrow As GridViewRow In GridView1.Rows
            GridView1.BackColor = Color.White
            If j <= GridView1.Rows.Count Then
                If j Mod 2 <> 0 Then
                    For k As Integer = 0 To gvrow.Cells.Count - 1
                        gvrow.Cells(k).Style.Add("background-color", "white")
                    Next
                End If
            End If
            j += 1
        Next
        GridView1.RenderControl(htw)
        Response.Write(sw.ToString())
        Response.[End]()
    End Sub

with the above code i am not getting the data from grid to excel that i why I switched to sqldatasource in aspx page

Screenshot 2023-02-18 124106

Lan Huang-MSFT 25,231 Microsoft Vendor

Hi @Baiju EP ,

Your question doesn't mention that you need to convert to excel.

I think the reason it doesn't work is because the code is incorrect. I added the function of converting excel to my original code, you can refer to it.

 Protected Sub Button1_Click(sender As Object, e As EventArgs)
        If mth.SelectedItem.Text <> "Select ALLmouth" AndAlso yrtxt.SelectedItem.Text <> "Select ALLyear" Then
            SqlDataSource1.SelectCommand = "SELECT  Empno, Name ,Medicine ,qty ,effdate from Clinic_Med_Issue where Month(effdate) = '" & mth.SelectedItem.ToString() & "' and Year(effdate)='" + yrtxt.SelectedItem.ToString() & "'"
        ElseIf yrtxt.SelectedItem.Text <> "Select ALLyear" Then
            SqlDataSource1.SelectCommand = "SELECT  Empno, Name ,Medicine ,qty ,effdate from Clinic_Med_Issue where  Year(effdate)='" & yrtxt.SelectedItem.ToString() & "'"
        ElseIf mth.SelectedItem.Text <> "Select ALLmouth" Then
            SqlDataSource1.SelectCommand = "SELECT  Empno, Name ,Medicine ,qty ,effdate from Clinic_Med_Issue where Month(effdate) = '" & mth.SelectedItem.ToString() & "'"
        Else
            SqlDataSource1.SelectCommand = "SELECT  Empno, Name ,Medicine ,qty ,effdate from Clinic_Med_Issue"
        End If
        GridView1.DataSource = SqlDataSource1
        GridView1.DataBind()
    End Sub
    Protected Sub btnExcel_Click(sender As Object, e As EventArgs)
        Response.Clear()
        Response.Buffer = True
        Response.ClearContent()
        Response.ClearHeaders()
        Response.Charset = ""
        Dim strwritter As StringWriter = New StringWriter()
        Dim htmltextwrtter As HtmlTextWriter = New HtmlTextWriter(strwritter)
        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Response.ContentType = "application/ms-excel"
        Response.AddHeader("content-Disposition", String.Format("attachment; filename={0}", "Baiju's Emp Summary.xls"))
        GridView1.GridLines = GridLines.Both
        GridView1.HeaderStyle.Font.Bold = True
        GridView1.RenderControl(htmltextwrtter)
        Response.Write(strwritter.ToString())
        Response.[End]()
    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
        'Confirms that an HtmlForm control Is rendered for the specified ASP.NET
        'Server control at run time. 
    End Sub

 <div>
            <asp:DropDownList ID="mth" runat="server">
                <asp:ListItem>Select ALLmouth</asp:ListItem>
                <asp:ListItem>1</asp:ListItem>
                <asp:ListItem>2</asp:ListItem>
                <asp:ListItem>9</asp:ListItem>
                <asp:ListItem>8</asp:ListItem>
            </asp:DropDownList>
            <asp:DropDownList ID="yrtxt" runat="server">
                <asp:ListItem>Select ALLyear</asp:ListItem>
                <asp:ListItem>2021</asp:ListItem>
                <asp:ListItem>2022</asp:ListItem>
            </asp:DropDownList>
            <asp:Button ID="Button1" runat="server" Text="Select" OnClick="Button1_Click" />      
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true">
            </asp:GridView>
              <asp:Button ID="btnExcel" runat="server" Text="btnExcel" OnClick="btnExcel_Click" />
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DBCS %>"
               ></asp:SqlDataSource>
        </div>

Best regards,
Lan Huang

SELECT MONTH AND YEAR FROM DATE FIELD FOR SQL DATASOURCE FOR A GRID VIEW

0 additional answers