Getting SqlDataAdapter and SqlCommand confused

Question

I've got this calling method:

public string RunReportSteps(int _reportKey) {
    DataTable fStepsTable;
    fStepsTable =  GetStepsTable("xxx.console.pr_xxx");
    return (string)fStepsTable.Rows[1][3];
}

It calls this private method:

private DataTable GetStepsTable(string procName) {
    var connectionString = ConfigurationManager.ConnectionStrings["xxx"].ConnectionString;
    using(var conn = new SqlConnection(connectionString))
    using(var adapt = new SqlDataAdapter())
    using(var cmd = new SqlCommand(procName, conn)) {

        conn.Open();

        SqlParameter p = new SqlParameter("@ReportKey", this.ReportKey);
        p.Direction = ParameterDirection.Input;
        cmd.Parameters.Add(p);
        adapt.SelectCommand = cmd;

        DataSet mySet = new DataSet();
        adapt.Fill(mySet);     //<<<<<<<<<<<<<<<<<<<errors here
        return mySet.Tables[0];
    }
}

Why am I getting the following error message?

Procedure or function 'pr_xxx' expects parameter '@ReportKey', which was not supplied.

I don't know if it's the root of the issue, but shouldn't you specify that you are using a stored procedure? — Steve B
– Steve B, Commented Nov 26, 2012 at 15:08

Tim Schmelter · Accepted Answer · 2012-11-26 15:08:25Z

5

I assume that procName is the name of the stored-procedure. You haven't set the CommandType of the SqlCommand to StoredProcedure:

using(var conn = new SqlConnection(connectionString))
using(var cmd = new SqlCommand(procName, conn))
using(var adapt = new SqlDataAdapter(cmd)) {
    cmd.CommandType = CommandType.StoredProcedure; // <<< this was missing

    SqlParameter p = new SqlParameter("@ReportKey", this.ReportKey);
    p.Direction = ParameterDirection.Input;
    cmd.Parameters.Add(p);

    DataTable table = new DataTable();
    adapt.Fill(table);     
    return table;
}

answered Nov 26, 2012 at 15:08

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

whytheq Over a year ago

thanks ...+ I had no real need for the dataset; + I'd confused the order of the adapter and sqlcommand!

Tim Schmelter Over a year ago

@whytheq: note also that you don't need to open the connection manually since the DataAdapter will do it for you in Fill.

Collectives™ on Stack Overflow

Getting SqlDataAdapter and SqlCommand confused

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related