0

I installed SqlHelper.DB NuGet package (https://www.nuget.org/packages/SQLHelper.DB/) and I am having issues passing parameters to the AddQuery method. I am passing a List of SqlParameter to my function and I receive an error: 'Failed to convert parameter value from a List`1 to a Int32.'

Here is the code to call my method:

SqlProcessor sqlproc = new SqlProcessor("Data Source = myserver; Initial Catalog = mydb; User ID = me; Password = $uper$ecret;");
List<SqlParameter> p = new List<SqlParameter>
{
    new SqlParameter("@MyParam1", "param1"),
    new SqlParameter("@MyParam2", "param2"),
};
List<dynamic> l = sqlproc.ExecuteNonQuery(new Models.SqlCommandHelper("dbo.spGoGadgetGo", p));

Here is the method:

public List<dynamic> ExecuteNonQuery(SqlCommandHelper sch)
{
    var results = SQLHelper.CreateBatch()
        .AddQuery(sch.CommandType, sch.StoredProcedureName, sch.StoredProcedureParameters)
        .Execute();
    return results[0];
}

SqlCommandHelper is a small class I wrote:

class SqlCommandHelper
{
    public SqlCommandHelper(string spName, List<SqlParameter> parameters)
    {
        StoredProcedureName = spName;
        StoredProcedureParameters = parameters;
        CommandType = CommandType.StoredProcedure;
    }

    public SqlCommandHelper(string queryString)
    {
        QueryString = queryString;
        CommandType = CommandType.Text;
    }

    public CommandType CommandType { get; set; }
    public string StoredProcedureName { get; set; }
    public string QueryString { get; set; }
    public List<SqlParameter> StoredProcedureParameters { get; set; }
}

Any help greatly appreciated!

Jeremy Hodge
  • 612
  • 3
  • 14
  • What type of database are you using? For example, Access does not use named fields in a query. Access uses index numbers instead of names. – jdweng Nov 08 '19 at 17:25
  • I am using MS SQL. I should also mention that if I call a parameterless procedure, it does work successfully. – Jeremy Hodge Nov 08 '19 at 17:40
  • Try var for the return type and see if you still get an exception. – jdweng Nov 08 '19 at 18:03
  • Thanks for the suggestion but still no luck. I also changed it to void but same error. – Jeremy Hodge Nov 08 '19 at 19:12
  • Have you tried passing `p.ToArray()` instead of just `p` when calling `ExecuteNonQuery`? – CPerson Nov 08 '19 at 20:42
  • From : return results[0]; To : return results; – jdweng Nov 09 '19 at 04:25
  • @CPerson - received error: Unhandled Exception: System.InvalidCastException: Failed to convert parameter value from a SqlParameter to a Int32. ---> System.InvalidCastException: Object must implement IConvertible. – Jeremy Hodge Nov 11 '19 at 13:31
  • @jdweng - i changed ExecuteNonQuery to a void and same error – Jeremy Hodge Nov 11 '19 at 13:34
  • 1
    SQLHelper.DB doesn't use SqlParameters. It has its own internal set of classes called Parameter and StringParameter. On top of that the AddQuery function uses params IParameter[] parameters or params object[] parameters for that field. So in your case it's sending in the list of SqlParameters as an object and the code doesn't know how to convert that. Switch to using StringParameter instead of SqlParameter and send in an array instead of a list. – JaCraig Nov 11 '19 at 17:07
  • Thanks for getting back to me JaCraig. It's now attempting to run the stored proc but getting an error Procedure expects parameter @MyParam1 which was not supplied. I loop through the parameters right before executions and it does exist in the IParameter array. – Jeremy Hodge Nov 11 '19 at 18:46
  • Created new question for parameter issue: https://stackoverflow.com/questions/58807720/sqlhelper-db-not-passing-parameters-to-stored-procedure – Jeremy Hodge Nov 11 '19 at 19:45

0 Answers0