SqlDataAdapter Output Variable Question C#
15,895
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);
Author by
CraigJSte
Updated on June 27, 2022Comments
-
CraigJSte almost 2 years
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; }
-
CraigJSte over 13 yearsthanks 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.