-1

Please help me with my problem I'm trying to find a number in SQL Server example 1000 but this code gives me an error.

public IEnumerable<NCABal> FindByNCABal(Double Data)
{ 
    var query = @"SELECT * FROM [dbo].[NCA_Balance] WHERE NCABalAmt LIKE '%'+'" + Data + "'+'%'";
    return this._db.Query<NCABal>(query).ToList();
}

Error:

Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.

How can I solve this problem? Please help me.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
James
  • 271
  • 1
  • 3
  • 13
  • Why are you using LIKE? Use = instead: `NCABalAmt = " + Data + ";` – Nick.Mc May 20 '16 at 02:43
  • Interesting, why doesn't an error occur in string concat for `Data` in the first place? And you should use `=` instead of `LIKE` – Eric May 20 '16 at 02:43
  • Is "LIKE" limited to strings only?? – James May 20 '16 at 02:46
  • What is the data type for the column NCABalAmt? – hivie7510 May 20 '16 at 02:48
  • 1
    LIKE is only for strings. You can't use it for numbers. Use `>`, `<', `>=`, `<=`, `=` or BETWEEN instead. Find a SQL tutorial; this is basic knowledge. Also, search for *parameterized queries*; concatenating SQL is an extremely bad practice that leaves you wide open for SQL injection. – Ken White May 20 '16 at 02:48

1 Answers1

1

You query is attempting to find entries where the NCABalAmt contains the number you are passing in in any part of it.

e.g. if you pass in 1000 then you are trying to look for any number withy 1000 in it. So you would get 10000, 1000, 6451000897 etc...

You will need to convert your original field to a varchar to use LIKE against it in this way.

WHERE CONVERT(varchar(100), NCABalAmt) LIKE '%" +...+ "%'"

Might do this. I'm not sure this is what you really intend though.

Brody
  • 2,074
  • 1
  • 18
  • 23
  • This solve my problem. That's what I really want to do, to find number with "1000" in it. Thank you so much Sir. :) – James May 20 '16 at 02:53
  • Sir, I have one more question. Why I can't match data in between dashes? Example in the date 2016-05-20, I'm trying to match number 5 using like statement but it can't find number 5 or even 05? – James May 20 '16 at 06:54
  • Date conversion are tricky you need to add a date format to the convert. Check out http://stackoverflow.com/questions/74385/how-to-convert-datetime-to-varchar. It would be best if you opened another question though. – Brody May 22 '16 at 21:27