?
Solved

How to use LIKE on int column?

Posted on 2006-04-20
14
Medium Priority
?
1,636 Views
Last Modified: 2008-01-09
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
Comment
Question by:brettr
  • 7
  • 7
14 Comments
 

Author Comment

by:brettr
ID: 16503005
One problem I see is that it seems the column datatype is never changed.

Brett
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 16503144
try ... using

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

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

Cheers,

Greg
0
 

Author Comment

by:brettr
ID: 16503179
Where exactly are you using Convert?

Thanks,
Brett
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 37

Expert Comment

by:gregoryyoung
ID: 16503200
0
 

Author Comment

by:brettr
ID: 16503249
That isn't working and it kicks out my tablestyle.

dt.DefaultView.RowFilter = "Convert(PersonID, 'System.String') Like '" + txtPersonID.Text.ToString( ) + "'";
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 16503362
whats the exception? that looks valid at first glance.
0
 

Author Comment

by:brettr
ID: 16503378
No errors but blanks out all the rows and I just have the column headers from the under lying table.
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 16503391
is it possible it is just returning no rows?

0
 

Author Comment

by:brettr
ID: 16503397
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
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 500 total points
ID: 16503402
2 or 4 would only match 2 or 4 ... 2* or 2% would match any starting with 2 or 4
0
 

Author Comment

by:brettr
ID: 16503418
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
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 16503425
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
 

Author Comment

by:brettr
ID: 16503486
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
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 16503874
you arent changing the column type.

you are using a conversion in the rowfilter ... the columns should stay the same.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question