Return Parameter from SQL Server Stored Procedure

[IndigoMontoya]

Greetings - below is my syntax where I am attempting to pass a parameter to a stored procedure, and return a parameter from the stored procedure. When I execute my code, I get the error:

System.Data.SqlClient.SqlException: 'Procedure or function '_GetInfo' expects parameter '@rn', which was not supplied.'


Below is my C# and my SQL - how should I alter this syntax so that it executes as desired and return the value?


Alter PROCEDURE _GetInfo
(
@sn varchar(200),
@rn VARCHAR(200) OUT
)
AS
BEGIN
SELECT @rn= rn FROM testdata WHERE sn=@sn
END

private void Form2_Load(object sender, EventArgs e)
{
dtSN = dtAS.DefaultView.ToTable(true, "locale");

foreach (DataRow row in dtSN.Rows)
{
var locale = row["locale"] as string;
dataGridView1.DataSource = dtAS;
dataGridView1.AutoResizeColumns();
((DataTable)dataGridView1.DataSource).DefaultView.RowFilter = "locale = '" + locale + "'";
var csvString = dataGridView1.ForEachCell(wrapper => EscapeSpecialChars(wrapper, '&', '/')).ChangeRows(RemoveExtraData).ToCsvString();

csvString = dataGridView1.ToCsvString();

using (SqlConnection conn3 = new SqlConnection(StartupForm.ServerAddressToUse))
{
conn3.Open();
SqlCommand sqlComm = new SqlCommand("_GetInfo", conn3);
sqlComm.Parameters.AddWithValue("@sn", Convert.ToString(locale));

sqlComm.CommandType = CommandType.StoredProcedure;
SqlParameter retval = sqlComm.Parameters.Add("@rn", SqlDbType.VarChar);

retval.Direction = ParameterDirection.ReturnValue;

sqlComm.ExecuteNonQuery();
string rn = (string)sqlComm.Parameters["@rn"].Value;
conn3.Close();
conn3.Dispose();
}

this.Close();
}
}

Continue reading...