Forums: Back End:

 

VB.NET, SqlDataSources & the SelectCommand Property

first
 

JimmyTheGent 2008.04.09, 07:51AM — VB.NET, SqlDataSources & the SelectCommand Property

Hi Folks.

I am hoping to gain some insight into why what I am trying does now work. Chances are it is a newbie coding error but there may be another reason.

I am creating an aspx page with a sqldatasource and a gridview. When a checkbox is checked, I want the SelectCommand property to change to a different SQl Query so that the gridview shows different information.

The current code does not throw an error but also shows no data. I am looking for ideas why. 


VB CODE



    Public Property Query() As String

        Get

            Return _query

        End Get

        Set(ByVal value As String)

            _query = value

        End Set

    End Property





Protected Sub chk1_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chk1.CheckedChanged



        If chk1.Checked = False Then

            Query() = "SELECT * FROM [Purchases] WHERE ([Dateordered] LIKE '%' + @Dateordered + '%') ORDER BY [Dateordered] DESC"



        Else

            Query() = "SELECT * FROM [Purchases] WHERE (([Dateordered] LIKE '%' + @Dateordered + '%') AND ([Datereceived] = '')) ORDER BY [Id] DESC"



        End If

    End Sub











SQL DataSource on ASPX Page



<asp:SqlDataSource ID="dsSQL1" runat="server" ConnectionString="<%$ ConnectionStringsbig grinSKBConnectionString1 %>" SelectCommand='<%# Query() %>'>

    <SelectParameters>

        <asp:ControlParameter ControlID="ddlmonths" DefaultValue="Jan" Name="Dateordered"

            PropertyName="SelectedValue" Type="String" />

    </SelectParameters>

</asp:SqlDataSource>

quote
 

JimmyTheGent 2008.04.09, 07:52AM —

*should say "Does not work" but I don't have an edit button for main post.

quote
 

jamiec 2008.04.09, 08:57AM —

<%# will only evaluate when DataBind is called - are you calling databind on the dsSQL1? try it within the chk1_CheckChanged event handler.


Protected Sub chk1_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chk1.CheckedChanged
 If chk1.Checked = False Then
    Query() = "SELECT * FROM [Purchases] WHERE ([Dateordered] LIKE '%' + @Dateordered + '%') ORDER BY [Dateordered] DESC"
 Else
    Query() = "SELECT * FROM [Purchases] WHERE (([Dateordered] LIKE '%' + @Dateordered + '%') AND ([Datereceived] = '')) ORDER BY [Id] DESC"
 End If
 dsSQL1.DataBind()
End Sub


Failing that, try having 2 SqlDataSource's - one one for each SQL Query - and swap the DataSourceID of the GridView when the checkbox is checked.

quote
 

JimmyTheGent 2008.04.09, 09:24AM —

I went with the second datasource options and works perfectly. Thanks very much Jamiec! 


    Protected Sub chk1_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chk1.CheckedChanged



        If chk1.Checked = False Then

            gv1.DataSourceID = "dsSQL1"



        Else

            gv1.DataSourceID = "dsSQL2"



        End If

    End Sub

quote
 

jamiec 2008.04.09, 09:53AM —

no problem, can I make 1 suggestion - that you name your objects properly. chk1, ds1, ds2 are fine when you're testing something out but it makes the code harder to understand.

dsReceived and dsOrdered perhaps?
chkSwapQuery ...?

quote
 

JimmyTheGent 2008.04.09, 12:18PM —

I usually do but I when working on things for my own use I tend to be a bit more lazy.
Cheers for the help.

quote
 
first
 

Forums: Back End: VB.NET, SqlDataSources & the SelectCommand Property

 
New Post
 
You must be logged in to post