CODEWITHSUNDEEP
asp.netsqlvb.netgridviewdatasource
Ahmad
Ahmad

Reputation: 12707

How can I bind a gridview to datasource with dynamic WHERE clause?

I am using Visual Basic for ASP. NET

I know how to populate a gridview with data using only code behind, like this

Dim ds as new datasourse
Dim da as new dataadaptor
Dim con as new SqlConnection
Dim cmd as new SqlCommand
... 
Cmd = "select ticketID, problem_text from problems where support_engineer = " & Session("Logged_in_user_id")
... 
Gridview1.Datasource = ds
Gridview1.Datasources. DataBind

And that works fines, but my question is: how can i drag and drop a gridview control, and using only the wizard to populate the gridview in design time, how can i define the SELECT statement to only select rows that are related to the logged in user id? See the sql statement above, it uses a session variable that i create in Page_Load event, but how can i use the same logic in design mode? (i don't want to modify the datasource control in code behind)

I looked in youtube and google, and this sites, but all results are simply showing me how to populate the gridview with all rows or with a static condition, not a dynamic one like the one i demonestrated.

Any help is highly appreciated

Upvotes: 1

Views: 2132

Answers (2)

Ahmad
Ahmad

Reputation: 12707

I found out the proper way of binding the gridview to a dynamic session variable, without involving code behind:. Simply use the property <SessionParameter> inside the <SelectParameter>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" 

SelectCommand="SELECT ticketID, problem_text FROM problems 
               WHERE (support_engineer = @eng_id) 

<SelectParameters>
         <asp:SessionParameter 
              Name="eng_id" 
              SessionField="LoggedInUser" 
              Type="String" />
</SelectParameters>
</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="True" 
         DataSourceID="SqlDataSource1">
</asp:GridView>

Upvotes: 1

Hans Derks
Hans Derks

Reputation: 1027

You could try replacing the form tag in the aspx file

<form id="form1" runat="server">
<div>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" 
        SelectCommand="SELECT ticketID, problem_text FROM Tabs WHERE (support_engineer = @Param1)">
        <SelectParameters>
            <asp:SessionParameter Name="Param1" SessionField="Logged_in_user_id" />
        </SelectParameters>
    </asp:SqlDataSource>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="True" 
         DataSourceID="SqlDataSource1">
    </asp:GridView>
</div>
</form>

Upvotes: 3

Related Questions