Store procedure


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Store procedure

  1. #1
    Join Date
    Mar 2005
    Posts
    110

    Store procedure

    Hi,Good Morning,

    I am learning how to write stored procedure. Now I got a question .

    in VB6 I create a stored Procedure

    Here is my code

    strsql = "create procedure USP_Getmaxsequence" & _
    "(@MaxsequenceNo Nvarchar output) as " & _
    "select @MaxsequenceNO=max(right(SEQUCENO,5)) from mytable"

    Set Cmd = CreateObject("adodb.command")
    With Cmd
    .ActiveConnection = ocnn
    .CommandText = strsql
    .Execute
    End With

    It exist in the Stored procedure list.

    Now I call USP_Getmaxsequence:

    With Ocmd
    .CommandText = "USP_Getmaxsequence"
    .CommandType = adCmdStoredProc
    End With
    Set param = Ocmd.CreateParameter("ReturnValue", adInteger, adParamReturnValue, 0)
    Ocmd.Parameters.Append param
    Set param = Ocmd.CreateParameter("MaxsequenceNo", adVarChar, adParamOutput, 1)
    Ocmd.Parameters.Append param
    Ocmd.Execute

    Debug.Print Ocmd.Parameters(0)
    Debug.Print Ocmd.Parameters(1).Value

    I got the output value (Ocmd.Parameters(1).Value) is "0", not the right value.

    if I directly use "sql"(select max(right(SEQUCENO,5)) from mytable) In vb6, the result is right, like "00037"

    anybody knows how to write this simple stored procedure to get the right result. such as 2000001,1000002, I need to get "00002".

    thanks

    Judy

  2. #2
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    Code:
    Set param = Ocmd.CreateParameter("@MaxsequenceNo", adVarChar, adParamOutput, 1)
    Note the at-sign.

    Rune
    If you hit a brick wall, you didn't jump high enough!

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