John Marsh John Marsh - 4 months ago 9
Vb.net Question

Cannot perform 'Like' operation on System.Int32 and System.String

I am getting this error when I try to add a search text box for my CertificateID column. I know it's becuase you can't use the "like" operation since my CertificateID column is int32 not a string. I just don't know what the code should be to make it work. I have tried Cast() function but haven't had any luck. could someone please help me with the code.
I am using the filterexpression to filter my data:

FilterExpression="[CertificateID] like '{0}%' and [Customs_Entry] like '{0}%' and [Product_Number] like '{1}%' and [Product_Name] like '{2}%'">

<asp:ControlParameter ControlID="txtCertificateID_Filter" Name="CertificateID" PropertyName="Text" Type="String" ConvertEmptyStringToNull="false" />


My full html is:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">

<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>

<asp:Label ID="CertificateID_Label" runat="server" Width="169px">Certificate Number</asp:Label>
<asp:TextBox ID="txtCertificateID_Filter" runat="server" Width="180px"></asp:TextBox>
<br />
<br />
<asp:Label ID="Customs_Entry_Label" runat="server" Width="169px">Customs Entry Number</asp:Label>
<asp:TextBox ID="txtCustoms_Entry_Filter" runat="server" Width="180px"></asp:TextBox>
<br />
<br />
<asp:Label ID="Product_Number_Label" runat="server" Width="169px">Product Number</asp:Label>
<asp:TextBox ID="txtProduct_Number_Filter" runat="server" Width="180px"></asp:TextBox>
<br />
<br />
<asp:Label ID="Customs_Name_Label" runat="server" Width="169px">Product Name</asp:Label>
<asp:TextBox ID="txtProduct_Name_Filter" runat="server" Width="180px"></asp:TextBox>
<br />
<br />
<asp:Button ID="btnSearch" runat="server" Text="Search" />
<br />
<br />

<asp:GridView ID="Certificate" runat="server" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="CertificateID" DataSourceID="SqlDataSource2" ShowFooter="True">
<Columns>
<asp:TemplateField ShowHeader="False">
<EditItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update" Text="Update"></asp:LinkButton>
&nbsp;<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton>
</EditItemTemplate>
<FooterTemplate>
<asp:Button ID="AddRow" runat="server" CommandName="Insert" Text="Add" />
</FooterTemplate>
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"></asp:LinkButton>
&nbsp;<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="CertificateID" HeaderText="Certificate Number" ReadOnly="True" SortExpression="CertificateID" />
<asp:TemplateField HeaderText="Customs Entry" SortExpression="Customs_Entry">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Customs_Entry")%>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="TxtCustoms_Entry" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Customs_Entry")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date Certificate" SortExpression="Date_Certificate">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Date_Certificate")%>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtDate_Certificate" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("Date_Certificate")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Invoice Number" SortExpression="Invoice_Number">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Invoice_Number")%>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtInvoice_Number" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("Invoice_Number")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Product Number" SortExpression="Product_Number">
<EditItemTemplate>
<asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Product_Number")%>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtProduct_Number" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("Product_Number")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Product Name" SortExpression="Product_Name">
<EditItemTemplate>
<asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("Product_Name")%>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtProduct_Name" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("Product_Name")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Product Category" SortExpression="Product_Category">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Name" DataValueField="Name" SelectedValue='<%# Bind("Product_Category") %>'>
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:GreatGrizzlyConnectionString2 %>" SelectCommand="SELECT [Name] FROM [Category]"></asp:SqlDataSource>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="txtProduct_Category" runat="server" DataSourceID="SqlDataSource1" DataTextField="Name" DataValueField="Name">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:GreatGrizzlyConnectionString2 %>" SelectCommand="SELECT [Name] FROM [Category]"></asp:SqlDataSource>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Bind("Product_Category")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="CPSC Regulation" SortExpression="CPSC_Regulation">
<EditItemTemplate>
<asp:TextBox ID="TextBox7" runat="server" Text='<%# Bind("CPSC_Regulation")%>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="TxtCPSC_Regulation" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label7" runat="server" Text='<%# Bind("CPSC_Regulation")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date Manufactured" SortExpression="Date_Manufactured">
<EditItemTemplate>
<asp:TextBox ID="TextBox8" runat="server" Text='<%# Bind("Date_Manufactured")%>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="TxtDate_Manufactured" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label8" runat="server" Text='<%# Bind("Date_Manufactured")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Manufacturer" SortExpression="Manufacturer">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource3" DataTextField="Name" DataValueField="Name" SelectedValue='<%# Bind("Manufacturer")%>'>
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:GreatGrizzlyConnectionString2 %>" SelectCommand="SELECT [Name] FROM [Manufacturer]"></asp:SqlDataSource>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="txtManufacturer" runat="server" DataSourceID="SqlDataSource3" DataTextField="Name" DataValueField="Name">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:GreatGrizzlyConnectionString2 %>" SelectCommand="SELECT [Name] FROM [Manufacturer]"></asp:SqlDataSource>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label9" runat="server" Text='<%# Bind("Manufacturer")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Email" SortExpression="Date_Tested">
<EditItemTemplate>
<asp:TextBox ID="TextBox10" runat="server" Text='<%# Bind("Date_Tested")%>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtDate_Tested" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label10" runat="server" Text='<%# Bind("Date_Tested")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Test Lot ID" SortExpression="Test_Lot_ID">
<EditItemTemplate>
<asp:TextBox ID="TextBox11" runat="server" Text='<%# Bind("Test_Lot_ID")%>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="TxtTest_Lot_ID" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label11" runat="server" Text='<%# Bind("Test_Lot_ID")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name Laboratory" SortExpression="Name_Laboratory">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList3" runat="server" DataSourceID="SqlDataSource4" DataTextField="Name" DataValueField="Name" SelectedValue='<%# Bind("Name_Laboratory")%>'>
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:GreatGrizzlyConnectionString2 %>" SelectCommand="SELECT [Name] FROM [Laboratory]"></asp:SqlDataSource>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="txtName_Laboratory" runat="server" DataSourceID="SqlDataSource4" DataTextField="Name" DataValueField="Name">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:GreatGrizzlyConnectionString2 %>" SelectCommand="SELECT [Name] FROM [Laboratory]"></asp:SqlDataSource>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label12" runat="server" Text='<%# Bind("Name_Laboratory")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:GreatGrizzlyConnectionString %>" DeleteCommand="DELETE FROM [Certificate] WHERE [CertificateID] = @CertificateID" InsertCommand="INSERT INTO [Certificate] ([Customs_Entry], [Date_Certificate], [Invoice_Number], [Product_Number], [Product_Name], [Product_Category], [CPSC_Regulation], [Date_Manufactured], [Manufacturer], [Date_Tested], [Test_Lot_ID], [Name_Laboratory]) VALUES (@Customs_Entry, @Date_Certificate, @Invoice_Number, @Product_Number, @Product_Name, @Product_Category, @CPSC_Regulation, @Date_Manufactured, @Manufacturer, @Date_Tested, @Test_Lot_ID, @Name_Laboratory)" SelectCommand="SELECT * FROM [Certificate]" UpdateCommand="UPDATE [Certificate] SET [Customs_Entry] = @Customs_Entry, [Date_Certificate] = @Date_Certificate, [Invoice_Number] = @Invoice_Number, [Product_Number] = @Product_Number, [Product_Name] = @Product_Name, [Product_Category] = @Product_Category, [CPSC_Regulation] = @CPSC_Regulation, [Date_Manufactured] = @Date_Manufactured, [Manufacturer] = @Manufacturer, [Date_Tested] = @DatE_Tested, [Test_Lot_ID] = @Test_lot_ID, [Name_Laboratory] = @Name_Laboratory WHERE [CertificateID] = @CertificateID" FilterExpression="[CertificateID] like '{0}%' and [Customs_Entry] like '{0}%' and [Product_Number] like '{1}%' and [Product_Name] like '{2}%'">
<FilterParameters>
<asp:ControlParameter ControlID="txtCertificateID_Filter" Name="CertificateID" PropertyName="Text" Type="String" ConvertEmptyStringToNull="false" />
<asp:ControlParameter ControlID="txtCustoms_Entry_Filter" Name="Customs_Entry" PropertyName="Text" Type="String" ConvertEmptyStringToNull="false" />
<asp:ControlParameter ControlID="txtProduct_Number_Filter" Name="Product_Name" PropertyName="Text" Type="String" ConvertEmptyStringToNull="false" />
<asp:ControlParameter ControlID="txtProduct_Name_Filter" Name="Product_Number" PropertyName="Text" Type="String" ConvertEmptyStringToNull="false" />
</FilterParameters>
<DeleteParameters>
<asp:Parameter Name="CertificateID" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="Customs_Entry" Type="String" />
<asp:Parameter Name="Date_Certificate" Type="String" />
<asp:Parameter Name="Invoice_Number" Type="String" />
<asp:Parameter Name="Product_Number" Type="String" />
<asp:Parameter Name="Product_Name" Type="String" />
<asp:Parameter Name="Product_Category" Type="String" />
<asp:Parameter Name="CPSC_Regulation" Type="String" />
<asp:Parameter Name="Date_Manufactured" Type="String" />
<asp:Parameter Name="Manufacturer" Type="String" />
<asp:Parameter Name="Date_Tested" Type="String" />
<asp:Parameter Name="Test_Lot_ID" Type="String" />
<asp:Parameter Name="Name_Laboratory" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="Customs_Entry" Type="String" />
<asp:Parameter Name="Date_Certificate" Type="String" />
<asp:Parameter Name="Invoice_Number" Type="String" />
<asp:Parameter Name="Product_Number" Type="String" />
<asp:Parameter Name="Product_Name" Type="String" />
<asp:Parameter Name="Product_Category" Type="String" />
<asp:Parameter Name="CPSC_Regulation" Type="String" />
<asp:Parameter Name="Date_Manufactured" Type="String" />
<asp:Parameter Name="Manufacturer" Type="String" />
<asp:Parameter Name="Date_Tested" Type="String" />
<asp:Parameter Name="Test_Lot_ID" Type="String" />
<asp:Parameter Name="Name_Laboratory" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
<br />
<br />
<br />
<br />
<br />
<br />

</div>
</form>
</body>
</html>


Here is the correct code:

FilterExpression="Convert(CertificateID , 'System.String') LIKE '{0}%' and [Customs_Entry] like '{1}%' and [Product_Number] like '{2}%' and [Product_Name] like '{3}%'">

Answer

Try

CAST(Product_Number as varchar(10)) LIKE '{1}%'

you can change the 10 to whatever is appropriate.

EDIT OK, then it is not getting to SQL server, have you tried

Convert(Product_Number , 'System.String') LIKE '{1}%'
Comments