"The given key was not present in the dictionary" when Execute the mysqldatareader in C#

Question

I have run the command without any error in mysql. my other command run fine but this code is not work. do someone know what happen with this code.

 private static User GetUser(MySqlCommand cmd)
        {
            User usr = new User();

            MySqlDataReader rdr = cmd.ExecuteReader();
            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    usr.Id = Convert.ToInt32(rdr["Id"]);
                    usr.Level = (Level)Enum.Parse(typeof(Level), rdr["level"].ToString());
                    usr.Email = rdr["email"].ToString();
                    usr.CreatedDate = Convert.ToDateTime(rdr["createdDate"].ToString());
                    usr.LastLoginDate = Convert.ToDateTime(rdr["lastLoginDate"].ToString());
                }
            }

           return usr;
        }

 public static User GetUserFromID(int userID)
        {
            string qry = "SELECT * FROM user WHERE ID = ?userID";

            User user = new User();
            MySqlConnection cnn = new MySqlConnection(Globals.CONNSTRING);
            cnn.Open();

            using (cnn)
            {
                MySqlCommand cmd = new MySqlCommand(qry, cnn);
                cmd.Parameters.AddWithValue("userID", userID);

                user = GetUser(cmd);

            }
            cnn.Close();
            return user;
        }

The code I paste here gave me error that

"The given key was not present in the dictionary."

on the line of Line 158:

MySqlDataReader rdr = cmd.ExecuteReader();

Do someone know what wrong happen with this code? I have added CharSet=utf8; in connectionstring as people suggest in SO.

The database I use is mariaDB and connector is mysql latest connector. Do someone know if this have any trouble.

I have no problem while I run my other function. The problem happen in this single function where I use mysqldatareader execution.

@DGibbs thanks but I am confused. My other query work fine written in C#. I have set utf-8 as people tell in their answer. Now I have check that paramter is set in right way. I thing I am missing something. Can you check it :) — user2126670, May 03 '13 at 07:52
What is the `Globals.connString` ? What is the value of `cnn` ? Are you sure your connection string is right? — Soner Gönül, May 03 '13 at 07:55
@SonerGönül yes, it's work on other function that I have written and they call from mysqlhelper. this single function written mysqlexecutereader make trouble. Well, i only have write 1 function who i posted here. — user2126670, May 03 '13 at 08:01
@user2126670: Please publish your connectionstring, and total exception incl. the stacktrace. — Martin Mulder, May 03 '13 at 09:12

Martin Mulder · Answer 1 · 2013-05-03T08:28:15.497

1

Try using @ instead of ?:

So:

string qry = "SELECT * FROM user WHERE ID = @userID";

and

cmd.Parameters.AddWithValue("@userID", userID);

As explained at the pages of MySQL: http://dev.mysql.com/doc/refman/5.0/en/connector-net-tutorials-intro.html#connector-net-tutorials-parameters

edited May 03 '13 at 08:28

answered May 03 '13 at 07:50

Martin Mulder

12,642
3
25
54

+1 for help, Please tell me the reason behind the answer, I am using Mysql and I am sure ? should work. Refer me to article who do your given code'way – user2126670 May 03 '13 at 07:56
I got my answer from: http://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c-sharp – Martin Mulder May 03 '13 at 08:06
1

@Martin Any idea why StackOverflow doesn't require a string against downvotes, so that they could then be metamoderated away if the reason given is wrong? – May 03 '13 at 08:08
@Poldie: True, it is not required, but sometimes very helpfull for the people who answer (who made a mistake) and people who read the answer (and are about to copy the mistake). – Martin Mulder May 03 '13 at 08:16
@user2126670: My answer is backed up by the pages of MySQL itself: http://dev.mysql.com/doc/refman/5.0/en/connector-net-tutorials-intro.html#connector-net-tutorials-parameters – Martin Mulder May 03 '13 at 08:29
@MartinMulder I have tried this trick but its' not work. Thanks for help. I not downvote your answer +1. – user2126670 May 03 '13 at 08:55

score 1 · Answer 2 · answered May 03 '13 at 08:09

1

You would use @ instead of ? as you are calling a parameter from sql which should be called in sql as: @userID (whatever type it is, VARCHAR, INT etc)

So your sql proc should look like this

Select *
From (TableName)
Where userID = @UserID

So in your form you would call @UserID as that is the parameter you passed in sql.

answered May 03 '13 at 08:09

Philip Gullick

995
7
22

I tried both way but none of them worked in case of executereader on mysql. still have same issue. – user2126670 May 03 '13 at 08:18
Did you call the @userID above the code I provided you? Look for type errors ins your work, perhaps you are calling a parameter with a capital letter somewhere when it should be a lower case. – Philip Gullick May 03 '13 at 08:22
i tried it but not got it work. I thing cmd.parameter.addwithvalue com with exception if this will have anything wrong. – user2126670 May 03 '13 at 08:59

"The given key was not present in the dictionary" when Execute the mysqldatareader in C#

2 Answers2