query with store procedure returns empty

Question

Running query below is successfully shows the returned results;

sp_wts_lst_mod_amp_mtx 'xxxx', '062306', 'LS-I', 'EA', null

So I'm correctly passing the same values as parameters to below method but it is returning empty. reader.Read() is false.

What is wrong in my method?

public static List<string> GetMatrices(object userMarketId, string userMarketLabelId, string model, string amp)
{
    var matrices = new List<string>();
    var connection = new OdbcConnection();
    try
    {
        using (connection = clsWTSCommon.GetDBConnection(userMarketId, null))
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "{CALL " + StoreProcedures.SpWtsLstModAmpMtx + " (?,?,?,?,?)}";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("@ctl_mkt_id", OdbcType.NVarChar, 10).Value = userMarketId.ToString();
            command.Parameters.Add("@ctl_mkt_lbl_id", OdbcType.NVarChar, 10).Value = userMarketLabelId;
            command.Parameters.Add("@model_code", OdbcType.NVarChar, 16).Value = model;
            command.Parameters.Add("@amp_code", OdbcType.NVarChar, 3).Value = amp;
            command.Parameters.Add("@language_id", OdbcType.NVarChar, 3).Value = null;

            if (connection.State == ConnectionState.Open)
            {
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    matrices.Add(reader.GetString(reader.GetOrdinal("sspl")) + "/" +
                                 reader.GetString(reader.GetOrdinal("peak_gain")));
                }
            }
        }
    }
    catch (Exception ex)
    {
        clsDataAccess.ShowInfo(ex.Message);
    }
    finally
    {
        connection.Close();
        connection.Dispose();
    }

    return matrices;
}

Try to use `reader["columnName"]` instead of `reader.GetString(reader.GetOrdinal("column"))`. I don't think its the solutuion to your problem but it might help. Like this http://stackoverflow.com/a/4018146/1317953 — dburner, Jan 09 '14 at 11:32
@dburner reader.Read() is false so mentioned line is not working yet. there isn't any returned value to try your suggestion. — htcdmrl, Jan 09 '14 at 11:42

score 1 · Answer 1 · edited Jan 10 '14 at 19:42

1

This line:

command.CommandText = "{CALL " + StoreProcedures.SpWtsLstModAmpMtx + " (?,?,?,?,?)}";

should be:

command.CommandText = StoreProcedures.SpWtsLstModAmpMtx;

Cheers -

edited Jan 10 '14 at 19:42

NotMe

87,343
27
171
245

answered Jan 09 '14 at 18:11

simon at rcl

7,326
1
17
24

1

Not actually true. The line should be: `command.CommandText = StoreProcedures.SpWtsLstModAmpMtx;` The rest of that isn't needed. – NotMe Jan 09 '14 at 18:17
Thanks Chris; I wasn't sure so left that out. – simon at rcl Jan 10 '14 at 18:02

query with store procedure returns empty

1 Answers1