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
– ADyson, Commented Apr 9, 2020 at 11:14

jason.kaisersmith · Accepted Answer · 2020-04-10 06:32:10Z

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, 2020 at 6:32

answered Apr 9, 2020 at 11:17

jason.kaisersmith

9,7233 gold badges35 silver badges61 bronze badges

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

4 Comments

Aleksa Ristic Over a year ago

This solution didn't work but one without ** did work. Just removed quotes and % from cmd and parameter passed as you wrote.

Aleksa Ristic Over a year ago

BTW: What should i use instead of AddWithValue? Any link on explaining?

jason.kaisersmith Over a year ago

@AleksaRistic Updated. Sorry the ** was a typo!!

Bradley Grainger Over a year ago

Go ahead and use AddWithValue for MySQL: mysqlconnector.net/overview/using-addwithvalue

Mureinik · Accepted Answer · 2020-04-09 11:16:44Z

0

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))

answered Apr 9, 2020 at 11:16

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Collectives™ on Stack Overflow

Query working in mysql but not from c# code

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related