C# reading int from mysql

Question

Having problem reading a value from my table in mysql, is the index value i cant read the value back no matter what. all i get is the initialized value of 0 i dont get any error because it return 0, if i run the query in the database it get the correct value. i tried to use executeScalar() but with the same result .

     MySqlConnection conn = new MySqlConnection(MyConString);
    ulong ukey=0;
            try
            {
                string sql_users2 = "SELECT `key` FROM `permuser` WHERE `user` = '" + myuser + "' AND `code` = '" + mycode + "'";
                MySqlCommand cmdSel2 = new MySqlCommand(sql_users2, conn);
                conn.Open();
                MySqlDataReader dr2 = cmdSel2.ExecuteReader();
                dr2.Read();

                    ukey = dr2.GetUInt64(dr2.GetOrdinal("key")); 
                   // MessageBox.Show("Sorry " + myuser + " already have access to " + mycode + ",\nIf this is an extension, search for the user which key is  " + ukey + "   and edit the end date.", "Duplicate User Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                dr2.Close();
                dr2.Dispose();
            }
            catch (MySqlException ex) //catch 2
            {

                MessageBox.Show("catch ukey\nCan't connect to database\n" + ex.ToString());
            }

            conn.Close();
            conn.Dispose();

define "can't read the value back": what comes back ? any error ? can you append a real-life example (i.e. "if data is like this, code should return an apple, but instead returns a banana") ? — Alex
– Alex, Commented Jan 3, 2013 at 15:24
I assume that you've used the debugger, what was the result? Btw, use parameters! — Tim Schmelter
– Tim Schmelter, Commented Jan 3, 2013 at 15:26

Steve · Accepted Answer · 2013-01-03 15:40:48Z

1

You are returning a single value from your query, so you could use directly ExecuteScalar instead of ExecuteReader. (the link point to the description for SqlServer, but it is the same for MySql)

An important question to never forget is the usage of parameters instead of string concatenation.
What happen if your myuser or mycode variables contain a single quote? You get wrong results or syntax errors. Of course, the main problem is the Sql Injection attack to never understimate.

using(MySqlConnection conn = new MySqlConnection(MyConString))
{
    ulong ukey=0;
    try
    {
        string sql_users2 = "SELECT `key` FROM `permuser` WHERE `user` = @usr AND `code` = @code";
        MySqlCommand cmdSel2 = new MySqlCommand(sql_users2, conn);
        conn.Open();
        cmdSel2.Parameters.AddWithValue("@usr", myuser);
        cmdSel2.Parameters.AddWithValue("@code", mycode);

        object result  = cmdSel2.ExecuteScalar();
        if(result != null)
            ukey = Convert.ToUInt64(result); 


    }
    catch (MySqlException ex) //catch 2
    {
        MessageBox.Show("catch ukey\nCan't connect to database\n" + ex.ToString());
    }
}

also I am a bit perplexed about your usage of UInt64. What kind of datatype is stored in the key column?

edited Jan 3, 2013 at 15:40

answered Jan 3, 2013 at 15:34

Steve

217k22 gold badges242 silver badges296 bronze badges

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

7 Comments

Reaven Smith Over a year ago

about the UInt64, I was also because it doesnt make any sense but someone told to tried that and sometimes you get to the point of trying anything no matter what it is...

Steve Over a year ago

Well, but what datatype is column key? The conversion could fail or simply give wrong results.

Steve Over a year ago

Do you have negative numbers? It should not be like that with auto-increment, but I'll ask the same because if you try to convert a negative integer value to an UInt64 you get errors. Try this sample with LinqPAD. Try to use a normal int for ukey and Convert.ToInt32(result);. Also could you update your question with the exact error message (ex.ToString())?

Steve Over a year ago

Sorry, I have read your update now. The result from ExecuteScalar is null or is zero? If it is null, are you sure to connect to the correct database?

Reaven Smith Over a year ago

thats the problem i dotn get any error, I just get 0 as a value !

|

devilkkw · Accepted Answer · 2013-01-03 15:30:00Z

0

way is many simply:

ukey = (uint)dr2[0];

answered Jan 3, 2013 at 15:30

devilkkw

4482 gold badges6 silver badges17 bronze badges

Collectives™ on Stack Overflow

C# reading int from mysql

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related