2

I have a method in my C# class that calls a single result from an SQLite database. My database connection opens successfully, but after running breakpoints at different places, I noticed something odd. This is my code:

Dictionary<string, string> getResult(string id)
{
    dataConn.Open();
    SQLiteCommand comm = dataConn.CreateCommand();
    comm.CommandText = "SELECT * FROM [tableName] WHERE id='" + id + "'";
    SQLiteDataReader result = comm.ExecuteReader();
    Dictionary<string, string> resultDict = new Dictionary<string, string>();

    /*
    This doesn't work.
    while(result.Read())
    {
        resultDict.Add(result.GetName(0), result.GetString(0));
    }
    */

    result.Read();
    for (int i = 0; i < result.FieldCount; i++)
    {
        resultDict.Add(result.GetName(i), result.GetString(i));
    }
    result.Dispose();
    comm.Dispose();
    dataConn.Close();
    return resultDict;
}

I know that I can use SQLiteParameters instead of inline concatenation, but for some reason they were breaking my query.

In my first loop, if I place a breakpoint just before it starts, I can see that my query has returned a single result, exactly what I need. But, when I go into my loop, before I execute any code other than reader.Read(), VS2010 says "Enumeration yielded no results." but they were there moments before?

So, I tried the second loop. Executing reader.Read(), and then looping through returned fields. When I do that, VS2010 throws the error "No current row selected.".

I'm completely lost with this, I have no idea what's wrong, because the rest of my SQLite connections for other database files work fine. I've tested my query in SQLite Administrator, and my query executes perfectly fine. All I'm aiming for is to end up with a dictionary where the key is the name of the field, and the value is the result of said field. Any ideas on what's wrong? Or, is there a way I can make this code simpler, in the process of fixing it?

Connor Deckers
  • 2,447
  • 4
  • 26
  • 45

3 Answers3

4

Solved. For some reason, using .GetString(0) or .GetString(i) was breaking it. However, when I turned it into .GetValue(0).ToString() it worked fine. I have no idea why this is the case, it's beyond me, but it works.

Connor Deckers
  • 2,447
  • 4
  • 26
  • 45
0

Its because GetValue(0) didn't return a string.

Casting it to a string allowed you to add it to the Dictionary<string,string>.

Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321
  • But why would this cause there to be no values stored in `reader`? – Connor Deckers Jun 24 '12 at 03:36
  • dude, thats just strange behaviour. Perhaps you did the DoWhile loop first, (I know the codes commented out) and then when you tried it result.Read which would have already been read? Just a guess but now I'm not sure why the ToString() method works. – Jeremy Thompson Jun 24 '12 at 03:39
  • 1
    Nope, I know that could cause an issue, so I checked, it was either only called in the `while` loop, or on it's own and then iterated through the `for` loop. But yes, it's weird. – Connor Deckers Jun 24 '12 at 03:42
0

I had a similar problem, in my case the table was empty, and i was asking for MAX() of some collumn. I solved it by first checking that COUNT(*) is not 0.

mlatu
  • 159
  • 1
  • 12