Create Record w/ and Catch Identity Value w/ .Net 1

[flaniganmj]

I use the below often; however, I have been unable to convert it to VB.NET. Mainly, I want to create a new record and capture the int identity record value for further use.

        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim strcn As String
        Dim strsql As String

        Try
            strcn = "provider=sqloledb;integrated security=SSPI;data source=LONGS;initial catalog=AFD"
            strsql = "select * from tcmm_creditmemo where creditmemokey=0"

            With cn
                .ConnectionString = strcn
                .ConnectionTimeout = 1000
                .Open()
            End With

            rs.Open(strsql, cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

            With rs
                .AddNew()
                .Fields("InvNo").Value = ""
                .Update()
                CreateNewRecord = .Fields("CreditMemoKey").Value
            End With

        Catch x As Exception
            MsgBox(x.Message, mbtErr, sErrTitle)
            CreateNewRecord = 0

        Finally
            rs = Nothing
            cn = Nothing

        End Try

I have not been able to find anything in these forums; so, any help (or pointing me in the right direction) would be appriciated.



Regards,

mjf

 

[chrissie1]

I don't think you will find much here about adodb since it's an obsolete technology.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[flaniganmj]

I understand and agree. I am looking to convert it.



Regards,

mjf

 

[SBendBuckeye]

Why not return the new primary key value as an output parm from your create stored proc?

As an example, say you have a table named GLWTestTable which contains 3 fields, ID which is int identity, FName which is varchar(50) and LName which is varchar(50).

The following Stored Procedure can be created using QueryAnalyzer:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GLWTestTableInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GLWTestTableInsert]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE GLWTestTableInsert
    (@FName varchar(50),
     @LName varchar(50), 
     @NewKey int OutPut)
AS

    INSERT INTO [dbo].[GLWTestTable]
        (FName, LName)
        VALUES(@FName, @LName)

    SELECT @NewKey = ID
    FROM GLWTestTable
    WHERE FName = @FName 
      AND LName = @LName

    RETURN @NewKey

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Then run this to see the results:

Declare @lOutPut int
EXEC GLWTestTableInsert 'Yipee', 'Yahoo', @lOutput Output
SELECT @lOutput 

-- This will show that you really did get the last one
Select * From GLWTestTable
OrderBy ID Desc

If you need some help with the VB.Net code to actually run the stored procedure, etc feel free to post again. Good Luck!


Have a great day!

 

[chrissie1]

Very nice SP. Have a star.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]