[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL 2008 Stored Procedure Problem with VB6

Posted on 2011-10-20
15
Medium Priority
?
669 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:TopCatOnyx
  • 6
  • 4
  • 2
  • +2
15 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37003190
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37003202
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37003223
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 26

Expert Comment

by:tigin44
ID: 37003230
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
 

Author Comment

by:TopCatOnyx
ID: 37003414
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
 
LVL 11

Expert Comment

by:kbirecki
ID: 37003949
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
 

Author Comment

by:TopCatOnyx
ID: 37006715
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
 
LVL 11

Expert Comment

by:kbirecki
ID: 37008277
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
 

Author Comment

by:TopCatOnyx
ID: 37008499
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 37009061
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
 

Author Comment

by:TopCatOnyx
ID: 37009417
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
 

Author Comment

by:TopCatOnyx
ID: 37009449
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 37009725
>>SQL doesn't like the ( value int) in the DECLARE @t ( value int )<<
It should be:
DECLARE @t table ( value int )

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 37009732
>>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
 

Author Closing Comment

by:TopCatOnyx
ID: 37009797
Great job (as usual) Angellll and acperkins.
Thanks to both of you for your help!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question