7

I am connecting to an SQL Server 2012 database to query for a single value based on an ID. (It may be worth mentioning that this database is on a server on another continent from my development machine, and so latencies are quite high. Somewhere around 100ms).

The query appears to execute successfully. The HasRows property of the SqlDataReader object is set to true, so I try to use the value to assign a variable. When I run the program normally, I encounter an exception with message 'Given key was not present in the dictionary'. If I stop the execution and inspect the SqlDataReader object, and enumerate the results. Firstly I am told 'enumeration yielded no results' and then when I continue execution I get a different exception with the message 'invalid attempt to read when no data is present'

Here is the code in question:

SqlConnection sql_conn = new SqlConnection(ConnectionString);
SqlCommand sql_cmd = new SqlCommand(String.Format("select ItemType from ItemTable where ItemID='{0}'", item_id), sql_conn);

Console.WriteLine(sql_cmd.CommandText);

sql_conn.Open();

SqlDataReader rdr = sql_cmd.ExecuteReader();

rdr.Read();

if (rdr.HasRows) //True
{
    item_type= TypesMap[rdr["ItemType"].ToString()]; //Either 'given key not found in dictionary' or 'invalid attempt to read when no data is present'
}

I have executed the SQL statement in SQL Server Management Studio and it is successful. I have tried hardcoding an ItemID into the statement in the C# code, and the same errors exist.

What more can I do to debug this? Everything appears to be okay, until I try to access the results of the query.

Improve this question
4
  • 2
    Not causing this issue but: I'd reverse the order, first use HasRows to check if there are any rows and then use Read to advance the reader to the next record. Commented Apr 15, 2016 at 8:46
  • The two error messages are caused by two totally different situations. What is the one that you receive with this code? Commented Apr 15, 2016 at 8:46
  • 1
    I would skip reader.HasRows and implement logic like if (reader.Read()) { item_type= TypesMap[rdr["ItemType"].ToString()]; } Commented Apr 15, 2016 at 8:48
  • 1
    If you want get just one value, use rdr.ExecuteScalar(). Commented Apr 15, 2016 at 8:50

3 Answers 3

Reset to default
4

You have to debug: it seems that the TypesMap doesn't have the key read from the database:

// Wrap IDisposable into using
using (SqlConnection sql_conn = new SqlConnection(ConnectionString)) {
  // Make SQL readable
  // Make SQL parametrized (and not formatted) when it's possible
  String sql = 
    @"select ItemType 
        from ItemTable 
       where ItemID = @prm_ItemId"; 

  // Wrap IDisposable into using
  using (SqlCommand sql_cmd = new SqlCommand(sql, sql_conn)) {
    // I don't know ItemID's type that's why I've put AddWithValue 
    sql_cmd.Parameters.AddWithValue("@prm_ItemId", item_id);

    // Wrap IDisposable into using
    using (SqlDataReader rdr = sql_cmd.ExecuteReader()) {
      // rdr.HasRows is redundant - rdr.Read() returns true if record has been read
      if (rdr.Read()) {
        String key = Convert.ToString(rdr.GetValue(0));
        // Put break point here: what is the "key" value?
        item_type = TypesMap[key];
      }
    }
  } 
}

Edit: as Luke has mentioned in the comment, the cause of the error was that key comparison is expected to be case insensitive, so the amendment is to explain .Net how to compare keys:

var TypesMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);

...
TypesMap.Add("aBc", "xyz"); 
String test = TypesMap["Abc"]; // return "xyz"; notice "aBc" and "Abc"
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks. It was unrelated to the Database query in the end. As ever it was just a stupid mistake, I needed to make the dictionary key all lowercase.
@Luke: a better choice is not to make keys lower case, but assign the appropriate comparer: TypesMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
I wasn't aware of this. Thanks!
2

As Dmitry pointed out the 'given key not found...' is not a DB thing but a dictionary thing. Below I've added a simple check to ensure the key is in the dictionary - if it is then we can assigned to item_type.

Also, if HasRows() isn't doing what you expect, try the following. It's the standard way I read from DB:

using (SqlDataReader results = sql_cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
    while (results.Read())
    {
        string Key = rdr["ItemType"].ToString();
        if (TypesMap.ContainsKey(Key))
            item_type = TypesMap[Key];       
    }
}
Improve this answer

4 Comments

your standard practice is not THE standard practice. you should always call hasrows as standard practice.
@MaxAlexanderHanna Um, I didn't say and didn't imply it was standard practice. Just the way "I do it. Hence the word "I" and no mention of "standard practice".
if it doesn't have rows, theres a bigger problem and your attempt at "standardizing practice" is actually counter productive. no clue how you got two points for this answer.
@MaxAlexanderHanna lol, really. You pull up answer that's 3 years old (that's not even the accepted answer) and accuse me of being counter productive?
1

I Converted:

dto.Id = (int)record["Id"];

To:

dto.Id = (int)record[0];

This worked for me.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.