SqlDataAdapter Output Variable Question C#
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][&开发者_运维知识库quot;AutoHold"].ToString();
trd = HoldVals.Rows[0]["Trade"].ToString();
hld = HoldVals.Rows[0]["Hold"].ToString();
extnow = HoldVals.Rows[0]["ExitNow"].ToString();
return HoldVals;
}
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);
精彩评论