SqlDataAdapter Output Variable Question C#

Question

I do not clearly understand how to format the SqlDataAdapter for output variables when working with C#

Error Message:

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Code Example (Stored Procedure works fine):

private DataTable updateOrdEodHold(DataTable tb, out string mnpft,
    out string authld, out string trd, out string hld, out string extnow)
{
    // Start the connection string.
    string connstr = ConfigurationManager.ConnectionStrings
        ["LocalSqlServer"].ConnectionString;
    SqlConnection myConn = new SqlConnection(connstr);
    // Declare symbol and assign for Errors Catch Exception.
    string Symbol = "";
    string sqlComm = "dbo.UpdateOrdEodHold";
    DataTable HoldVals = new DataTable();
    SqlDataAdapter dataAdp = new SqlDataAdapter(sqlComm, myConn);
    dataAdp.SelectCommand.CommandType = CommandType.StoredProcedure;
    string ticker = (string)Convert.ToString(tb.Rows[0]["Ticker"]);
    // update Symbol for Catch ex
    Symbol = ticker.ToString();
    String company = (string)Convert.ToString(tb.Rows[0]["Company"]);
    String avgprofit = (string)Convert.ToString(tb.Rows[0]["AvgProfit"]);
    String extdte = (string)Convert.ToString(tb.Rows[0]["ExitDate"]);
    dataAdp.SelectCommand.Parameters.Clear();
    dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@ticker",
        SqlDbType.VarChar, 10));
    dataAdp.SelectCommand.Parameters["@ticker"].Value =
        (string)ticker.ToString();
    dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@company",
        SqlDbType.VarChar, 25));
    dataAdp.SelectCommand.Parameters["@company"].Value =
        (string)company.ToString();
    dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@avgpft",
        SqlDbType.VarChar, 10));
    dataAdp.SelectCommand.Parameters["@avgpft"].Value =
        (string)avgprofit.ToString();
    dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@mnpft",
        SqlDbType.VarChar, 10));
    dataAdp.SelectCommand.Parameters["@mnpft"].Direction =
        ParameterDirection.Output;
    dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@authld",
        SqlDbType.VarChar, 6));
    dataAdp.SelectCommand.Parameters["@authld"].Direction =
        ParameterDirection.Output;
    dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@hld",
        SqlDbType.VarChar, 6));
    dataAdp.SelectCommand.Parameters["@hld"].Direction =
        ParameterDirection.Output;
    dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@trd",
        SqlDbType.VarChar, 6));
    dataAdp.SelectCommand.Parameters["@trd"].Direction =
        ParameterDirection.Output;
    dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@extnow",
        SqlDbType.VarChar, 6));
    dataAdp.SelectCommand.Parameters["@extnow"].Direction =
        ParameterDirection.Output;
    dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@extdte",
        SqlDbType.VarChar, 15));
    dataAdp.SelectCommand.Parameters["@extdte"].Value =
        (string)extdte.ToString();
    dataAdp.Fill(HoldVals);
    mnpft = HoldVals.Rows[0]["MinProfit"].ToString();
    authld = HoldVals.Rows[0]["AutoHold"].ToString();
    trd = HoldVals.Rows[0]["Trade"].ToString();
    hld = HoldVals.Rows[0]["Hold"].ToString();
    extnow = HoldVals.Rows[0]["ExitNow"].ToString();
    return HoldVals;
}

Jagmag · Accepted Answer · 2010-10-03 16:26:31Z

9

You need to hold a reference to the Output parameter variable so that you can access the value returned to it using parameter.Value once the adapter has executed the command.

//Create the parameter 
SqlParameter parameter = new SqlParameter("@mnpft", SqlDbType.VarChar);

//Set the parameter direction as output
parameter.Direction = ParameterDirection.Output;

sqlCommand.Parameters.Add(parameter);

SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);
sqlAdapter.Fill(dataSet);

//Fetch the output parameter after doing the Fill
string outputValue = Convert.ToString(parameter.Value);

answered Oct 3, 2010 at 16:26

Jagmag

10.4k1 gold badge37 silver badges60 bronze badges

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

1 Comment

CraigJSte Over a year ago

thanks for your answer I am sure it is useful but I am going to just hit the table twice - update with one and then just return a table with the next to get the parameters... it' easier for me.

Collectives™ on Stack Overflow

SqlDataAdapter Output Variable Question C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related