none
Passing a parameter to a store procedure RRS feed

  • Question

  • Hi, have the following ;

    dsAsso = new DataSet();
          using (SqlConnection conn = new SqlConnection(Functions.GetConnectionString()))
          {
            SqlCommand cmd = conn.CreateCommand();
            
            
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "uspIFACount";
            SqlParameter param = new SqlParameter("@assoShortname", SqlDbType.VarChar,50);
            param.Direction= ParameterDirection.Input;
            param.Value = "IFA";
            cmd.Parameters.Add(param);
    
            SqlDataAdapter da = new SqlDataAdapter(cmd.CommandText, conn);
            conn.Open();
            
            da.Fill(dsAsso);
    
            conn.Close();
          }
    

    and here is the store procedure:

    create procedure uspIFACount
    (@assoShortname varchar(50) )
    as
    select count(distinct s.DirectoryID)as 'Asso_Cnt'
    		,s.AssociationId
    		,t.FullName as 'FullName'
    	from dbo.DirectoriesAssociationDtls s
    	 join dbo.bizform_Directory t
    		on s.AssociationId = t.DirectoryID and t.DirectoryType='Association'
    		where t.Shortname=@assoShortname
    	group by AssociationId,t.FullName
    go
    

    each time i run it i get the following error:

    System.Data.SqlClient.SqlException: Procedure or Function 'uspIFACount' expects parameter '@assoShortname', which was not supplied.

    any hint on what is wrong?

    thanks

    Friday, September 24, 2010 5:40 PM

Answers

  • I think the problem is with the SqlDataAdapter. You need to assign the SqlCommand object you created to the SelectCommand property of the SqlDataAdapter:

        SqlDataAdapter da = new SqlDataAdapter;
        da.SelectCommand = cmd;
    


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 24, 2010 6:33 PM

All replies

  • I think the problem is with the SqlDataAdapter. You need to assign the SqlCommand object you created to the SelectCommand property of the SqlDataAdapter:

        SqlDataAdapter da = new SqlDataAdapter;
        da.SelectCommand = cmd;
    


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 24, 2010 6:33 PM
  • It did worked , but im still not getting it;
    Is the
      SqlDataAdapter da = new SqlDataAdapter(cmd.CommandText, conn);
    
    

    not suppose to assign the command.text statement to the select command of the sqlDataAdapter automatically and set the connection object for it ?

     

    Friday, September 24, 2010 7:14 PM
  • The problem with that statement is that the SqlDataAdapter only has the CommandText and SQL connection you assigned when it was created. It is missing the Parameters collection.

    When you assign the complete SqlCommand object you created, which has the CommandText, Parameters and Connection, to the SqlDataAdapter.SelectCommand object, the SqlDataAdapter now has all the data it needs to execute the stored procedure.

    Does that make sense?


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 24, 2010 8:30 PM
  • Hum, i gree but still a bit confused since  a sql direct statement to the command with the paramter in it (@param) does work;

    I was thinking that may be because the command text is only accessed after the connection open, the Sqldataadapter has no knowledge about it's content until connection ;i guess ;)

    Thanks

    Friday, September 24, 2010 8:40 PM