MySQL select query with parameter

Question

I am trying to use MYSQL select query with c#.

Following query for searching "ID" is working fine:

conn = new MySqlConnection(cs);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "select * from catalog_product_entity where entity_id = ?Id";
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
cmd.Parameters.Add("?Id", SqlDbType.Text).Value = ProductList[i].ProductId.ToString();
adp.Fill(MagentoProduct);

Now, I want to search exact string value in table. I am using following code and its giving empty result:

My Code:

                            conn = new MySqlConnection(cs);
                            conn.Open();
                            cmd = new MySqlCommand("select * from catalog_category_entity_varchar where value = @Value;", conn);
                            cmd.Parameters.AddWithValue("@Value", "Storybooks");
                            MySqlDataReader r = cmd.ExecuteReader();

                            while (r.Read())
                            {
                                log.WriteEntry(r.GetString("value"));
                            }

What does "giving an error" mean? Make sure to include the exact error message and/or accurate problem description. — user2864740
– user2864740, Commented Jun 12, 2014 at 18:22

Jon Skeet · Accepted Answer · 2014-06-12 18:16:16Z

3

This is the problem:

where value = '?cname'

That's specifying ?cname as the literal value you're searching for - when you actually just want the parameter. Remove the quotes and it should be fine:

where value = ?cname

(You should use using statements for the connection and command, mind you...)

answered Jun 12, 2014 at 18:16

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

3 Comments

Jon Skeet Over a year ago

@user2864740: True - unless it detected that a parameter had been passed but not specified?

user3685232 Over a year ago

I just changed "where value = '?cname'" to "where value = ?cname".

Jon Skeet Over a year ago

@user3685232: Right, and? (You should edit your post to say what error you were receiving, by the way.)

BlaugranaGent · Accepted Answer · 2014-06-12 18:23:58Z

0

You could try SQL Reader

c = new MySqlCommand("select * from catalog_product_entity where column_nam = @Value;", conn);
c.Parameters.AddWithValue("@Value", your string);
MySqlDataReader r = c.ExecuteReader();

and then use Reader methods like reader.GetString("column_name"), ....

answered Jun 12, 2014 at 18:23

BlaugranaGent

1001 gold badge2 silver badges7 bronze badges

2 Comments

user3685232 Over a year ago

HI BlaugranaGent, I just tried your code. No error but the result is still empty. I am not what is wrong.

BlaugranaGent Over a year ago

Well, it is good practise to use parameters because it gives you some protection against SQL Injection. I am not sure why result is empty, you should see in database are there any queries. Also, you could paste the exact command that is not working so I could tell you more

Collectives™ on Stack Overflow

MySQL select query with parameter

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related