C# write select from mysql to variable

Question

I have a big problem with save variable from select in mysql.

I wrote the following code:

string connectionstring = @"****;userid=*****;
password=***;database=***";
cnn = new MySqlConnection(connectionstring);
cnn.Open();
MySqlDataReader reader = null;
string query_date = "SELECT computer_name from wp_users where user_login = @login";
MySqlCommand command2 = new MySqlCommand(query_date, cnn);
command2.Parameters.AddWithValue("@login", metroTextBox.Text);
reader = command2.ExecuteReader();
while (reader.Read())
{
   string ColumnName = (string)reader["computer_name"];
}
cnn.Close();

I tried a lot of commands like ExecuteReader , ExecuteNonQuery , ExecuteScalar. but none of them worked and I am getting the same error:

Really don't know what is wrong here I searched a lot and didn't find a solution of any form. Please help.

EDIT 1

I just did how you wrote and i did this :

     string connectionstring = @"****;userid=*****;
        password=***;database=***";
        cnn = new MySqlConnection(connectionstring);
        cnn.Open();
        MySqlDataReader reader = null;
        string query_date = "SELECT computer_name from wp_users where user_login = @login";
        MySqlCommand command2 = new MySqlCommand(query_date, cnn);
        command2.Parameters.AddWithValue("@login", metroTextBox.Text);
        DataTable table = new DataTable("ResultTable");
            MySqlDataAdapter adapter = new MySqlDataAdapter(command2);
            adapter.Fill(table);

            // This is the important line
            string result = table.Rows[0].ToString();

            cnn.Close();

Its the same error as earlier but another place. What's going on here... just don't know. Additional information mean in english : The key is not present in the dictionary

EDIT 2

The funniest is when i just try to update with code :

 string connectionstring = @"****;userid=*****;
    password=***;database=***";
    cnn = new MySqlConnection(connectionstring);
    cnn.Open();
    MySqlDataReader reader = null;
    string upd = "UPDATE w_users Set computer_name = CURRENT_DATE where user_login =  @login";
    MySqlCommand command2 = new MySqlCommand(upd, cnn);
    command2.Parameters.AddWithValue("@login", metroTextBox.Text);
    DataTable table = new DataTable("ResultTable");
    SqlDataAdapter adapter = new MySqlDataAdapter(command2);
    adapter.Fill(table);
    cnn.Close();

And this works fine without any errors just update my table... What's the point of it

EDIT 3

I jutr try used to ExecuteScalar() and still i have the same error :

No i just create an application in winforms and try to connect to mysql database — Michael, Apr 13 '17 at 12:31
maybe that the exception details can help you. Please, post here the stack trace and inner exception details — Vinicius Gonçalves, Apr 13 '17 at 12:33
@Michael is there a class called MySqlDataAdapter or something like that? — Thomas Flinkow, Apr 13 '17 at 12:34
Yes i have line with MySqlDataAdapter but , when i try execute only this code have this error — Michael, Apr 13 '17 at 12:36
@Michael I now posted an answer with code for you to see if that works, I am glad to help if it still does not. — Thomas Flinkow, Apr 13 '17 at 12:44
What happens if, just for testing, you hard-code a known valid value for login instead of `metroTextBox.Text`? — Crowcoder, Apr 13 '17 at 13:02

score 1 · Accepted Answer · edited Apr 13 '17 at 19:13

1

The solution is simple:

Updating mysql.data.dll to the newest version fixed it (https://dev.mysql.com/downloads/connector/net/6.9.html).

edited Apr 13 '17 at 19:13

halfer

19,824
17
99
186

answered Apr 13 '17 at 17:36

Michael

202
1
3
13

ichselber · Answer 2 · 2017-04-13T12:37:55.063

0

This exception wants to tell you, that there is no matching key in your resultset. Keys are case sensitive. Have you tried to spell your column name with all uppercase letters: (string)reader["COMPUTER_NAME"]?

Databases tend to return the column names in uppercase, even though you selected the column name in lower case.

edited Apr 13 '17 at 12:37

answered Apr 13 '17 at 12:36

ichselber

11
2

The error happens before the attempt to key into the reader's current position. – Crowcoder Apr 13 '17 at 12:49
Oops, did not notice the position of the exception in your screen shot. I tried your code and I could not reproduce the error you described. Maybe there is something wrong with your MySQL driver installation. – ichselber Apr 13 '17 at 13:46
command2.Parameters.AddWithValue("@login", metroTextBox.Text); The "@" may cause this problem. Have you tried command2.Parameters.AddWithValue("login", metroTextBox.Text); – ichselber Apr 13 '17 at 13:56
I am not the OP. But from what I know about the mysql provider (admittedly not much), parameters are positional and it doesn't really matter what they are named. – Crowcoder Apr 13 '17 at 14:02
i try with @login and without its the same ;/ – Michael Apr 13 '17 at 16:36

score 0 · Answer 3 · answered Apr 13 '17 at 12:42

0

If you have the MySqlDataAdapter, try if the following code works for you:

string connectionstring = @"****;userid=*****;
password=***;database=***";
cnn = new MySqlConnection(connectionstring);
cnn.Open();

string query_date = "SELECT computer_name AS `computer_name` FROM wp_users WHERE user_login = @login";

MySqlCommand command2 = new MySqlCommand(query_date, cnn);
command2.Parameters.AddWithValue("@login", metroTextBox.Text);

DataTable table = new DataTable("ResultTable");
MySqlDataAdapter adapter = new MySqlDataAdapter(command2);
adapter.Fill(table);

// This is the important line
string result = table["computer_name"];

cnn.Close();

answered Apr 13 '17 at 12:42

Thomas Flinkow

4,845
5
29
65

look main topic , i just edited, When i try do like you wrote string result = table["computer_name"]; i can't do that i have an error inside [] – Michael Apr 13 '17 at 16:12
@Michael I just looked at your Edits, it is strange that it still does not work. Can you run the following query `SHOW CREATE TABLE wp_users;` and show me the result? I then will be able to help you further – Thomas Flinkow Apr 13 '17 at 18:40
@Michael alright, as I see you **were** using Oracle MySql Connector even if you said no when I asked you :D either way I'm glad you fixed it – Thomas Flinkow Apr 13 '17 at 18:49

C# write select from mysql to variable

3 Answers3