• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1640
  • Last Modified:

How to use LIKE on int column?

I have a text box that users can type in a PersonID (all integers) and get a match of IDs in a datagrid.  At least that is the concept.  The problem is that I keep getting errors related to "Cannot perform LIKE operation on System.Int32 and System.String" or something similar depending on how I rearrange the datatable column data type and the filter expression.

I'm currently changing the integer column to a string hoping it can use LIKE.

ds.Tables[0].Columns["PersonID"].DataType = System.Type.GetType("System.String");

The above throws an exception because the column datatype can't be modified after it is filled with data.  I'm using a data adapter to fill a data set so the table will always have data unless I get into creating the datatable from scratch, which I want to avoid.

Then I do this:
dt.DefaultView.RowFilter = "PersonID Like '" + txtPersonCode.Text.ToString() + "'";

and get the error.  I want a user to type in 101 and get results such as 1010, 10122, 1011.  How can I do this?

Thanks,
Brett
0
brettr
Asked:
brettr
  • 7
  • 7
1 Solution
 
brettrAuthor Commented:
One problem I see is that it seems the column datatype is never changed.

Brett
0
 
gregoryyoungCommented:
try ... using

Convert(PersonId, 'System.String') like ...

That will convert to a string then use the string in your like comparison.

Cheers,

Greg
0
 
brettrAuthor Commented:
Where exactly are you using Convert?

Thanks,
Brett
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
gregoryyoungCommented:
0
 
brettrAuthor Commented:
That isn't working and it kicks out my tablestyle.

dt.DefaultView.RowFilter = "Convert(PersonID, 'System.String') Like '" + txtPersonID.Text.ToString( ) + "'";
0
 
gregoryyoungCommented:
whats the exception? that looks valid at first glance.
0
 
brettrAuthor Commented:
No errors but blanks out all the rows and I just have the column headers from the under lying table.
0
 
gregoryyoungCommented:
is it possible it is just returning no rows?

0
 
brettrAuthor Commented:
No.  I type in 2 or 4.  There are many rows in the grid I see starting with a 2 or 4.  Then I execute the filter and nothing.
0
 
gregoryyoungCommented:
2 or 4 would only match 2 or 4 ... 2* or 2% would match any starting with 2 or 4
0
 
brettrAuthor Commented:
Ok, that goes but it jumps out of the tablestyle.  I get the right results but my style is no longer there.  Any ideas?
0
 
gregoryyoungCommented:
not off the top of my head without looking at the rest of your code ... the rowfilter has nothing to do with your tablestyles.
0
 
brettrAuthor Commented:
Could it be that since the column type has changed, that column no longer maps to the tablestyle and so can't find a matching tablestyle (b/c it is off by one column) at that point?
0
 
gregoryyoungCommented:
you arent changing the column type.

you are using a conversion in the rowfilter ... the columns should stay the same.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now