Why does SQLite tell me "No current row"?

Question

The query being sent is good; running it:

SELECT line_id, description, department, upc_pack_size, pack_size, unit_cost, unit_list FROM Inventory WHERE siteNum = "03" AND upc_code = "76145513"

...in LINQPad returns a record with these values:

line_id = 0
description = [empty string]
department = 2.99
upc_pack_size = 333
pack_size = 333
unit_cost = 50.01
unit_list = 50.99

But the code:

public List<String> GetDynamicINVValsForUPCCode(String qry, String siteNum, String upcCode)
{
    ExceptionLoggingService.Instance.WriteLog(String.Format("Reached TestHHSDBUtils.GetDynamicINVValsForUPCCode(); siteNum is {0}; upcCode is {1}; qry is {2}", siteNum, upcCode, qry));
    List<String> dsdValsForUPCCode = new List<string>(); 
    try
    {
        using (SQLiteConnection conn = new SQLiteConnection(HHSUtils.GetDBConnection()))
        {
            conn.Open();
            using (SQLiteCommand cmd = new SQLiteCommand(qry, conn))
            {
                cmd.Parameters.Add(new SQLiteParameter("upcCode", upcCode));
                cmd.Parameters.Add(new SQLiteParameter("SiteNum", siteNum));
                using (SQLiteDataReader rdr = cmd.ExecuteReader())
                {
                    dsdValsForUPCCode.Add(Convert.ToString(rdr["Line_id"]));       // Line_id, int32
                    dsdValsForUPCCode.Add(Convert.ToString(rdr["Description"]));   // Description
                    dsdValsForUPCCode.Add(Convert.ToString(rdr["Department"]));    // Department
                    dsdValsForUPCCode.Add(Convert.ToString(rdr["Upc_pack_size"])); // Upc_pack_size (int32)
                    dsdValsForUPCCode.Add(Convert.ToString(rdr["Pack_size"]));     // Pack_size (int32)
                    dsdValsForUPCCode.Add(Convert.ToString(rdr["Unit_qty"]));      // Unit_qty (single)
                }
            }
            return dsdValsForUPCCode;
        }
    }
    catch (Exception ex)
    {
        String msgInnerExAndStackTrace = String.Format("{0}; Inner Ex: {1}; Stack Trace: {2}", ex.Message, ex.InnerException, ex.StackTrace);
        ExceptionLoggingService.Instance.WriteLog(String.Format("From TestHHSDBUtils.GetDynamicINVValsForUPCCode: {0}", msgInnerExAndStackTrace));
        return null;
    }
}

...fails, the err being logged as:

Message: From TestHHSDBUtils.GetDynamicINVValsForUPCCode: No current row; Inner Ex: ; Stack Trace:    at System.Data.SQLite.SQLiteDataReader.CheckValidRow()
   at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
   at System.Data.SQLite.SQLiteDataReader.get_Item(String name)
   at HHS.TestHHSDBUtils.GetDynamicINVValsForUPCCode(String qry, String siteNum, String upcCode)

Why does it say "no current row" when there is one?

Answer 1

While I'm not explicitly familiar with the SqlLiteReader, but the rest of the readers require you to read the first row. I'm using an if statement but where you are expecting numerrous rows you would use a while. The DataReader.Read() returns a boolean to indicate if a record has been read. https://msdn.microsoft.com/en-us/library/haa3afyz%28v=vs.110%29.aspx

using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
  if (rdr.Read()){
    dsdValsForUPCCode.Add(Convert.ToString(rdr["Line_id"]));       // Line_id, int32
    dsdValsForUPCCode.Add(Convert.ToString(rdr["Description"]));   // Description
    dsdValsForUPCCode.Add(Convert.ToString(rdr["Department"]));    // Department
    dsdValsForUPCCode.Add(Convert.ToString(rdr["Upc_pack_size"])); // Upc_pack_size (int32)
    dsdValsForUPCCode.Add(Convert.ToString(rdr["Pack_size"]));     // Pack_size (int32)
    dsdValsForUPCCode.Add(Convert.ToString(rdr["Unit_qty"]));      // Unit_qty (single)
  }
}

Answer 2

When you set the parameters, the parameter name needs the @ prefix to work. So your code should instead read:

cmd.Parameters.Add(new SQLiteParameter("@upcCode", upcCode));
cmd.Parameters.Add(new SQLiteParameter("@SiteNum", siteNum));

Additionally (as pointed out in other answers) you also need to perform a Read() on the reader first:

rdr.Read();