Passing Parameters to a Stored Procedure (in C# NOT VB)

[DRAYKKO]

I'm having some problems getting my stored procedure to see th parameters I'm passing in. The parameters are retrieved from a web form, and seen to be successfully send to the data access tier, but when I run the code I get the following exception: "Procedure or Function 'stbFormPropertiesToXML' expects parameter '@displayMode', which was not supplied. " The code is:

        DisplayMode = dMode;
        FolderAlias = \_folderAlias;
        SqlConnection conn = new SqlConnection(@"Data Source= .;Initial Catalog=MXContact.NET\_Demo\_100401;Integrated Security=true;");

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "stbFormPropertiesToXML";    //stored procedure
        SqlDataAdapter frmDA = new SqlDataAdapter(cmd.CommandText, cmd.Connection);
        DataSet FormPropertiesDS = new DataSet();

        try
        {
            conn.Open();
            cmd.Parameters.Add(new SqlParameter("@displayMode", SqlDbType.NVarChar));
            cmd.Parameters\["@displayMode"\].Direction = ParameterDirection.Input;

            cmd.Parameters.Add(new SqlParameter("@folderAlias", SqlDbType.NVarChar));
            cmd.Parameters\["@folderAlias"\].Direction = ParameterDirection.Input;


            cmd.Parameters\["@displayMode"\].Value = DisplayMode;
            cmd.Parameters\["@folderAlias"\].Value = FolderAlias;

            cmd.ExecuteNonQuery();
            frmDA.Fill(FormPropertiesDS);
            conn.Close();

            FormPropertiesDS.WriteXml("FormProperties.xml");
            //FormPropertiesDS.WriteXml(MapPath("FormProperties.xml"));
        }
        catch (Exception ex)
        {
            errorMsg = ex.Message;
        }
        finally
        {
            conn.Close();
        }

Where DisplayMode and FolderAlias are the parameters I'm trying to pass into the SP. The SP is defined as:

ALTER PROCEDURE [dbo].[stbFormPropertiesToXML]
@displayMode NVARCHAR(2),
@folderAlias NVARCHAR(30)

AS

SELECT *
FROM [stbFormProperties]
WHERE [FolderKey] = (SELECT [FolderKey] FROM [stbFolders] WHERE [FolderAlias] = @folderAlias)
AND [DisplayMode] = @displayMode

I've been eearching the web for articles for the past serveral hours; so a second pair of eyes would be a big help. DRAYKKO

[Not Active]

You using the wrong method, cmd.ExecuteNonQuery(); is for commands like updates and inserts. You want to return data so need to use something like ExecuteReader. However, since you are using DataAdapater.Fill the call is not necessary at all. Since SqlParameters are input by default it is not necessary to set it explicitly and the value can be set directly using AddWithValue[^] so you eliminate a few lines. Also, using SELECT * in a sql statement is a poor practice.

---

I know the language. I've read a book. - _Madmatt

modified on Tuesday, June 22, 2010 2:36 PM

[DRAYKKO]

Thanks for the prompt reply! I'll correct that... DRAYKKO

======================= Every experience in life is a lesson to be learned A. Stevens B.S., Computer Science

[Adam R Harris]

Using Stored Procedures in Conjuction with DataAdapter[^] It's in VB.NET but Use this to convert it[^]

If at first you don't succeed ... post it on The Code Project and Pray.