Results 1 to 10 of 10

Thread: Using a Filter Expression in an SqlDataSource for an Integer Value

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2001
    Posts
    53

    Using a Filter Expression in an SqlDataSource for an Integer Value

    I have a gridview bound to a sqldatasource with a select command that selects from a table. When a user selects a row in that grid, my formview control should populate with that row's info. My formview is bound to another sqldatasource which has the same select command as the first sqldatasource but it has a filterexpression based on the value of a column from the selected row. The problem is that the column that I'm using the filterexpression on is an int32 (the column in the db is an identity seed) so as soon as I select the row from the gridview, I get the following error: "Cannot perform '=' operation on System.Int32 and System.String." Ok, I get that. It's looking for a string value and I'm giving it an Int32. So how do I fix this? I was able to capture the value on the GridView1_SelectedIndexChanged event (SqlDataSource2.filterexpression="c_entry='" & GridView1.SelectedRow.Cells(8).Text & "'". But I'm guessing this is a fairly common issue so there's got to be a way to get it to work without capturing the event and feeding it the value. So what's the secret?

  2. #2
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    Re: Using a Filter Expression in an SqlDataSource for an Integer Value

    My formview is bound to another sqldatasource which has the same select command as the first sqldatasource but it has a filterexpression based on the value of a column from the selected row.
    Are you specifying parameters for your sql command, or just appending a bunch of data?

    Here's a simple example
    vb Code:
    1. 'bad
    2. sqlCommand.CommandText = "Select somedata from mytable where id = " & id
    3. 'good
    4. sqlCommand.CommandText = "Select somedata from mytable where id = @id"
    5. sqlCommand.Parameters.Add("@id", id)

  3. #3

    Thread Starter
    Member
    Join Date
    Aug 2001
    Posts
    53

    Re: Using a Filter Expression in an SqlDataSource for an Integer Value

    I'm using a sqldatasource control and it's properties are set to:
    sqldatasource1.selectcommand="select * from mytable"
    sqldatasource1.filterexpression="id='@id'"

    I got this from a microsoft sample program. Is there a better way?

  4. #4
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,173

    Re: Using a Filter Expression in an SqlDataSource for an Integer Value

    My formview is bound to another sqldatasource which has the same select command as the first sqldatasource but it has a filterexpression based on the value of a column from the selected row.
    Show your relevant ASPX/HTML code for this.

  5. #5

    Thread Starter
    Member
    Join Date
    Aug 2001
    Posts
    53

    Re: Using a Filter Expression in an SqlDataSource for an Integer Value

    <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Communication.aspx.vb" Inherits="Communication" title="Untitled Page" Theme="SmokeAndGlass" %>
    <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    &nbsp; &nbsp;&nbsp;
    <asp:Label ID="Label1" runat="server" Style="z-index: 100; left: 0px; position: absolute;
    top: 0px" Text="Customer Name:"></asp:Label>
    <aspropDownList ID="CustomerDropDown" runat="server" AutoPostBack="True" Style="z-index: 101;
    left: 112px; position: absolute; top: 0px">
    </aspropDownList>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CommunicationConnectionString %>"
    SelectCommand="SELECT [c_type] as [Type],[c_contact_name] as [Customer Contact], [c_employee_name] as [Employee],[c_info] as [Info],[c_Programs] as [Programs Given], [c_Issue_Num] as [Issue #], [c_date] as [Date], [c_entry], [c_cust_id] FROM [Communication] WHERE ([c_cust_id] = @c_cust_id)">
    <SelectParameters>
    <asp:ControlParameter ControlID="CustomerDropDown" Name="c_cust_id" PropertyName="SelectedValue"
    Type="Int32" />
    </SelectParameters>
    </asp:SqlDataSource>
    <table style="z-index: 100; left: 16px; width: 552px; position: relative; top: 48px;
    height: 832px">
    <tr>
    <td style="width: 89px; height: 4px">
    &nbsp;
    <asp:FormView ID="FormView1" runat="server" Style="z-index: 100; left: 16px; position: relative;
    top: 16px" DataSourceID="CommunicationDetails" Height="200px" Width="520px">
    <ItemTemplate>
    <asp:TextBox ID="TextBox1" runat="server" Style="z-index: 100; left: 64px; position: absolute;
    top: 136px" Text='<%# Bind("[c_info]") %>' Height="40px" TextMode="MultiLine" Width="432px"></asp:TextBox>
    <asp:Label ID="Label2" runat="server" Style="z-index: 101; left: 8px; position: absolute;
    top: 136px" Text="Info:"></asp:Label>
    &nbsp;
    <asp:TextBox ID="IssueNumTextBox" runat="server" Style="z-index: 102; left: 64px;
    position: absolute; top: 72px" Text='<%# Bind("[c_Issue_Num]") %>'></asp:TextBox>
    <asp:Label ID="Label3" runat="server" Style="z-index: 103; left: 8px; position: absolute;
    top: 72px" Text="Issue #:"></asp:Label>
    &nbsp;
    <asp:Label ID="Label4" runat="server" Style="z-index: 105; left: 8px; position: absolute;
    top: 104px" Text="Contact:"></asp:Label>
    <asp:TextBox ID="ContactTextBox" runat="server" Style="z-index: 107; left: 64px;
    position: absolute; top: 104px" Text='<%# Bind("[c_contact_name]") %>'></asp:TextBox>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="TextBox1" runat="server" Height="40px" Style="z-index: 100; left: 72px;
    position: absolute; top: 136px" Text='<%# Bind("[c_info]") %>' TextMode="MultiLine"
    Width="432px"></asp:TextBox>
    <asp:Label ID="Label2" runat="server" Style="z-index: 101; left: 8px; position: absolute;
    top: 144px" Text="Info:"></asp:Label>
    <asp:TextBox ID="IssueNumTextBox" runat="server" Style="z-index: 102; left: 72px;
    position: absolute; top: 64px" Text='<%# Bind("[c_Issue_Num]") %>'></asp:TextBox>
    <asp:Label ID="Label3" runat="server" Style="z-index: 103; left: 8px; position: absolute;
    top: 64px" Text="Issue #:"></asp:Label>
    <asp:Label ID="Label4" runat="server" Style="z-index: 104; left: 8px; position: absolute;
    top: 96px" Text="Contact:"></asp:Label>
    <aspropDownList ID="DropDownList1" runat="server" SelectedValue='<%# Bind("[c_Contact_Name]") %>'
    Style="z-index: 106; left: 72px; position: absolute; top: 96px" Width="152px">
    </aspropDownList>
    </EditItemTemplate>
    </asp:FormView>
    &nbsp;
    </td>
    </tr>
    <tr>
    <td style="width: 89px; height: 57px;">
    &nbsp;&nbsp;
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
    AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333"
    GridLines="None" Width="1px" style="z-index: 100; left: 24px; position: relative; top: 0px" DataKeyNames="c_cust_id" AutoGenerateSelectButton="True" Height="608px">
    <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
    <RowStyle BackColor="#E3EAEB" />
    <EditRowStyle BackColor="#7C6F57" />
    <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
    <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
    <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
    <AlternatingRowStyle BackColor="White" />
    <Columns>
    <asp:BoundField DataField="Type" HeaderText="Type" SortExpression="Type" />
    <asp:BoundField DataField="Customer Contact" HeaderText="Customer Contact" SortExpression="Customer Contact" />
    <asp:BoundField DataField="Employee" HeaderText="Employee" SortExpression="Employee" />
    <asp:BoundField DataField="Info" HeaderText="Info" SortExpression="Info">
    <ItemStyle Width="1000px" />
    </asp:BoundField>
    <asp:BoundField DataField="Programs Given" HeaderText="Programs Given" SortExpression="Programs Given" />
    <asp:BoundField DataField="Issue #" HeaderText="Issue #" SortExpression="Issue #" />
    <asp:BoundField DataField="Date" HeaderText="Date" SortExpression="Date" />
    <asp:BoundField DataField="c_entry" HeaderText="c_entry" InsertVisible="False" SortExpression="c_entry" />
    <asp:BoundField DataField="c_cust_id" HeaderText="c_cust_id" SortExpression="c_cust_id"
    Visible="False" />
    </Columns>
    </asp:GridView>
    </td>
    </tr>
    </table>
    <asp:sqldatasource runat="server" id="CommunicationDetails"
    connectionstring="<%$ ConnectionStrings:CommunicationConnectionString %>"
    selectcommand="SELECT * FROM communication" >
    </asp:sqldatasource>
    </asp:Content>

  6. #6
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,173

    Re: Using a Filter Expression in an SqlDataSource for an Integer Value

    Is the dropdownlist populated after the gridview is populated?

  7. #7

    Thread Starter
    Member
    Join Date
    Aug 2001
    Posts
    53

    Re: Using a Filter Expression in an SqlDataSource for an Integer Value

    No, the dropdown list is populated first. The user then chooses an item on the dropdown list and the gridview is populated. When the user selects a row on the gridview, it's supposed to populate the formview with that record.

  8. #8
    New Member ivymiller0's Avatar
    Join Date
    Sep 2014
    Posts
    2

    Re: Using a Filter Expression in an SqlDataSource for an Integer Value

    Quote Originally Posted by Pitbull View Post
    I have a gridview bound to a sqldatasource with a select command that selects from a table. When a user selects a row in that grid, my formview control should populate with that row's info. My formview is bound to another sqldatasource which has the same select command as the first sqldatasource but it has a filterexpression based on the value of a column from the selected row. The problem is that the column that I'm using the filterexpression on is an int32 (the column in the db is an identity seed) so as soon as I select the row from the gridview, I get the following error: "Cannot perform '=' operation on System.Int32 and System.String." Ok, I get that. It's looking for a string value and I'm giving it an Int32. So how do I fix this? I was able to capture the value on the GridView1_SelectedIndexChanged event (SqlDataSource2.filterexpression="c_entry='" & GridView1.SelectedRow.Cells(8).Text & "'". But I'm guessing this is a fairly common issue so there's got to be a way to get it to work without capturing the event and feeding it the value. So what's the secret?

    Hi Pitbull, would you mind posting your solution for the filter expression issue here, cuz I really wanna know how this worked out. Thanks.

  9. #9
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    Re: Using a Filter Expression in an SqlDataSource for an Integer Value

    This thread is seven years old, so he probably won't be tracking it. What have you got for code?
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney

  10. #10
    New Member ivymiller0's Avatar
    Join Date
    Sep 2014
    Posts
    2

    Re: Using a Filter Expression in an SqlDataSource for an Integer Value

    Just bumped into this page, and curious how this turned out..sorry I forgot to check the time, never meant for any thread resurrection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width