none
Passing Parameters to StoredProcedure RRS feed

  • Question

  • Hai all

     

    I ve a VB.NET windows application ... in that  i enter a MsgID in text box ..

    and depending upon the MsgID that i entered in the text box i will fetch the StoredProcedure name from my SQLServer Database Table and store the procedure name in the variable named Proc_Name. The variable P_I_Fld_CNT contains the number of input parameters and P_O_Fld_CNT contains the number of output parameters....

    and call the Function called Execute_Proc to execute the procedure...The table is as below

     

    MsgID                                StoredProcedureName       InputFieldCount               OutputFieldCount

    1                                         usp_get_status                       2                                        1

    2                                         get_loan_det                           3                                        1

    3                                         curr_code                               4                                        1

    4                                         dis_status                              3                                         1

     

    So if i enter the MsgID 2 in the text box i will get the procedure name "get_loan_det" and this procedure name will be stored in the variable Proc_Name ... Then the function Execute_Proc will be called to execute this procedure...

    The Problem is that different Stored Procedures will be having different parameters and parameter names will also differ ..

    So how can i solve this problem ... how i can call the Stored procedure...

     

    the code i wrot in Exceute_Proc is as below

     

    '''' setting up input parameters for the proc

    For i = 0 To P_I_Fld_CNT - 1

                 param = New SqlParameter

                 param.Value = CStr(data(x))

       comand.parameters.add(param)

    Next

     

    ''' setting up output parameters for the proc

    For j = 0 To P_O_Fld_CNT - 1

    param = New SqlParameter

    param.Direction = ParameterDirection.Output

    param.Value = ""

    comand.Parameters.Add(param)

    Next

     

    objrs = objcomm.ExecuteReader(CommandBehavior.CloseConnection)

     If (objrs.Read) Then

                 MsgBox(objrs(0))

    End If

     

    But i am getting the exception "Parameter1 is not a parameter for procedure get_loandet"...

    how i can solve this

    Anybody knows ?????????

    pLZ DO HELP ME ..

    Thanx in advance

     

     

     

    Thursday, February 14, 2008 8:43 AM

All replies

  • It looks like if you don't explicitly name your parameter it takes the name "Parameter1" and if you have not defined a parameter in your sp with the name Parameter1 no match will be done.

     

    Try param = new SqlParameter("@paraname",para_type) for instance param = new SqlParameter("@para1",sqldbtype.int) instead of param = new sqlparameter()

     

    Have a look at http://msdn2.microsoft.com/nb-no/library/system.data.sqlclient.sqlparameter.sqlparameter(en-us).aspx

    for more information.

     

     

    Harry

    Thursday, February 14, 2008 9:15 AM
  • hai Harrys

    thanx for the reply .... itried doing so ... but again i am getting error saying

    "para1 is not a parameter for procedure"

     

    Thursday, February 14, 2008 9:22 AM
  • Could you show me the sp?

    Thursday, February 14, 2008 9:26 AM
  • CREATE     PROCEDURE get_loan_det @cmrc_code varchar(5),@slno varchar(16), @sal varchar(10), @ret VARCHAR(10) OUTPUT
    AS
    BEGIN
          SET NOCOUNT ON
     declare @cmrc_code1 varchar(4)

     set @ret = '-1';
     select @cmrc_code1 = cmrc_code
        from cmrccode_slno where  cmrc_code = @cmrc_code and serial_no = @slno
             if (@@ROWCOUNT <= 0) begin
                set @ret = '-1'
       end 
              else begin
                set @ret = 0
             end
      select @ret
    end
    GO

    Thursday, February 14, 2008 9:32 AM
  • The parameters you defined in your sp are

     

    @cmrc_code and @slno and @ret

     

    those are the ones you need to refer to in your code.

     

    i.e. new sqlparameter(@cmrc_code, sqldbtype char(5))

     

     

     

     

     

    Harry

     

     

     

    Thursday, February 14, 2008 9:35 AM
  • Sorry, it should be:

     

    i.e. new sqlparameter("@cmrc_code", sqldbtype.char,5 )

     

     

    Thursday, February 14, 2008 9:38 AM
  •  

    yes HarryS what u replied is correct ... thats working ...

    but each time i enter the MsgID in the text box different stored procedures are to be called

    And each stored procedure's parameter name will be different ....

    so is there any way to create and add parameters to the command without specifying the

    parameter names and datatypes??

    Thursday, February 14, 2008 9:42 AM
  • Why do you need to call a different sp every time?

     

    Thursday, February 14, 2008 9:45 AM
  • If i enter MsgID as 1 then the Stored Procedure usp_get_status is to be executed ...

    If i enter MsgID as 2 then the Stored Procedure get_loan_det is to be executed ...

    If i enter MsgID as 3 then the Stored Procedure curr_code is to be executed ...

    If i enter MsgID as 4 then the Stored Procedure dis_status is to be executed ...

    and so on... Each performs a difefrent task

     

     

    Thursday, February 14, 2008 9:52 AM
  • In this scenario I would have created one sp with a parameter that would act as a flag to signal to the sp what kind of functionality I'd be using right now.

     

    Then inside the sp I'd check for the value of this "flag" and the using a if else structure execute the right portion of the sp using the same input-parameter no-matter what kind of functionality needed for this input value on your form.

     

     

    Contact me on harrysolsem@hotmail.com if you like more details.

     

     

    Cheers

    Thursday, February 14, 2008 10:01 AM
  • I would recommend taking a look at Microsoft Application Data Block since it does have the functionality you are looking for. http://msdn2.microsoft.com/en-us/library/aa480458.aspx

     

    Wednesday, February 20, 2008 8:57 PM