locked
stored procedure cant see my Paramaters RRS feed

  • Question

  •   having problems executing a stored procedure.  the error is from the sqlserver.  I can get it to work using tsql in a test script of the stored procedure.
    
    <span style="font-size:small; font-family:Calibri">any help would be helpful.</span>
    
      using (SqlConnection con = new SqlConnection(S_XREF_NameSpace.Properties.Settings.Default.UA_SharesMxDBConnectionString))
                {
                    using (SqlCommand command = new SqlCommand("q06ElementNames",con))
                    {
                        SqlParameter myParm1 = new SqlParameter();
                        myParm1.DbType = (System.Data.DbType)SqlDbType.NVarChar;
                        myParm1.ParameterName = "@ObjectTypeIn";
                        myParm1.Size = 20;
                        myParm1.SqlDbType = SqlDbType.NVarChar;
                        myParm1.Direction = ParameterDirection.Input;
                        myParm1.SqlValue = "Program_";
    
                    //    myParm1.Value = "Program_";
                        command.Parameters.Add(myParm1);
    
                        SqlParameter myParm2 = new SqlParameter("@CMDBPartName", SqlDbType.NVarChar, 8);
                        myParm2.Direction = ParameterDirection.Output;
                        command.Parameters.Add(myParm2);
    
    
    

     

     

    System.Data.SqlClient.SqlException was caught

      Message=Procedure or function 'q06ElementNames' expects parameter '@ObjectTypeIn', which was not supplied.

      Source=.Net SqlClient Data Provider

      ErrorCode=-2146232060

      Class=16

      LineNumber=0

      Number=201

      Procedure=q06ElementNames

      Server=WDEN8131\UA_SHARES

      State=4

      StackTrace:

           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

           at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

           at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

           at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

           at System.Data.SqlClient.SqlDataReader.get_MetaData()

           at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

           at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

           at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

           at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

           at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

           at System.Data.SqlClient.SqlCommand.ExecuteReader()

           at S_XREF_DataBaseLayer.Source_Code_MSSQL2008_D_B_L.q06NamesByOT(String myObjectType) in C:\Documents and Settings\n097377\My Documents\Visual Studio 2010\Projects\WindowsFormsApplication1\WindowsFormsApplication1\Source_Code_MSSQL2008_D_B_L.cs:line 109

      InnerException:

     

     

    MyE

    {"Procedure or function 'q06ElementNames' expects parameter '@ObjectTypeIn', which was not supplied."}

        base {System.Data.Common.DbException}: {"Procedure or function 'q06ElementNames' expects parameter '@ObjectTypeIn', which was not supplied."}

        Class: 16

        Errors: {System.Data.SqlClient.SqlErrorCollection}

        LineNumber: 0

        Number: 201

        Procedure: "q06ElementNames"

        Server: "WDEN8131\\UA_SHARES"

        Source: ".Net SqlClient Data Provider"

        State: 4

     

     this works

    -- =============================================
    -- Script Template
    -- =============================================
    USE [UA_SharesMxDB]
    GO
     
    DECLARE	@return_value int,
    		@CMDBPartName nvarchar(8)
     
    EXEC	@return_value = [dbo].[q06ElementNames]
    		@ObjectTypeIn = N'Program_',
    		@CMDBPartName = @CMDBPartName OUTPUT
     
    SELECT	@CMDBPartName as N'@CMDBPartName'
     
    SELECT	'Return Value' = @return_value
     
    GO
    

    Peter G. Simmon
    Wednesday, October 26, 2011 8:36 PM

Answers

  • i FOUND THE PROBLEM, i NEVER SET COMMAND.COMMANDTYPE=STORED PROCEDURE.

    THANKS FOR YOUR HELP


    Peter G. Simmon
    • Marked as answer by PeterSimmon Friday, October 28, 2011 1:38 PM
    Friday, October 28, 2011 1:38 PM

All replies

  • here is the complete method

                using (SqlConnection con = new SqlConnection(S_XREF_NameSpace.Properties.Settings.Default.UA_SharesMxDBConnectionString))
                {
                    using (SqlCommand command = new SqlCommand("q06ElementNames",con))
                    {
                        SqlParameter myParm1 = new SqlParameter();
                        myParm1.DbType = (System.Data.DbType)SqlDbType.NVarChar;
                        myParm1.ParameterName = "@Param1";
                        myParm1.Size = 20;
                        myParm1.SqlDbType = SqlDbType.NVarChar;
                        myParm1.Direction = ParameterDirection.Input;
                        myParm1.SqlValue = "Program_";
     
                    //    myParm1.Value = "Program_";
                        command.Parameters.Add(myParm1);
     
                        SqlParameter myParm2 = new SqlParameter("@CMDBPartName"SqlDbType.NVarChar, 8);
                        myParm2.Direction = ParameterDirection.Output;
                        command.Parameters.Add(myParm2);
     
     
     
                        try
                        {
                            con.Open();
                            string myString;
                            using (SqlDataReader reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    myString=(string)command.Parameters["@CMDBPartName"].Value;
                                    myData.Add(myString);
                                }
                            }
                        }
                            // add in personalized exceptions
                        catch (SqlException MyE)
                        {
                            throw MyE;
                        }

    Peter G. Simmon
    • Proposed as answer by pdelco Thursday, October 27, 2011 2:12 AM
    • Unproposed as answer by pdelco Thursday, October 27, 2011 2:12 AM
    Wednesday, October 26, 2011 8:57 PM
  • Peter,

     

    Looks like you may have a type-o. The line where you are setting the parameter name is incorrect. This line:

     

    myParm1.ParameterName = "@Param1";

    should be:

    myParm1.ParameterName = "@ObjectTypeIn";

    HTH


    Paul Delcogliano
    Thursday, October 27, 2011 2:15 AM
  • i FOUND THE PROBLEM, i NEVER SET COMMAND.COMMANDTYPE=STORED PROCEDURE.

    THANKS FOR YOUR HELP


    Peter G. Simmon
    • Marked as answer by PeterSimmon Friday, October 28, 2011 1:38 PM
    Friday, October 28, 2011 1:38 PM