Problem with C# and Stored procedure

Question

Hi

I want to run a stored procedure to load records from the database into a dataset. It gives me message that the stored procedure requires a parameter @ProdIndex which is not supplied. I have debugged my code and I cannot see what the problem is. Some of the code is as follows (all my stored procedures that require parameters, are giving me similar messages):

public class ProductManager
{
public ProductManager()
{
}

public static DataSet LoadProductsSProc(int prodIndex)
{
//sproc is the Stored Procedure name
//IS THERE A BETTER WAY THAN HARD-CODING IT HERE
string sproc = "Select_Products_ProdID";

//create two-dimensional array of objects to hold the parameter name and value
//IS THERE A BETTER WAY OF DOING THIS?
object[,] parameters = new object[1,2];
parameters[0,0] = "ProdIndex";
parameters[0,1] = prodIndex;

DataSet ds = GeneralDAL.LoadObjectsDS(sproc, parameters);
return ds;
}
}



public class GeneralDAL
{

public static DataSet LoadObjectsDS(string sprocedure, object[,] parameters)
{
DataSet ds = null;

SqlConnection conn = null;

try
{
//instantiate the conn object – this works fine
conn = new SqlConnection(“database”,”server”);
conn.Open();

//create a SqlCommand object
SqlCommand sqlCmd = new SqlCommand(sprocedure, conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

//add the parameters to the SqlCommand object
for (int i=0; i<parameters.GetLength(0); i++)
{
//the parameter name in my stored procedure is @ProdIndex
string parameter = "@" + (string) parameters[i, 0];
object val = parameters[i, 1];
sqlCmd.Parameters.Add(parameter, val);
//So the previous step looks like sqlCmd.Parameters.Add("@ProdIndex", 1);
}

ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter(sqlCmd.CommandText, conn);

//IT IS THE NEXT STEP THAT THROWS THE EXCEPTION
//THE MESSAGE IS: Procedure requires a parameter which is not supplied
da.Fill(ds);
}
catch (Exception e)
{
Console.WriteLine("MESSAGE = " + e.Message);
}
finally
{
if (dbc != null)
{
conn.Close();
conn.Dispose();
}
}
return ds;
}
}


Any help and advice will be greatly appreciated.

Kobus