MySql "Select Where" and C#

Question

How can i read the return value from "Select Where" statement , every time i run no return value appear in the label, and no syntax error.

command.CommandText = "select product_price from product where product_name='"+x+"';";
            connection.Open();
            Reader = command.ExecuteReader();
            while(Reader.Read()){


            Price_label.Content = "" + Reader.GetString(0);

            }
            connection.Close();

Does it really have any relevant records in the database ? maybe you can also think of passing the name as a parameter — V4Vendetta, Mar 20 '12 at 12:53
i did , still no return appear in the label , so i think something wrong with the Reader Part . — A.R, Mar 20 '12 at 12:56
You're from a VB6 background, are you? First off, C# is a static language, so get rid of that `"" + ` habit for typecasting. Secondly, you want to look [at parameterized queries](http://bobby-tables.com/). Additionally, you should utilize the `IDispoable` interface via `using()`. — Bobby, Mar 20 '12 at 12:58
From your code I can't see if the command is connected to the connection. Is it connected right? — Steve, Mar 20 '12 at 13:04
Yes Steve , it is connected , all my problem is in reading the return value :S .. the label doesn't show any values when i run — A.R, Mar 20 '12 at 13:08

Thorsten Dittmar · Accepted Answer · 2012-03-20T13:43:56.237

4

If the product_price column is not of type TEXT in MySQL, the Reader.GetString(0) will (depending on how the reader was implemented by Oracle) throw an Exception or return an empty string. I would think the latter is happening.

Retrieving the value through a DataReader requires you to know the data type. You can not simply read a string for every type of field. For example, if the field in the database is an Integer, you need to use GetInt32(...). If it is a DateTime use GetDateTime(...). Using GetString on a DateTime field won't work.

EDIT
This is how I'd write this query:

using (MySqlConnection connection = new MySqlConnection(...))
{
    connection.Open();
    using (MySqlCommand cmd = new MySqlCommand("select product_price from product where product_name='@pname';", connection))
    {
        cmd.Parameters.AddWithValue("@pname", x);
        using (MySqlDataReader reader = cmd.ExecuteReader())
        {
            StringBuilder sb = new StringBuilder();
            while (reader.Read())
                sb.Append(reader.GetInt32(0).ToString());

            Price_label.Content = sb.ToString();
        }
    }
}

edited Mar 20 '12 at 13:43

answered Mar 20 '12 at 12:57

Thorsten Dittmar

55,956
8
91
139

i changed it from GetString() to GetInt32() ,still no return value , and the column type is INT – A.R Mar 20 '12 at 13:10
Have you made sure that there actually is a record that matches `x`? – Thorsten Dittmar Mar 20 '12 at 13:11
yea i tested it on Mysql Command and it worked , even in the code replaced X with a know product name , still no return value. – A.R Mar 20 '12 at 13:34
Just noticed that there was an error in the code - when creating the command, the second parameter must be `connection`, not `conn` as `conn` doesn't exist :-) – Thorsten Dittmar Mar 20 '12 at 13:44

Bobby · Answer 2 · 2012-03-20T13:29:34.167

To append to my comment, your approach has three problems which are not part of your problem:

SQL-Injection, always use parameterized queries.
Leaking resources, IDisposable-Objects need to be treated properly.
Bad habits, "" + string for casting is...uhhh...not good and not necessary.

So, a more correct version for your code would look like this:

// using utilizes the IDisposable-Interface, whcih exists to limit the lifetime
// of certain objects, especially those which use native resources which
// otherwise might be floating around.
using(YourConnectionType connection = new YourConnectionType("connectionstring"))
{
    connection.Open(); // You might want to have this in a try{}catch()-block.

    using(YourCommandType command = connection.CreateCommand())
    {
        command.CommandText = "select product_price from product where product_name=@NAME;";
        command.Parameters.Add("NAME", YourTypes.VarChar);
        command.Parameters[0].Value = x; // For your own sanity sake, rename that variable!

        using(YourReaderType reader = command.ExecuteReader())
        {
            while(reader.Read()) // If you're expecting only one line, change this to if(reader.Read()).
            {
                Price_label.Content = reader.GetString(0);
            }
        }
    }
} // No need to close the conenction explicit, at this point connection.Dispose()
  // will be called, which is the same as connection.Close().

score 0 · Answer 3 · answered Sep 02 '19 at 08:48

0

You should write @pname without '' otherwise it won't work.

instead of:

select product_price from product where product_name='@pname'

you should write like this:

select product_price from product where product_name=@pname

answered Sep 02 '19 at 08:48

Tugrul

76
5

score 0 · Answer 4 · answered Mar 20 '12 at 12:56

0

you have to create a variable of your reader

command.CommandText = "select product_price from product where product_name='"+x+"';";
try {
connection.Open();
SqlReader reader = command.ExecuteReader();
while(reader.Read()){


    Price_label.Content = "" + Reader.GetString(0);

}
} catch (Exception) {}
finally {
connection.Close();
}

answered Mar 20 '12 at 12:56

Safari

3,302
9
45
64

Nope, your code wouldn't work, as you're not using `reader`, but `Reader` to actually read the value. – Thorsten Dittmar Mar 20 '12 at 12:58
yes. thanks. of course you have to write reader.GetString(0); instead of Reader.GetString(0); – Safari Mar 20 '12 at 12:59

MySql "Select Where" and C#

4 Answers4

Linked