setting output parameter of stored procedure in VB6.0


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: setting output parameter of stored procedure in VB6.0

  1. #1
    susan Guest

    setting output parameter of stored procedure in VB6.0


    Hi.
    What is wrong?
    =================================================================
    Private Sub cmdShowIt_Click()
    Dim par1 As ADODB.Parameter
    Dim par2 As ADODB.Parameter

    Set cmnd = New ADODB.Command
    Set cmnd.ActiveConnection = cn
    cmnd.CommandType = adCmdStoredProc
    cmnd.CommandText = "spShowIt"

    Set par1 = cmnd.CreateParameter("Input", adTinyInt, adParamInput)
    cmnd.Parameters.Append par1
    par1.Value = txtNumber.Text

    Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput)
    cmnd.Parameters.Append par2

    cmnd.Execute

    Debug.Print cmnd(1).Value
    txtName.Text = cmnd(1).Value

    txtName.Enabled = True
    Set cmnd = Nothing

    End Sub
    =====================================================================
    And the Stored procedure "spShowIt" :
    =====================================================================
    CREATE PROCEDURE spShowIt(@number tinyint,@name varchar(20) OutPut)
    AS

    SET NOCOUNT ON
    SELECT @name = Personel.Name
    FROM Personel
    WHERE Personel.NO = @number;

    RETURN @name
    GO

    Any guide please


  2. #2
    jy Guest

    Re: setting output parameter of stored procedure in VB6.0


    "susan" <iamsmahd@yahoo.com> wrote:
    >
    >Hi.
    >What is wrong?
    >=================================================================
    >Private Sub cmdShowIt_Click()
    > Dim par1 As ADODB.Parameter
    > Dim par2 As ADODB.Parameter
    >
    > Set cmnd = New ADODB.Command
    > Set cmnd.ActiveConnection = cn
    > cmnd.CommandType = adCmdStoredProc
    > cmnd.CommandText = "spShowIt"
    >
    > Set par1 = cmnd.CreateParameter("Input", adTinyInt, adParamInput)
    > cmnd.Parameters.Append par1
    > par1.Value = txtNumber.Text
    >
    > Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput)
    > cmnd.Parameters.Append par2
    >
    > cmnd.Execute
    >
    > Debug.Print cmnd(1).Value
    > txtName.Text = cmnd(1).Value
    >
    > txtName.Enabled = True
    > Set cmnd = Nothing
    >
    >End Sub
    >=====================================================================
    >And the Stored procedure "spShowIt" :
    >=====================================================================
    >CREATE PROCEDURE spShowIt(@number tinyint,@name varchar(20) OutPut)
    >AS
    >
    >SET NOCOUNT ON
    >SELECT @name = Personel.Name
    >FROM Personel
    >WHERE Personel.NO = @number;
    >
    >RETURN @name
    >GO
    >
    >Any guide please
    >

    you need to specify the size of the adVarChar-type parameter "Output"; in
    this case:

    Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput, 20)

    since the varchar datatype does not have a predefined fixed size.

    jy

  3. #3
    jy Guest

    Re: setting output parameter of stored procedure in VB6.0


    "jy" <jy_mead@hotmail.com> wrote:
    >
    >"susan" <iamsmahd@yahoo.com> wrote:
    >>
    >>Hi.
    >>What is wrong?
    >>=================================================================
    >>Private Sub cmdShowIt_Click()
    >> Dim par1 As ADODB.Parameter
    >> Dim par2 As ADODB.Parameter
    >>
    >> Set cmnd = New ADODB.Command
    >> Set cmnd.ActiveConnection = cn
    >> cmnd.CommandType = adCmdStoredProc
    >> cmnd.CommandText = "spShowIt"
    >>
    >> Set par1 = cmnd.CreateParameter("Input", adTinyInt, adParamInput)
    >> cmnd.Parameters.Append par1
    >> par1.Value = txtNumber.Text
    >>
    >> Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput)
    >> cmnd.Parameters.Append par2
    >>
    >> cmnd.Execute
    >>
    >> Debug.Print cmnd(1).Value
    >> txtName.Text = cmnd(1).Value
    >>
    >> txtName.Enabled = True
    >> Set cmnd = Nothing
    >>
    >>End Sub
    >>=====================================================================
    >>And the Stored procedure "spShowIt" :
    >>=====================================================================
    >>CREATE PROCEDURE spShowIt(@number tinyint,@name varchar(20) OutPut)
    >>AS
    >>
    >>SET NOCOUNT ON
    >>SELECT @name = Personel.Name
    >>FROM Personel
    >>WHERE Personel.NO = @number;
    >>
    >>RETURN @name
    >>GO
    >>
    >>Any guide please
    >>

    >you need to specify the size of the adVarChar-type parameter "Output"; in
    >this case:
    >
    >Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput, 20)
    >
    >since the varchar datatype does not have a predefined fixed size.
    >
    >jy

    (you would have to specify parameter size for all char, varchar, nvarchar,
    binary & varbinary parameters. jy)

  4. #4
    michael Guest

    Re: setting output parameter of stored procedure in VB6.0


    You cannot 'return @name'. You can only return integers, at least no characters.
    Remove the return line I think the procedure will work correctly.

    michael

    "jy" <jy_mead@hotmail.com> wrote:
    >
    >"jy" <jy_mead@hotmail.com> wrote:
    >>
    >>"susan" <iamsmahd@yahoo.com> wrote:
    >>>
    >>>Hi.
    >>>What is wrong?
    >>>=================================================================
    >>>Private Sub cmdShowIt_Click()
    >>> Dim par1 As ADODB.Parameter
    >>> Dim par2 As ADODB.Parameter
    >>>
    >>> Set cmnd = New ADODB.Command
    >>> Set cmnd.ActiveConnection = cn
    >>> cmnd.CommandType = adCmdStoredProc
    >>> cmnd.CommandText = "spShowIt"
    >>>
    >>> Set par1 = cmnd.CreateParameter("Input", adTinyInt, adParamInput)
    >>> cmnd.Parameters.Append par1
    >>> par1.Value = txtNumber.Text
    >>>
    >>> Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput)
    >>> cmnd.Parameters.Append par2
    >>>
    >>> cmnd.Execute
    >>>
    >>> Debug.Print cmnd(1).Value
    >>> txtName.Text = cmnd(1).Value
    >>>
    >>> txtName.Enabled = True
    >>> Set cmnd = Nothing
    >>>
    >>>End Sub
    >>>=====================================================================
    >>>And the Stored procedure "spShowIt" :
    >>>=====================================================================
    >>>CREATE PROCEDURE spShowIt(@number tinyint,@name varchar(20) OutPut)
    >>>AS
    >>>
    >>>SET NOCOUNT ON
    >>>SELECT @name = Personel.Name
    >>>FROM Personel
    >>>WHERE Personel.NO = @number;
    >>>
    >>>RETURN @name
    >>>GO
    >>>
    >>>Any guide please
    >>>

    >>you need to specify the size of the adVarChar-type parameter "Output";

    in
    >>this case:
    >>
    >>Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput, 20)
    >>
    >>since the varchar datatype does not have a predefined fixed size.
    >>
    >>jy

    >(you would have to specify parameter size for all char, varchar, nvarchar,
    >binary & varbinary parameters. jy)



  5. #5
    susan Guest

    Re: setting output parameter of stored procedure in VB6.0


    jy and michael,
    thanks alot for your efficient helps.Now my program works.
    Yes michael you were right,I removed the return line and with Jy advise
    for setting the size of Varchar variable now my program works properly
    thank you again
    Susan





    "michael" <mmayfield_NOSPAM@houston.rr.com> wrote:
    >
    >You cannot 'return @name'. You can only return integers, at least no characters.
    >Remove the return line I think the procedure will work correctly.
    >
    >michael
    >
    >"jy" <jy_mead@hotmail.com> wrote:
    >>
    >>"jy" <jy_mead@hotmail.com> wrote:
    >>>
    >>>"susan" <iamsmahd@yahoo.com> wrote:
    >>>>
    >>>>Hi.
    >>>>What is wrong?
    >>>>=================================================================
    >>>>Private Sub cmdShowIt_Click()
    >>>> Dim par1 As ADODB.Parameter
    >>>> Dim par2 As ADODB.Parameter
    >>>>
    >>>> Set cmnd = New ADODB.Command
    >>>> Set cmnd.ActiveConnection = cn
    >>>> cmnd.CommandType = adCmdStoredProc
    >>>> cmnd.CommandText = "spShowIt"
    >>>>
    >>>> Set par1 = cmnd.CreateParameter("Input", adTinyInt, adParamInput)
    >>>> cmnd.Parameters.Append par1
    >>>> par1.Value = txtNumber.Text
    >>>>
    >>>> Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput)
    >>>> cmnd.Parameters.Append par2
    >>>>
    >>>> cmnd.Execute
    >>>>
    >>>> Debug.Print cmnd(1).Value
    >>>> txtName.Text = cmnd(1).Value
    >>>>
    >>>> txtName.Enabled = True
    >>>> Set cmnd = Nothing
    >>>>
    >>>>End Sub
    >>>>=====================================================================
    >>>>And the Stored procedure "spShowIt" :
    >>>>=====================================================================
    >>>>CREATE PROCEDURE spShowIt(@number tinyint,@name varchar(20) OutPut)
    >>>>AS
    >>>>
    >>>>SET NOCOUNT ON
    >>>>SELECT @name = Personel.Name
    >>>>FROM Personel
    >>>>WHERE Personel.NO = @number;
    >>>>
    >>>>RETURN @name
    >>>>GO
    >>>>
    >>>>Any guide please
    >>>>
    >>>you need to specify the size of the adVarChar-type parameter "Output";

    >in
    >>>this case:
    >>>
    >>>Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput, 20)
    >>>
    >>>since the varchar datatype does not have a predefined fixed size.
    >>>
    >>>jy

    >>(you would have to specify parameter size for all char, varchar, nvarchar,
    >>binary & varbinary parameters. jy)

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center