Query working in mysql but not from c# code

Question

I am having c# code like this:

using (MySqlConnection con = new MySqlConnection(AR.ConnectionString))
{
    con.Open();
    using (MySqlCommand cmd = new MySqlCommand(@"SELECT PORUDZBINAID, USERID, BRDOKKOM, DATUM,
                STATUS, MAGACINID, PPID, INTERNIKOMENTAR, REFERENT_OBRADE, NACIN_PLACANJA, TAG FROM 
                PORUDZBINA WHERE TAG LIKE '%@MOB%'", con))
    {
        cmd.Parameters.AddWithValue("@MOB", Mobilni);

        MySqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())
            list.Add(new Porudzbina()
            {
                PorudzbinaID = Convert.ToInt32(dr[0]),
                UserID = Convert.ToInt32(dr[1]),
                BrDokKom = Convert.ToInt32(dr[2]),
                Datum = Convert.ToDateTime(dr[3]),
                Status = (PorudzbinaStatus)Convert.ToInt32(dr[4]),
                MagacinID = Convert.ToInt32(dr[5]),
                PPID = (dr[6] is DBNull) ? (int?)null : Convert.ToInt32(dr[6]),
                InterniKomentar = (dr[7] is DBNull) ? null : dr[7].ToString(),
                ReferentObrade = (dr[8] is DBNull) ? (int?)null : Convert.ToInt32(dr[8]),
                NacinUplate = (PorudzbinaNacinUplate)Convert.ToInt32(dr[9]),
                Tag = JsonConvert.DeserializeObject<Properties>(dr["TAG"].ToString())
            });
    }
}

I put breakpoint and it passes good paramter to query but doesn't enter while() loop (so i is not reading) and it returns no rows.

When i enter same query in my mysql and replace @MOB with parameter passed there, it does return me one row.

I guess problem is something with passing LIKE through c# but not sure why it does that.

Try `WHERE TAG LIKE @MOB",` and then `cmd.Parameters.AddWithValue("@MOB", "%" + Mobilni + "%");` instead — ADyson, Apr 09 '20 at 11:14

jason.kaisersmith · Accepted Answer · 2020-04-10T06:32:10.263

2

You need to change how you are adding parameters slightly:

In your SQL, no quotes and no % symbols.

using (MySqlCommand cmd = new MySqlCommand(@"SELECT PORUDZBINAID, USERID, BRDOKKOM, DATUM,
                STATUS, MAGACINID, PPID, INTERNIKOMENTAR, REFERENT_OBRADE, NACIN_PLACANJA, TAG FROM 
                PORUDZBINA WHERE TAG LIKE @MOB", con))
    {

Then the parameter like this, without quotes.

cmd.Parameters.AddWithValue("@MOB", "%" + Mobilni + "%");

BTW: Ideally you should not use AddWithValue, but rather Add(). See this blog: https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/

And this SO post: MySqlCommand Command.Parameters.Add is obsolete

Instead, it should be like this:

cmd.Parameters.Add("@MOB", SqlDbType.Varchar).Value = "%" + Mobilni + "%";
//you must update to use the correct DBType for your data

edited Apr 10 '20 at 06:32

answered Apr 09 '20 at 11:17

jason.kaisersmith

8,712
3
29
51

This solution didn't work but one without `**` did work. Just removed quotes and `%` from `cmd` and parameter passed as you wrote. – Aleksa Ristic Apr 09 '20 at 11:24
BTW: What should i use instead of `AddWithValue`? Any link on explaining? – Aleksa Ristic Apr 09 '20 at 11:25
@AleksaRistic Updated. Sorry the ** was a typo!! – jason.kaisersmith Apr 09 '20 at 11:35
1

Go ahead and use `AddWithValue` for MySQL: https://mysqlconnector.net/overview/using-addwithvalue/ – Bradley Grainger Apr 09 '20 at 17:34

score 0 · Answer 2 · answered Apr 09 '20 at 11:16

Since @MOB is inside single quotes, it's interpreted as a string literal, which isn't what you meant. You can fix this by concatenating the placeholder to the % characters:

using (MySqlCommand cmd = new MySqlCommand(
    @"SELECT PORUDZBINAID, USERID, BRDOKKOM, DATUM, STATUS, MAGACINID, PPID, INTERNIKOMENTAR, REFERENT_OBRADE, NACIN_PLACANJA, TAG 
      FROM PORUDZBINA 
      WHERE TAG LIKE CONCAT('%', @MOB, '%')", con))

Query working in mysql but not from c# code

2 Answers2