Setting up a GridView Filter with a BoundField

ssezotman · December 10th, 2014, 04:41 PM

Hi:
I have a GridView that I would likie to filter.
My GridView first line code is:

Code:

   <asp:GridView ID="GridView1" runat="server" ForeColor="Black" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="Round_Num,Customer_ID" DataSourceID="SqlDataSource4" EmptyDataText="There are no data records to display." >

Within the GridView I have several BoundFields, the most âcrucialâ is:

Code:

           <asp:BoundField DataField="Customer_ID" HeaderText="Customer_ID" SortExpression="Customer_ID" />

I would like to filter the data âcoming backâ from the database by this Customer_ID value.

My SqlDataSource initial line is:

Code:

<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ 
         ConnectionStrings:GolfDatabaseConnectionString1 %>"

My SelectComman initially is:

Code:

       SelectCommand="SELECT * FROM RoundOfGolf"

â¦and at this point (without any filtering) I get the entire contents of my table RoundOfGolf, so all is well at this pointas far as connecting to the database and the transfer back to the GridView

To try and filter the returning data, if I change my SelectCommand to (with no other changes), I get:

Code:

         SelectCommand="SELECT * from RoundOfGolf Where [Customer_ID] = @Customer_ID"

My error message is:

Must declare the scalar variable "@Customer_ID".

So now I have added a SelectParameter to my SqlDataSource which looks like:

Code:

<SelectParameters>
            <asp:ControlParameter ControlID="GridView1" Name="Customer_ID" PropertyName="SelectedValue" Type="Int32" />
        </SelectParameters>

Now the code runs, but I get the message that âthere are no data records to display!

Thoughts:
I am able to retrieve data from my database table, so it seems that my âconnectionsâ are good, but as soon as I try and filter that data to show only certain Customer_IDs things go bad.

You thoughts would be appreciated.
Cliff

Imar · December 11th, 2014, 10:35 AM

Did you add a control called Customer_ID to enter the customer ID?

Imar

ssezotman · December 11th, 2014, 03:24 PM

Hi Imar:

i believe that I do have a control named Customer_ID in my DetailView of the page..

Code:

 <td><asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="Round_Num" DataSourceID="SqlDataSource1" DefaultMode="Insert" Height="50px" Width="125px" OnItemInserted="DetailsView1_ItemInserted" OnItemInserting="DetailsView1_ItemInserting" OnItemUpdated="DetailsView1_ItemUpdated" OnItemUpdating="DetailsView1_ItemUpdating">
        <Fields>
            <asp:TemplateField HeaderText="Customer_ID" SortExpression="Customer_ID">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Customer_ID") %>'></asp:TextBox>
                </EditItemTemplate>
                <InsertItemTemplate>
                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Customer_ID") %>'></asp:TextBox>
                </InsertItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label4" runat="server" Text='<%# Bind("Customer_ID") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>

Thanks for looking at this
Cliff

Imar · December 11th, 2014, 04:30 PM

Do you need the DetailsView and bind the GridView to it? If not, you can simply add a TextBox called Customer_ID to filter the GridView (possibly with a Button to rebind the GridView or through an OnChange event on the TextBox). Alternatively, try binding to DetailsView1 instead of Customer_ID; however, that means that the GridView will look at Round_Num instead of the Customer_ID.

If you describe what you want to achieve in more detail, I may be able to supply a better suggestion.

Cheers,

Imar

ssezotman · December 11th, 2014, 05:56 PM

Hi Imar and thanks for taking your time for this.

On an aspx page I have both a GridView (that I was to filter) and a DetailsView.

The DetailsView allows a customer/user to enter a new Round of Golf for on of their players. The GridView shows all previous rounds, if filtered, for that particular customer/user.

My table RoundOfGolf contains All the rounds for all of my customers, but each customer/user has a unique Customer_ID in table RoundOfGolf.

Since I plan on having multiple customer/users log into my site, I only want the currently logged in customer (as denoted by their Customer_ID) to "see" only their players. Right now I am able to show everything in the RoundOfGolf table, but need a way to filter by Customer_ID.

I have several screen captures, but can't see to use this "textbox" to Paste in a png or jpg, but I could send it to a URL?

Hope this helps further explain what I am trying to do.
Cliff

ssezotman · December 11th, 2014, 06:14 PM

Code:

Skip Navigation Links.
Home Web Page
Instruction Page
Add a New Player/View Existing Players and their Slope-Index
Insert New/View All Courses
Pick ONE of the Two Insert Links below:
	View ALL Players (Edit/Delete/Select Rounds) for a Member
	Insert New Round (Edit/Delete/Select) for a Specific Single Member
Contact Us
Skip Navigation LinksGolf Handicap 4.5 Web Page > Pick ONE of the Two Insert Links below: > View ALL Players (Edit/Delete/Select Rounds) for a Member

 	Customer_ID	Round_Num	Player_ID	Player_Name	Score	Course_Name	Course_Rating	Course_Slope	Date_Played	Course_ID	HDiff	Best_Ten	userName
Edit Delete Select	1	2	1	Cliff Sherrill	76	Prescott Couintry Club -White	68.9	121	10/15/2014	9	6.6	 	zotman
Edit Delete Select	1	9	1	Cliff Sherrill	92	Prescott Couintry Club -White	68.9	121	10/15/2014	9	21.6	 	zotman
Edit Delete Select	1	21	1	Cliff Sherrill	94	Prescott Couintry Club -White	68.9	121	10/15/2014	9	23.4	 	zotman
Edit Delete Select	1	23	1	Cliff Sherrill	105	Prescott Couintry Club -White	68.9	121	10/15/2014	9	33.7	 	zotman
Edit Delete Select	1	24	1	Cliff Sherrill	91	Prescott Couintry Club -White	68.9	121	10/15/2014	9	20.6	 	zotman
Edit Delete Select	1	25	1	Cliff Sherrill	96	Prescott Couintry Club -White	68.9	121	10/15/2014	9	25.3	 	zotman
Edit Delete Select	1	26	1	Cliff Sherrill	91	Prescott Couintry Club -White	68.9	121	10/15/2014	9	20.6	 	zotman
Edit Delete Select	1	27	1	Cliff Sherrill	92	Prescott Couintry Club -White	68.9	121	10/15/2014	9	21.6	 	zotman
Edit Delete Select	1	28	1	Cliff Sherrill	99	Prescott Couintry Club -White	68.9	121	10/15/2014	9	28.1	 	zotman
Edit Delete Select	1	29	1	Cliff Sherrill	95	Prescott Couintry Club -White	68.9	121	10/15/2014	9	24.4	 	zotman
Round may be on another Page: 	1	2
Customer_ID	
Player_ID	
Player_Name	
Score	
Course_Name	
Course_Rating	
Course_Slope	
Date_Played	Click here to display calendar
Course_ID	
userName	
Insert  Cancel
	1) Make a selection from the Player Name drop down list.
(Player_ID will be filled in automatically)

2) Enter the 18 hole score for the player.

3) Make a selection from the Course Name drop down list.
(Course_Rating, Course_Slope and Course_ID will be filled in automatically)

4) Make sure the date is correct.
(Click on the calendar icon to choose if not today's date)

5) Click on the "Insert" link to add round for player.

Imar · December 12th, 2014, 03:45 AM

In that case, you need to get the Customer ID from the DetailsView and feed it to the GridView. Since that's not the Key of the DetailsView (the Round_Num is), you need some trickery to get it. This SO article shows some samples: http://stackoverflow.com/questions/2...-a-detailsview

Where to call this code depends on your setup. You could do it the Selecting handler of the SqlDataSource for the GridView. Then grab the value from the DetailsView and assign it to the Parameters collection of the control. Here are some examples:

http://stackoverflow.com/questions/4...-sqldatasource

Cheers,

Imar

Imar · December 12th, 2014, 03:48 AM

Here's another link that could be useful: http://msdn.microsoft.com/en-us/libr...vs.100%29.aspx

Cheers,

Imar

ssezotman · December 13th, 2014, 12:23 PM

Hi Imar:
Thank you for the links. I will pour over them and should be able to come up with something.

A thought:

You mention getting the Customer_ID from the DetailsView and feeding it to the GridView, I may have something to consider.

In the page that has both the GridView I want to filter and the DetailsView to allow the user to enter the data for another round; as a convenience to the user I do "grab" the very Customer_ID that I want to filter the GridView with. I then insert that value into "TextBox1" of the the DetailsView so the user can see the value but does not have to remember or type in the value.

In the Page_Load method of the page I have the following code:

Code:

  //here we put in the values of Customer_ID and the userName
                 var query1 = from Customer in myEntities.Customers
                              where Customer.UserName == User.Identity.Name
                              select Customer.Customer_ID;


                 foreach (var result in query1)
                 {
                     string value = result.ToString();
                     theCustomer = Convert.ToInt32(value);
                     TextBox CustomerID = ((TextBox)DetailsView1.FindControl("TextBox1"));
                     CustomerID.Text = value;
                 }//end foreach

Page_Load may be too late in life cycle of the page to filter the GridView, but it makes me think of something like:

SelectCommand = "Select * from RoundOfGolf where [Customer_ID] = DetailsView.TextBox1.Value;

for my Select Command in the SqlDataSource that is connected to the GridView.

Ideas?
Thanks again for all the help
Cliff

Imar · December 13th, 2014, 07:23 PM

Quote:

but it makes me think of something like:

SelectCommand = "Select * from RoundOfGolf where [Customer_ID] = DetailsView.TextBox1.Value;

Yeah, that's the direction you should take this in. It won't work directly because you have a loop, but if you have a single customer ID, you can assign the GridView a new SelectCommand and then call its DataBind method.

Cheers,

Imar