views:

45

answers:

1

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;

    }
+1  A: 

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);
InSane
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.
CraigJSte