asp mvc3: Search both string and Integer in a single query

Question

I come across a very silly issue. I have a searchbox in which user can enter both numeric and string. Some of Where clauses are integer while others are string. My query is something like:

string keyword = "";
string query = "Select * from Table
where POrderNumber = {0}
AND
Fname LIKE '% {0}%'";
query = string.Format(query,keyword);

I am getting Data Conversion Error

what value are you giving for this to happen? you also realise that both the FName value and the POrderNumber value will be the same, correct? what type is POrderNumber? to me looks like an integer therefore if you enter a string, it will NOT work....hence the error from SQL. — Ahmed ilyas
– Ahmed ilyas, Commented Oct 28, 2014 at 17:26
Yes PONumber is integer that's why I am getting error. My question is how to cater both? — Volatil3
– Volatil3, Commented Oct 28, 2014 at 17:31
what value are you actually entering in the box? for example if you enter "ABC123", this will error out completely because POrderNumber is of type int but you entered a string. you therefore cannot cater for it. the data type must be the same. — Ahmed ilyas
– Ahmed ilyas, Commented Oct 28, 2014 at 17:32
Isn't there way to parse string to Int in the int type field? I typed Jhon — Volatil3
– Volatil3, Commented Oct 28, 2014 at 17:34

IyaTaisho · Accepted Answer · 2014-10-28 17:36:53Z

1

My suggestion would be to make the query built conditionally.

int POrderNumber;
string keyword = "";
string query = "Select * from Table Where ";
if (Int32.TryParse(keyword, out POrderNumber))
  query += "POrderNumber = " + POrderNumber;
if (!string.IsNullOrEmpty(keyword))
  query += " AND Fname LIKE '%" + keyword + "%'";

You can simplify it a bit more if you want, but that is the basic gist of it.

answered Oct 28, 2014 at 17:36

IyaTaisho

88320 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Volatil3 Over a year ago

Guess we had no other choice.

Collectives™ on Stack Overflow

asp mvc3: Search both string and Integer in a single query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related