SQL 2008 Stored Procedure Problem with VB6

I have the small SQL test stored procedure and the short VB6 code to test the procedure - both shown in the Code Section.  If I run the stored procedure in the query analyzer it runs without errors, however doesn't increment the SubPtr field.  When I try running from VB, the systems hangs at the dbData.Execute command.  I'm trying something I thought would be simple to get my feet wet with stored procedures - Ha,Ha!  I want to be able to use a stored procedure to increment a field value and call the stored procedure from VB.
Any help would be greatly appreciated!
 
SET QUOTED_IDENTIFIER ON
GO

   CREATE PROCEDURE [dbo].[sp_RJS_Inc_SPtr_2] AS

   BEGIN
     Declare @lMax INT
     Declare @lSPtr INT

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

     Select @lSptr = (Select SubPtr) FROM dbo.AC89_East_PTR
     SELECT @lMax = (Select PtrMax) FROM dbo.AC89_East_PTR  
     BEGIN
       IF @lSptr >= @lMax
         BEGIN
           SET @lSptr = 0
         End
       Else
	 BEGIN
	   Set @lSPtr=@lSPtr+1
	 END
     End
     UPDATE AC89_East_PTR  SET SubPtr=@lSptr
     COMMIT
   End


Private Sub cbSQLTest_Click()
    Dim sCont As String
    Dim lPtr As Long
    Dim lPtr2 As Long

    sCont = "SELECT * FROM AC89_East_Ptr"

    Set rsActPtr = New ADODB.Recordset
    With rsActPtr
        .CursorLocation = adUseServer                   
        .CursorType = adOpenDynamic
        .LockType = adLockPessimistic
        .Open sCont, dbData, , , adCmdText
        lPtr = .Fields("SubPtr")
        txtSQLTest.Text = lPtr
        sCont = "EXEC sp_RJS_Inc_SPtr_2"
        dbData.Execute sCont
        lPtr2 = .Fields("SubPtr")
        txtSQLAfter.Text = lPtr2
        .Close
    End With
    Set rsActPtr = Nothing

End Sub

Open in new window

TopCatOnyxAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see I posted untested code ...

SET QUOTED_IDENTIFIER ON
GO
   CREATE PROCEDURE [dbo].[sp_RJS_Inc_SPtr_2] 
   ( @SubPrt int OUTPUT )
   AS
   BEGIN
    DECLARE @t ( value int )

    UPDATE AC89_East_PTR 
        SET SubPtr= CASE WHEN SubPtr >= PtrMax THEN 0 ELSE SubPtr + 1 END
    OUTPUT INSERTED.SubPtr INTO @t 
    COMMIT

    SELECT @SubPrt = value FROM @t
   End

Open in new window


and indeed in the vb code there is a typo in regards to that:

Private Sub cbSQLTest_Click()
    Dim cmdProc as ADODB.Command
    Dim lPtr As Long
    Dim prmSubPrt as ADODB.Parameter

    Set cmdProc = New ADODB.Command
    With cmdProc 
      set .ActiveConnection = dbData  
      .CommandType = adStoredProc
      .CommandText = "sp_RJS_Inc_SPtr_2"
      set prmSubPtr = .CreateParameter("@SubPrt", adInteger)
      prmSubPtr.Direction = adParamOutput
      .Parameters.Add prmSubPtr
      .Execute

      lPtr2 = prmSubPtr.Value
    End With
End Sub

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's first change the procedure to simplify it:
SET QUOTED_IDENTIFIER ON
GO
   CREATE PROCEDURE [dbo].[sp_RJS_Inc_SPtr_2] 
   AS
   BEGIN
    UPDATE AC89_East_PTR 
        SET SubPtr= CASE WHEN SubPtr >= PtrMax THEN 0 ELSE SubPtr + 1 END
    COMMIT
   End

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and your issue is/was that your procedure was waiting for the command on the recordset to be completed because of the serializable ...

so, you want to increment the value, and get the new value returned?!


SET QUOTED_IDENTIFIER ON
GO
   CREATE PROCEDURE [dbo].[sp_RJS_Inc_SPtr_2] 
   ( @SubPrt int OUTPUT )
   AS
   BEGIN
    UPDATE AC89_East_PTR 
        SET SubPtr= CASE WHEN SubPtr >= PtrMax THEN 0 ELSE SubPtr + 1 END
    OUTPUT INSERTED.SubPtr INTO @SubPrt
    COMMIT
   End

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and your VB code is then:
Private Sub cbSQLTest_Click()
    Dim cmdProc as ADODB.Command
    Dim lPtr As Long
    Dim prmSubPrt as ADODB.Parameter

    Set cmdProc = New ADODB.Command
    With cmdProc 
      set .ActiveConnection = dbData  
      .CommandType = adStoredProc
      .CommandText = "sp_RJS_Inc_SPtr_2"
      set prmSubPtr = .CreateParameter("@subPtr", adInteger)
      prmSubPtr.Direction = adParamOutput
      .Parameters.Add prmSubPtr
      .Execute

      lPtr2 = prmSubPtr.Value
    End With
End Sub

Open in new window

0
 
tigin44Commented:
try this procedure
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_RJS_Inc_SPtr_2] AS

BEGIN

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	
	Declare @lMax INT
	Declare @lSPtr INT

	BEGIN TRY
		BEGIN TRANSACTION
			SELECT @lSptr = SubPtr, @lMax = PtrMax FROM dbo.AC89_East_PTR  

			IF @lSptr >= @lMax
			BEGIN
			   SET @lSptr = 0
			End
			Else
			BEGIN
				Set @lSPtr=@lSPtr+1
			END

			UPDATE AC89_East_PTR  
			SET SubPtr=@lSptr
			
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END

Open in new window

0
 
TopCatOnyxAuthor Commented:
Hi AngelIII
I get the following SQL error for your simplified SP:
Msg 1087, Level 16, State 1, Procedure sp_RJS_Inc_SPtr_3, Line 7
Must declare the table variable "@SubPrt".
0
 
kbireckiCommented:
I think AngelIII meant @SubPtr in...

OUTPUT INSERTED.SubPtr INTO @SubPrt

Open in new window


It was just a typo (use @SubPtr instead of @SubPrt).
0
 
TopCatOnyxAuthor Commented:
Hi kbirecki,
@SubPtr isn't declared anywhere, so I get:
Msg 1087, Level 15, State 2, Procedure sp_RJS_Inc_SPtr_3, Line 7
Must declare the table variable "@SubPtr".
when I use:
      OUTPUT INSERTED.SubPtr INTO @SubPtr

If I change the procedure as shown in the code I don't get any errors when creating the procedure, however if I execute the procedure:
    exec sp_RJS_Inc_SPtr_3
I get the error:
   Msg 201, Level 16, State 4, Procedure sp_RJS_Inc_SPtr_3, Line 0
   Procedure or function 'sp_RJS_Inc_SPtr_3' expects parameter '@SubPrt', which was not supplied.



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

   ALTER PROCEDURE [dbo].[sp_RJS_Inc_SPtr_3] 
   ( @SubPrt int OUTPUT )
   AS
   BEGIN
    UPDATE AC89_East_PTR 
        SET SubPtr= CASE WHEN SubPtr >= PtrMax THEN 0 ELSE SubPtr + 1 END
    OUTPUT @SubPrt 
/*	OUTPUT INSERTED. @SubPrt INTO AC89_Cent_PTR */
	Begin
      COMMIT
    end
   End

Open in new window

0
 
kbireckiCommented:
I think I see what happened.  The first example angellIII showed has the defined variable in the output parameter, but not I'm tyre second one.  I'm away from my computer right now so I can't try anything more specific til later.
0
 
TopCatOnyxAuthor Commented:
My ultimate goal of the stored procedure is to have the procedure increment the SubPtr field then test to see if the field is >= PtrMax,  if so, set SubPtr = 0 and return the value of the SubPtr field.  I need to call the stored procedure from VB6 and use the returned value for further VB6 processing.
0
 
TopCatOnyxAuthor Commented:
SQL doesn't like the ( value int) in the DECLARE @t ( value int )
I get incorrect syntax near '(' and
incorrect syntax near 'value'
also it doesn't think @t is declared
and I get invalid column name 'value'
in SELECT @SubPtr = value FROM @t
also the @t (above) isn't declared.
0
 
TopCatOnyxAuthor Commented:
On the VB side, on the statement:
      .Parameters.Add prmSubPtr
the .add gives a compile error - "Method or Data Member not found"
I changed it to:
     .Parameters.Append prmSubPtr
and it compiles OK
lPtr always shows a value of 0 - maybe that is what the SP is returning?
Also the .commandType doesn't work - the adStoredProc is empty. Not sure why.
0
 
Anthony PerkinsCommented:
>>SQL doesn't like the ( value int) in the DECLARE @t ( value int )<<
It should be:
DECLARE @t table ( value int )

0
 
Anthony PerkinsCommented:
>>I changed it to:
     .Parameters.Append prmSubPtr<<
That is correct.  In ADO.NET the method is called Add.

>>Also the .commandType doesn't work - the adStoredProc is empty. Not sure why.<<
It should be adCmdStoredProc
0
 
TopCatOnyxAuthor Commented:
Great job (as usual) Angellll and acperkins.
Thanks to both of you for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.