SQLServer Stored Procedure Problem

[WuzZ]

Here is the table I'm working with, the primary key is an incremental ID and the table is called ‘Categories’:

CategoryID Primary Key int (4) identity seed 1, identity increment 1
CategoryName varchar (50)
Description varchar (50)

------------------------------------------------

I have created the following stored procedure to insert values into the table (the CategoryID is missing from the stored procedure as it gets created automatically).

“CREATE PROCEDURE [InsertNewCategory]

@CatName VARCHAR(50),
@Desc VARCHAR(50)

AS

BEGIN TRANSACTION

INSERT INTO [Categories]
(
CategoryName,
Description
)
VALUES
(
@CatName,
@Desc
)

COMMIT TRANSACTION”

-----------------------------------------------

I have executed the procedure in VB by using the following code:

Dim rstSave As New ADODB.Recordset
Dim mProcName As String
 
Screen.MousePointer = 11
    If ValidateSave = True Then
        If mCatID = 0 Then
            mCatName = txtCatName
            mDesc = txtDesc
            'Procedure name with parameters
            mProcName = "InsertNewCategory '" & mCatName & "', '" & mDesc & "'"
                'Executes a function in a class to execute the command
                If objSQLServer.ExecuteCommand(mProcName) = 0 Then
                    MsgBox "The new record has been saved to the database"
                End If
        End If
    End If
Screen.MousePointer = 0

The command executes perfectly but the problem I'm having is how to retrieve the CategoryID from the newly created record without having to do another check using the CategoryName and Description as criteria. Is there a way of selecting or outputting the new ID while inserting a new record?

Please help if possible, thanks.

[WuzZ]

Thanks for the tip, but I have tried many combinations of retrieving the returned ID and have had no success.

Here is the Stored Procedure as it stands:

"CREATE PROCEDURE [CAT_I_NewCategory]

@CatName VARCHAR(50),
@Desc VARCHAR(50)

AS

BEGIN TRANSACTION

INSERT INTO [Categories]
(
CategoryName,
Description
)
VALUES
(
@CatName,
@Desc
)

COMMIT TRANSACTION

SELECT@@IDENTITY"

The syntax has been checked and is ok.

---------------------

Here is the class I'm using:

Public Function OpenRecordset(ByRef pRecordSet As ADODB.Recordset, pSQLString As String) As Long
 
    On Error GoTo Error
    
    If Cnn.State = adStateOpen Then
        
        pRecordSet.CursorLocation = adUseClient
        Cnn.CursorLocation = adUseClient
        
        pRecordSet.Open pSQLString, Cnn, adOpenDynamic, adLockReadOnly, adCmdText
        
        OpenRecordset = pRecordSet.RecordCount
    Else
        OpenRecordset = 0
    End If
    
    Exit Function
    
Error:
    HandleADOError ("OpenRecordset " & "<recordset>, '" & pSQLString & "'")
    If pRecordSet.State = adStateOpen Then
        pRecordSet.Close
    End If
 
    OpenRecordset = 0
 
End Function

-----------------------------------------

And here is how I’ve referred to the class in a form:

Dim rstSave As New ADODB.Recordset
Dim mProcName As String
 
Screen.MousePointer = 11
    If ValidateSave = True Then
        'If it is a new record
        If mCatID = 0 Then
            mCatName = txtCatName
            mDesc = txtDesc
            mProcName = "CAT_I_NewCategory '" & mCatName & "', '" & mDesc & "'"
                'If the insert procedure was executed correctly
                If objSQLServer.OpenRecordset(rstSave, mProcName) Then
                    mCatID = rstSave.Fields(0).Value
                    MsgBox mCatID
                    MsgBox "The new record has been saved to the database", vbInformation + vbOKOnly, "Record Saved"
                End If

When I execute the OpenRecordset function, the new record is inserted into the database, but it seems unable to find any data to insert into the specified recordset object and thus returns an error. I’m completely stumped as to how I can send a parameter back from the stored procedure into a recordset which can then populate a variable.

[RSINGH]

OK

try the following line in your class instead of returning the recordcount:

OpenRecordset = pRecordSet.fields(0).value

That way the class will return the value that you need and then you can assign that to m_CatID as

m_CatID = objSQLServer.OpenRecordset(rstSave, mProcName)

in your form.

Basically, it looks like your variables are losing scope or something. I haven't tested this but I think it should work. Just shout if it doesn't and I will write it for you. (you have 1 hour b4 I go home!)

[WuzZ]

I get the following error:

Run-time error '3265'

Item cannot be found in the collection corresponding to the requested name or ordinal.

at this line: OpenRecordset = pRecordSet.Fields(0).Value

Any thoughts?

[RSINGH]

Firstly try putting SELECT @@IDENTITY before the COMMIT.

I'll start checking the rest of the code. Let us know either way.

Also, try running your sp in t-sql and see if it returns the value you need. This will allow us to eliminate whether the error is caused by the sp or VB.

[WuzZ]

Ok, I've tried placing the "SELECT @@IDENTITY" before the "COMMIT TRANSACTION" and it says Incorrect Syntax. So I tried doing a simple "COMMIT" after the "SELECT @@IDENTITY" but then I get an unknown error on the following VB line:

pRecordSet.Open pSQLString, Cnn, adOpenDynamic, adLockReadOnly, adCmdText

I'm afraid I'm not familiar with Transact SQL, reading up on it as I type this.

[RSINGH]

Running short of time, so here's another way.

Sub test()

Dim rstSave As ADODB.Recordset
Dim mProcName As String

Set rstSave = New ADODB.Recordset

Screen.MousePointer = 11
    If ValidateSave = True Then
        'If it is a new record
        If mCatID = 0 Then
            mCatName = txtCatName
            mDesc = txtDesc
            mProcName = "CAT_I_NewCategory '" & mCatName & "', '" & mDesc & "'"
            
            'GET A CONNECTION AND OPEN IT HERE
            '
            '
            With rstSave
                .Open mProcName, TheConnectionThatYouSet
                mCatID = .Fields(0).Value
                MsgBox mCatID
                .Close
            End With
            Set rstSave = Nothing
            ''Close and set your connections to nothing
        End If
    End If
End Sub

Run that from the form instead of using the OpenRecordset function. It works for me

[RSINGH]

When the SELECT @@IDENTITY was after the commit, did you try running the sp in query analyzer and getting a result?

[WuzZ]

Thanks, I’ll try that code in a minute.

I executed the stored procedure in the Query Analyzer as below:

EXECUTE CAT_I_NewCategory 'HELLO', 'HELLO'

And it simply said : (1 row(s) affected) in the results window.

[WuzZ]

News Update

Ok, I've tried the SELECT @@IDENTITY before the COMMIT again and then executed the procedure in the query analyzer and it works (returns the last generated ID), BUT when I try it in VB through the OpenRecordset function I get an error. At least now I know its VB thats the culprit.

[idemkovitch]

in ADO you usually want start SP like this:
(this way you have all advantages)

Dim StProc As Command
Dim Param As ADODB.Parameter

Set StProc = New Command
With StProc
.ActiveConnection = WorkConnection

.CommandText = "SPNAME"

.CommandType = adCmdStoredProc
'your parameters
.Parameters(1).Value = MaintType
.Parameters(2).Value = AddrKey
.Parameters(3).Value = LineNumber
.Parameters(4).Value = MiscellType
.Parameters(5).Value = Frequency
.Parameters(6).Value = Amount
.Parameters(7).Value = InceptionDate

.Execute

'here you return parameter from SP
Return = .Parameters("@_oReturn")

End With

[WuzZ]

Ok I finally got it working. It was in actual fact, the stored procedure that was the problem. It returned two values: (1) rows affected (from the insert record) and then the @@IDENTITY value. The problem was that VB was looking at the rows affected string and not the ID. The way to get round this is by inserting the following line in the stored procedure: SET NOCOUNT ON.

So now the stored procedure looks like this:

CREATE PROCEDURE [CAT_I_NewCategory]

@CatName VARCHAR(50),
@Desc VARCHAR(50)

AS

SET NOCOUNT ON

BEGIN TRANSACTION

INSERT INTO [Categories]
(
CategoryName,
Description
)
VALUES
(
@CatName,
@Desc
)

SELECT [CategoryID] FROM [Categories] WHERE [CategoryID] = @@IDENTITY

COMMIT TRANSACTION

Executing this in the query analyser returns the CategoryID column and nothing else so now it works in VB.

Thanks for your help RSINGH and Idemkovitch, appreciated.

[Mongo]

A couple thoughts...

The fields(0).value is the RETURN_VALUE of a stored procedure.
To return a return_value, you have to designate it in your sp
and then construct the applicable objects in VB to reference it.

For example:

CREATE PROCEDURE [CAT_I_NewCategory] 
  @CatName VARCHAR(50), 
  @Desc VARCHAR(50) 
AS 
SET NOCOUNT ON 
INSERT INTO [Categories] 
( CategoryName, 
  Description ) akistan.

64.215.87.3

¨Tlþ;µ“
AndreexÉCSQL Query question¡;b
HI... Thanks for helping me!!!

I have this Query....

select area 
from areas
where area not like (select area from oldareas)

But it gives me an error saying that more than one record can be
returned....

What I want to do.... is get the areas.area but only the ones that are not in the oldareas.area..... how can I do this??? Thank you!
:)

148.235.120.11
òTlþ<´“
filburt1ãŸ¡;¿I knew I forgot stuff:

8x burner (I don't care about the speed)
10x DVD
100MB ZIP drive
Floppy *snicker*

I only have one internal and one external 3.5" bay open (no 5.25"s are open).

63.73.156.12
bTlþ=•“
dimava„!*¡;0what do you use to enter the info into the grid?


24.18.203.163


%Tnþ@µ“
vbgladiatorxGö¡;ðMaybe like this:

select area 
from areas 
where area not like (select area from oldareas Where area = some area)

I think the other select statement might return more thanentity
IF ( @@ERROR = 0 ) 
  SELECT @@IDENTITY AS 'NewIdentity'
ELSE
  SELECT -1 AS 'NewIdentity'
-- both selects above return a single field recordset
RETURN @@ERROR


' And in VB:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With New ADODB.Connection
 .ConnectionString = TheConnectionThatYouSet
 .Open
 .CAT_I_NewCategory txtCatName, txtDesc, rs
  ' fields collection is zero-based
  MsgBox rs(0).Value
  rs.Close
  .Close
End With
Set rs = Nothing

Hope this helps.



Note: In my original post, I incorrectly included "BEGIN"
after IF ( @@ERROR = 0 ) This edit removed that. 9/14/01

[WuzZ]

You're right about the SELECT @@IDENTITY as it does use less resources, however I tried to write the procedure you suggested and couldn't help but notice that BEGIN TRANSACTION and COMMIT TRANSACTION were missing. I was under the impression that statements such as COMMIT were required when changing data within a database. Please correct me if I'm wrong.

[Mongo]

A transaction is a single unit of work. When a transaction is successful, all data
modifications made are committed. When a transaction encounters errors and must be
canceled or rolled back, then all data modifications are dropped/erased.

There are basicly three generic types of transactions. Depending upon your reference,
the naming for these can be most confusing. These are modified Microsoft definitions:

1. Autocommit transactions where each individual statement is a transaction.
Immediately the confusion begins, because several sources also refer to this type
as "implicit" or "implied" transactions.

2. Explicit transactions where each transaction is explicitly started with the BEGIN
TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement. These
are is also called "user-initiated" or "SQL-initiated" transactions.

3. Implicit transactions are where a new transaction is implicitly started when the prior
transaction completes, but each transaction is explicitly completed with a COMMIT or
ROLLBACK statement. This mode is typically controlled and set by the owning DBA. The
statement "SET IMPLICIT_TRANSACTIONS {ON | OFF}" can also change this setting. This
setting is usually set OFF, which then defaults processing to autocommit mode.

Hopefully you won't get tripped up in specific names for these or how many types there are.
Even articles in MSDN use naming conventions different from their SQL Server Online Books.

At the 100,000-foot theoretical level, every unit of database server work uses and requires
transactional processing. So at this level, the answer to your question is yes - the use
of BEGIN TRANS and COMMIT (or ROLLBACK, which you omitted to mention) is required.

At the 5,000-foot server level, if your DBA uses IMPLICIT_TRANSACTIONS ON, then the answer
to half your question is yes - the use of COMMIT or ROLLBACK is required.

At ground-zero, user-initiated transactions can be sticky wickets. If you truly know what
you're doing and have a need or spec to design toward nested transaction processing, then
using them is a given and the answer to you question remains yes. These require careful,
precise design.

However, with the info and code you've thus provided, I felt it best to steer you toward
using an implicit transaction. Note my bias! Grandpa has pretended to grow up using the
term implicit transaction for what I just "defined per MS verbage" above as an autocommit
transaction. I have assumed your DBA uses the standard IMPLICIT_TRANSACTIONS OFF setting.

Rows are locked inside a transaction. No other user can fetch, access or update these rows;
and depending upon how you choose to connect to your server, the use of a BEGIN TRANS can
actually lock the entire table or data page(s) you use.

Let's look at your SP using an explicit transaction. The BEGIN TRANS starts a transaction.
Next an INSERT is initiated. What happens when this chokes? It throws an error. Next it
trys to COMMIT, which throws another error. The result is your SP completes without ever
COMMITing the transaction. *Snickers* I've also seen folks RETURN from inside an explicit
transaction, without COMMIT, which achieves the same result. If it's not readily apparent
what this pretzel logic does, re-read the paragraph above...

Now, if you can or choose to use an SP with autocommit/implied/"my def" implicit transaction
processing: The implied BEGIN TRANS is part of the INSERT. What happens when it chokes?
The INSERT throws and error to the transaction and automatically includes a ROLLBACK for the
INSERT transaction and the SP completes with an @@ERROR code.

FWIW, as a rule of thumb, I tend not to use an explicit transaction for a single INSERT. But
then, there are exceptions to every rule! *Smirk*

Hopefully this makes some sense and helps.