Getting IDENTITY value of newly inserted row

Getting IDENTITY value of newly inserted row

Post by Xin L » Wed, 25 Jul 2001 12:24:11



Hi,

I have a quick question.  How can I get the identity value of a newly
inserted row using the Recordset object.  Here is what I am trying right
now:

        Set toRS = New Recordset
        toRS.Open "PMSUsers", oDb.CN, adOpenForwardOnly, adLockOptimistic
        toRS.AddNew
        toRS("UsrLoginID") = strEncUsername
        toRS("UsrPassword") = strEncPassword
        toRS("CustomerID") = CustomerID
        toRS.Update
        AddUser = toRS("UsrID") 'Return New UserID to calling function

The UsrID column is defined as an IDENTITY column in SQL.  However, it's
always returning 0 for some reason, and according to SQL Server, new ids are
being correctly generated.  Any help would be appreciated.  Thanks.

Xin Li

 
 
 

Getting IDENTITY value of newly inserted row

Post by Carl Prothma » Wed, 25 Jul 2001 15:28:18


Xin,
Try changing your Recordset Open to:

oRs.Open "PMSUsers", oDb.CN, adOpenKeyset, adLockOptimistic, adCmdTableDirect

Note: using the Recordset Addnew / Update is the slowest way to add a new record.  The faster way
is to use ADO's Command and Parameter objects and call a Stored Procedure which does the insert and
returns the new Identity value via an output parameter.

For example, given the following Stored Procedure for the Northwind SQL Server database

'*****************
Create Procedure spEmployees_Insert
(


















)
AS
    INSERT INTO [Employees]
    (
        [LastName],
        [FirstName],
        [Title],
        [TitleOfCourtesy],
        [BirthDate],
        [HireDate],
        [Address],
        [City],
        [Region],
        [PostalCode],
        [Country],
        [HomePhone],
        [Extension],
        [Photo],
        [Notes],
        [ReportsTo],
        [PhotoPath]
    )
    VALUES
    (

















    )


GO
'*****************

Here is how you would use ADO to get call and get the new Identity value

'*****************
    ' Create and open a new Connection object
    Set oConn = New ADODB.Connection
    oConn.Open "Provider=SQLOLEDB;" & _
                 "Data Source=(local);" & _
                 "Initial Catalog=NorthWind;" & _
                 "User ID=sa;" & _
                 "Password=;"

    ' Create a new Command object
    Set oCmd = New ADODB.Command
    With oCmd

        ' Set Command properties
        .ActiveConnection = oConn
        .CommandType = adCmdStoredProc
        .CommandText = "spEmployees_Insert"

        ' Create Command Parameter(s)
        .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4)
        .Parameters.Append .CreateParameter("EmployeeID", adInteger, adParamOutput, 4)
        .Parameters.Append .CreateParameter("LastName", adVarWChar, adParamInput, 20, sLastName)
        .Parameters.Append .CreateParameter("FirstName", adVarWChar, adParamInput, 10, sFirstName)
        .Parameters.Append .CreateParameter("Title", adVarWChar, adParamInput, 30, vTitle)
        .Parameters.Append .CreateParameter("TitleOfCourtesy", adVarWChar, adParamInput, 25,
vTitleOfCourtesy)
        .Parameters.Append .CreateParameter("BirthDate", adDBTimeStamp, adParamInput, 8,
vBirthDate)
        .Parameters.Append .CreateParameter("HireDate", adDBTimeStamp, adParamInput, 8, vHireDate)
        .Parameters.Append .CreateParameter("Address", adVarWChar, adParamInput, 60, vAddress)
        .Parameters.Append .CreateParameter("City", adVarWChar, adParamInput, 15, vCity)
        .Parameters.Append .CreateParameter("Region", adVarWChar, adParamInput, 15, vRegion)
        .Parameters.Append .CreateParameter("PostalCode", adVarWChar, adParamInput, 10,
vPostalCode)
        .Parameters.Append .CreateParameter("Country", adVarWChar, adParamInput, 15, vCountry)
        .Parameters.Append .CreateParameter("HomePhone", adVarWChar, adParamInput, 24, vHomePhone)
        .Parameters.Append .CreateParameter("Extension", adVarWChar, adParamInput, 4, vExtension)
        .Parameters.Append .CreateParameter("Photo", adLongVarBinary, adParamInput, 16, vPhoto)
        .Parameters.Append .CreateParameter("Notes", adLongVarWChar, adParamInput, 16, vNotes)
        .Parameters.Append .CreateParameter("ReportsTo", adInteger, adParamInput, 4, vReportsTo)
        .Parameters.Append .CreateParameter("PhotoPath", adVarWChar, adParamInput, 255, vPhotoPath)

        ' Run the command (perform the Insert)
        .Execute , , adExecuteNoRecords

        ' Get the new Identity value
        lEmployeeID = .Parameters("EmployeeID").Value
    End With
'*****************

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com


> Hi,

> I have a quick question.  How can I get the identity value of a newly
> inserted row using the Recordset object.  Here is what I am trying right
> now:

>         Set toRS = New Recordset
>         toRS.Open "PMSUsers", oDb.CN, adOpenForwardOnly, adLockOptimistic
>         toRS.AddNew
>         toRS("UsrLoginID") = strEncUsername
>         toRS("UsrPassword") = strEncPassword
>         toRS("CustomerID") = CustomerID
>         toRS.Update
>         AddUser = toRS("UsrID") 'Return New UserID to calling function

> The UsrID column is defined as an IDENTITY column in SQL.  However, it's
> always returning 0 for some reason, and according to SQL Server, new ids are
> being correctly generated.  Any help would be appreciated.  Thanks.

> Xin Li


 
 
 

Getting IDENTITY value of newly inserted row

Post by VOrte » Fri, 27 Jul 2001 21:25:17


Well Carl that all looks simple enough?? ...  ummm what is the
actual difference using >>

Open,update and your method do you think in milleseconds?

Really I mean that all looks nice but is it neccessary?

It would take me 10 minutes just to write it all...

Ummm 30 seconds ..... this took

Access ... I haven't done SqlServer for a while so

Function AddContactReturnId () as Long

Dim strSql as string
Dim rsTemp as string
Dim intX as integer
Dim LngId as long

Set rsTemp = new recordset

With rsTemp
    strSql = "SELECT * FROM tblContacts WHERE  Id = Widget"
    .addnew
    For intX = 0 to 15
        .fields.item(intX).value = text1(intx).text
    Next Intx
    .update
     LngId = .fields.item("Id").value
    .close
End With

Set rsTemp =nothing

AddContactReturnId = LngId

End Function

 Of course ... I don't say using THE Fields index NUMBER  is correct but
sometimes ......   [8^ )

Some really serious questions though to you!

Is this really the way you do everything ... create an update procedure?
Can't you just have one in SqlServer and leave it there?
What about a full example .... including how you call it, how you pass the
values to the
parameters. >> This is intended for study purposes  : )

What are you passing an object ... I  guess as data validation must have
been carried out

Come on  .... give me a full example ..... I am a developing VB JEDI with no
Master

LMHO

 Regards

VOrtex

"Carl Prothman" <car...@spamcop.net> wrote in message

news:eIkAznAFBHA.1740@tkmsftngp02...
> Xin,
> Try changing your Recordset Open to:

> oRs.Open "PMSUsers", oDb.CN, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

> Note: using the Recordset Addnew / Update is the slowest way to add a new

record.  The faster way
> is to use ADO's Command and Parameter objects and call a Stored Procedure

which does the insert and
> returns the new Identity value via an output parameter.

> For example, given the following Stored Procedure for the Northwind SQL
Server database

> '*****************
> Create Procedure spEmployees_Insert
> (
>     @EmployeeID int Output,
>     @LastName nvarchar(20),
>     @FirstName nvarchar(10),
>     @Title nvarchar(30),
>     @TitleOfCourtesy nvarchar(25),
>     @BirthDate datetime ,
>     @HireDate datetime ,
>     @Address nvarchar(60),
>     @City nvarchar(15),
>     @Region nvarchar(15),
>     @PostalCode nvarchar(10),
>     @Country nvarchar(15),
>     @HomePhone nvarchar(24),
>     @Extension nvarchar(4),
>     @Photo image,
>     @Notes ntext,
>     @ReportsTo int,
>     @PhotoPath nvarchar(255)
> )
> AS
>     INSERT INTO [Employees]
>     (
>         [LastName],
>         [FirstName],
>         [Title],
>         [TitleOfCourtesy],
>         [BirthDate],
>         [HireDate],
>         [Address],
>         [City],
>         [Region],
>         [PostalCode],
>         [Country],
>         [HomePhone],
>         [Extension],
>         [Photo],
>         [Notes],
>         [ReportsTo],
>         [PhotoPath]
>     )
>     VALUES
>     (
>         @LastName,
>         @FirstName,
>         @Title,
>         @TitleOfCourtesy,
>         @BirthDate,
>         @HireDate,
>         @Address,
>         @City,
>         @Region,
>         @PostalCode,
>         @Country,
>         @HomePhone,
>         @Extension,
>         @Photo,
>         @Notes,
>         @ReportsTo,
>         @PhotoPath
>     )

>     SELECT @EmployeeID = @@IDENTITY
> GO
> '*****************

> Here is how you would use ADO to get call and get the new Identity value

> '*****************
>     ' Create and open a new Connection object
>     Set oConn = New ADODB.Connection
>     oConn.Open "Provider=SQLOLEDB;" & _
>                  "Data Source=(local);" & _
>                  "Initial Catalog=NorthWind;" & _
>                  "User ID=sa;" & _
>                  "Password=;"

>     ' Create a new Command object
>     Set oCmd = New ADODB.Command
>     With oCmd

>         ' Set Command properties
>         .ActiveConnection = oConn
>         .CommandType = adCmdStoredProc
>         .CommandText = "spEmployees_Insert"

>         ' Create Command Parameter(s)
>         .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,

adParamReturnValue, 4)
>         .Parameters.Append .CreateParameter("EmployeeID", adInteger,
adParamOutput, 4)
>         .Parameters.Append .CreateParameter("LastName", adVarWChar,

adParamInput, 20, sLastName)
>         .Parameters.Append .CreateParameter("FirstName", adVarWChar,

adParamInput, 10, sFirstName)
>         .Parameters.Append .CreateParameter("Title", adVarWChar,

adParamInput, 30, vTitle)
>         .Parameters.Append .CreateParameter("TitleOfCourtesy", adVarWChar,
adParamInput, 25,
> vTitleOfCourtesy)
>         .Parameters.Append .CreateParameter("BirthDate", adDBTimeStamp,
adParamInput, 8,
> vBirthDate)
>         .Parameters.Append .CreateParameter("HireDate", adDBTimeStamp,

adParamInput, 8, vHireDate)
>         .Parameters.Append .CreateParameter("Address", adVarWChar,

adParamInput, 60, vAddress)
>         .Parameters.Append .CreateParameter("City", adVarWChar,

adParamInput, 15, vCity)
>         .Parameters.Append .CreateParameter("Region", adVarWChar,

adParamInput, 15, vRegion)
>         .Parameters.Append .CreateParameter("PostalCode", adVarWChar,
adParamInput, 10,
> vPostalCode)
>         .Parameters.Append .CreateParameter("Country", adVarWChar,

adParamInput, 15, vCountry)
>         .Parameters.Append .CreateParameter("HomePhone", adVarWChar,

adParamInput, 24, vHomePhone)
>         .Parameters.Append .CreateParameter("Extension", adVarWChar,

adParamInput, 4, vExtension)
>         .Parameters.Append .CreateParameter("Photo", adLongVarBinary,

adParamInput, 16, vPhoto)
>         .Parameters.Append .CreateParameter("Notes", adLongVarWChar,

adParamInput, 16, vNotes)
>         .Parameters.Append .CreateParameter("ReportsTo", adInteger,

adParamInput, 4, vReportsTo)
>         .Parameters.Append .CreateParameter("PhotoPath", adVarWChar,

adParamInput, 255, vPhotoPath)

- Show quoted text -

>         ' Run the command (perform the Insert)
>         .Execute , , adExecuteNoRecords

>         ' Get the new Identity value
>         lEmployeeID = .Parameters("EmployeeID").Value
>     End With
> '*****************

> --

> Thanks,
> Carl Prothman
> Microsoft Visual Basic MVP
> http://www.able-consulting.com

> "Xin Li" <xin...@uiuc.edu> wrote in message

news:Ox#X9E$EBHA.1392@tkmsftngp05...

- Show quoted text -

> > Hi,

> > I have a quick question.  How can I get the identity value of a newly
> > inserted row using the Recordset object.  Here is what I am trying right
> > now:

> >         Set toRS = New Recordset
> >         toRS.Open "PMSUsers", oDb.CN, adOpenForwardOnly,
adLockOptimistic
> >         toRS.AddNew
> >         toRS("UsrLoginID") = strEncUsername
> >         toRS("UsrPassword") = strEncPassword
> >         toRS("CustomerID") = CustomerID
> >         toRS.Update
> >         AddUser = toRS("UsrID") 'Return New UserID to calling function

> > The UsrID column is defined as an IDENTITY column in SQL.  However, it's
> > always returning 0 for some reason, and according to SQL Server, new ids
are
> > being correctly generated.  Any help would be appreciated.  Thanks.

> > Xin Li

 
 
 

Getting IDENTITY value of newly inserted row

Post by Carl Prothma » Sat, 28 Jul 2001 05:52:01



Quote:> Well Carl that all looks simple enough?? ...  ummm what is the
> actual difference using >>

> Open,update and your method do you think in milleseconds?

> Really I mean that all looks nice but is it neccessary?

I don't have actual numbers.  I'm sure someone in this newsgroup knows where they can be viewed.
But I have found that using an ADO Command (with Parameter) that calls a SQL Server stored
procedure is much faster than using the Recordset Update method, or even the Execute method!

As I understand it, the MDAC 2.5 ODBC Driver and OLE DB Provider for SQL Server have been
tuned to access the "sp_executesql" system stored procedure. It's support for parameter subsitution
makes more efficient because it generates execution plans that more likey to be reused by SQL
Server.

Quote:> It would take me 10 minutes just to write it all...

> Ummm 30 seconds ..... this took

Is 10 mins of your dev time worth the effort to make your application run fast?  I would think so!
;-)

Quote:> Access ... I haven't done SqlServer for a while so

I still believe it's faster to run a Command that calls a Stored Query (or runs SQL)
than it is to use the Recordset Update when using Access (JET).

Quote:> Some really serious questions though to you!

> Is this really the way you do everything ... create an update procedure?

Absolutely!  You can create the basic Select, Insert, Update, and Delete stored procedures
for each table in your database.  A variation on this is to have a Save stored procedure for
each table that does either an Insert or Update depending if it finds and existing ID or not.

Quote:> Can't you just have one in SqlServer and leave it there?

Huh?  Stored Procedures are stored in Sql Server...

Quote:> What about a full example .... including how you call it, how you pass the
> values to the
> parameters. >> This is intended for study purposes  : )

See my prior post for the ADO Command example

Note, I have an ADO Command code generator (for free) at
http://www.able-consulting.com/ecodegen/
which will create the Stored Procedures and ADO Commands / Parameters
for one or more tables.

Quote:> What are you passing an object ... I  guess as data validation must have
> been carried out

No object is being passed.   See my code example in my prior post.

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com

 
 
 

Getting IDENTITY value of newly inserted row

Post by Bill » Sat, 28 Jul 2001 09:33:18


Yes, any update that does not involve the Recordset will be faster. There
are a number (a bevy) of ways to update and execute queries and they don't
have to be very fancy to outrun the Recordset.Update. Execute is about the
fastest especially when using the adExecuteNoRecords. Example? In the usual
place.

--
William (Bill) Vaughn
President, Beta V Corporation
Redmond, Washington USA
www.betav.com
(425) 556-9205 (v/f)



> > Well Carl that all looks simple enough?? ...  ummm what is the
> > actual difference using >>

> > Open,update and your method do you think in milleseconds?

> > Really I mean that all looks nice but is it neccessary?

> I don't have actual numbers.  I'm sure someone in this newsgroup knows

where they can be viewed.
Quote:> But I have found that using an ADO Command (with Parameter) that calls a
SQL Server stored
> procedure is much faster than using the Recordset Update method, or even
the Execute method!

> As I understand it, the MDAC 2.5 ODBC Driver and OLE DB Provider for SQL
Server have been
> tuned to access the "sp_executesql" system stored procedure. It's support

for parameter subsitution
Quote:> makes more efficient because it generates execution plans that more likey
to be reused by SQL
> Server.

> > It would take me 10 minutes just to write it all...

> > Ummm 30 seconds ..... this took

> Is 10 mins of your dev time worth the effort to make your application run

fast?  I would think so!
Quote:> ;-)

> > Access ... I haven't done SqlServer for a while so

> I still believe it's faster to run a Command that calls a Stored Query (or
runs SQL)
> than it is to use the Recordset Update when using Access (JET).

> > Some really serious questions though to you!

> > Is this really the way you do everything ... create an update procedure?

> Absolutely!  You can create the basic Select, Insert, Update, and Delete
stored procedures
> for each table in your database.  A variation on this is to have a Save

stored procedure for

- Show quoted text -

Quote:> each table that does either an Insert or Update depending if it finds and
existing ID or not.

> > Can't you just have one in SqlServer and leave it there?

> Huh?  Stored Procedures are stored in Sql Server...

> > What about a full example .... including how you call it, how you pass
the
> > values to the
> > parameters. >> This is intended for study purposes  : )

> See my prior post for the ADO Command example

> Note, I have an ADO Command code generator (for free) at
> http://www.able-consulting.com/ecodegen/
> which will create the Stored Procedures and ADO Commands / Parameters
> for one or more tables.

> > What are you passing an object ... I  guess as data validation must have
> > been carried out

> No object is being passed.   See my code example in my prior post.

> --

> Thanks,
> Carl Prothman
> Microsoft Visual Basic MVP
> http://www.able-consulting.com

 
 
 

Getting IDENTITY value of newly inserted row

Post by VOrte » Sun, 29 Jul 2001 05:23:05


Carl ...still a little more help .....

When I use sqlServer ... I create the Sp and then pass parameters using the
command object ....

This "create" stuff is just a little weird to me ..... and believe me I have
read
a few books by database people with VB.

This is an example of how I learnt with business objects ......

Sorry it is a little long  : ) but if you could just finalise this issue
once and for all with
me I would be more than grateful.

Public Sub Save()

  If mobjBrokenRules.Count = 0 Then

    Dim cmd As ADODB.Command
    Dim rsTemp As ADODB.Recordset
    Dim IdComp As Long

    If Not (mflgEditing Or mflgNew) Then Err.Raise 445

    If mflgNew Then
      IdComp = 99999999
    Else
      IdComp = mudtCPs.IdComp
    End If

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnJetFab
    cmd.CommandText = "CompanySelect"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters("@IdComp").Value = IdComp  '0 will return all records

    Set rsTemp = New ADODB.Recordset
    rsTemp.CursorLocation = adUseClient
    rsTemp.Open cmd, , adOpenForwardOnly, adLockOptimistic

    If mflgNew Then
      rsTemp.AddNew
    End If

    With rsTemp.Fields
      If mudtCPs.IdType > 0 Then .Item("IdType").Value = mudtCPs.IdType
      If mudtCPs.IdValidity > 0 Then .Item("IdValidity").Value =
mudtCPs.IdValidity
      If mudtCPs.IdTermsDel > 0 Then .Item("IdTermsDel").Value =
mudtCPs.IdTermsDel
      If mudtCPs.IdTermsPay > 0 Then .Item("IdTermsPay").Value =
mudtCPs.IdTermsPay
      If mudtCPs.IdDays > 0 Then .Item("IdDays").Value = mudtCPs.IdDays
      If mudtCPs.IdStatus > 0 Then .Item("IdStatus").Value =
mudtCPs.IdStatus
      .Item("txtDesc").Value = Trim(mudtCPs.Name)
      .Item("txtCode").Value = Trim(mudtCPs.Code)
      .Item("txtCreditLimit").Value = mudtCPs.CreditLimit
      .Item("cboxPackaging").Value = mudtCPs.Packaging
      .Item("cboxCoc").Value = mudtCPs.CertOfConf
      If mudtCPs.IdInvoiceCountry > 0 Then .Item("IdICountry").Value =
mudtCPs.IdInvoiceCountry
      .Item("txtILine1").Value = Trim(mudtCPs.InvAdd1)
      .Item("txtILine2").Value = Trim(mudtCPs.InvAdd2)
      .Item("txtILine3").Value = Trim(mudtCPs.InvAdd3)
      .Item("txtICity").Value = Trim(mudtCPs.InvCity)
      .Item("txtIPCode").Value = Trim(mudtCPs.InvPostCode)
      .Item("txtIState").Value = Trim(mudtCPs.InvState)
      .Item("txtIPhone1").Value = Trim(mudtCPs.InvPhone1)
      .Item("txtIPhone2").Value = Trim(mudtCPs.InvPhone2)
      .Item("txtIFax").Value = Trim(mudtCPs.InvFax)
      .Item("txtIEmail").Value = Trim(mudtCPs.InvEmail)
      .Item("txtIWebPage").Value = Trim(mudtCPs.InvWebPage)
      .Item("txtIExt").Value = Trim(mudtCPs.InvExtension)
       If mudtCPs.IdDeliveryCountry > 0 Then .Item("IdDCountry").Value =
mudtCPs.IdDeliveryCountry
      .Item("txtDLine1").Value = Trim(mudtCPs.DelAdd1)
      .Item("txtDLine2").Value = Trim(mudtCPs.DelAdd2)
      .Item("txtDLine3").Value = Trim(mudtCPs.DelAdd3)
      .Item("txtDCity").Value = Trim(mudtCPs.DelCity)
      .Item("txtDPCode").Value = Trim(mudtCPs.DelPostCode)
      .Item("txtDState").Value = Trim(mudtCPs.DelState)
      .Item("txtDPhone1").Value = Trim(mudtCPs.DelPhone1)
      .Item("txtDPhone2").Value = Trim(mudtCPs.DelPhone2)
      .Item("txtDFax").Value = Trim(mudtCPs.DelFax)
      .Item("txtDEmail").Value = Trim(mudtCPs.DelEmail)
      .Item("txtDWebPage").Value = Trim(mudtCPs.DelWebPage)
      .Item("txtDExt").Value = Trim(mudtCPs.DelExtension)
      If mudtCPs.IdOfficeCountry > 0 Then .Item("IdCountry").Value =
mudtCPs.IdOfficeCountry
      .Item("txtLine1").Value = Trim(mudtCPs.OffAdd1)
      .Item("txtLine2").Value = Trim(mudtCPs.OffAdd2)
      .Item("txtLine3").Value = Trim(mudtCPs.OffAdd3)
      .Item("txtCity").Value = Trim(mudtCPs.OffCity)
      .Item("txtPCode").Value = Trim(mudtCPs.OffPostCode)
      .Item("txtState").Value = Trim(mudtCPs.OffState)
      .Item("txtPhone1").Value = Trim(mudtCPs.OffPhone1)
      .Item("txtPhone2").Value = Trim(mudtCPs.OffPhone2)
      .Item("txtFax").Value = Trim(mudtCPs.OffFax)
      .Item("txtEmail").Value = Trim(mudtCPs.OffEmail)
      .Item("txtWebPage").Value = Trim(mudtCPs.OffWebPage)
      .Item("txtExt").Value = Trim(mudtCPs.OffExtension)
      .Item("txtNotes").Value = Trim(mudtCPs.Notes)
      .Item("numDisc").Value = mudtCPs.Discount
    End With

    rsTemp.Update

    If mflgNew Then
      mudtCPs.IdComp = rsTemp.Fields.Item("IdComp").Value
    End If

    rsTemp.Close
    Set rsTemp = Nothing

    mflgNew = False
    mflgDirty = False
    mflgEditing = False

  End If

End Sub

And the part from my SqlServer

CREATE PROCEDURE CompanySelect
@IdComp int

 AS

SELECT *
FROM tblCompany
WHERE IdComp  = @IdComp

One thing I have to confess I thought the following was code you actually
had in VB

"Create Procedure spEmployees_Insert
(
    @EmployeeID int Output,
    @LastName nvarchar(20),

...........
Ok I am stupid sometimes I admit it!

BUT... This part of your code .....

    ' Create a new Command object
    Set oCmd = New ADODB.Command
    With oCmd

        ' Set Command properties
        .ActiveConnection = oConn
        .CommandType = adCmdStoredProc
        .CommandText = "spEmployees_Insert"

' Create Command Parameter(s)
        .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,
adParamReturnValue, 4)
        .Parameters.Append .CreateParameter("EmployeeID", adInteger,
adParamOutput, 4)
        .Parameters.Append .CreateParameter("LastName", adVarWChar,
adParamInput, 20,

Can it not be like this then?

Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnJetFab
    cmd.CommandText = "CompanySelect"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters("@IdComp").Value = mudtCps.IdComp
    cmd.parameters("@EmployeeId").Value = mudtCps.IdEmployee

Why do you add all this ....... adVarWChar, adParamInput, 20

If I am passing all this data from an object I am assuming that is
controlling the data verification as it happens .. as a good object
should.

And was it you or someone else who in the pass said never to use the
command object to update data .....

Man you guys are confusing me!

Comment from you and alexander

Alexander

OK, you catched me.

You can return updatable recordsets from stored procedures but *in no way
you should do that*. Actually it's the matter of code redability and style.
I can't accept that db update happens somewhere outside my code. I always
use sp for updates or have a module which generates SQL. And I always use
disconnected recordsets whenever possible.

And of course from you!

Alexander,
You are correct.  For best performance, one should call a stored procedure
to get a forward-only, read-only recordset.  Then if any values are changed
in the UI, then one should call another stored procedure that will either
Insert or Update the values.  This is the design pattern that I use all the
time with great success!

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP

So I am going to have to finalise my understanding of all this ... based on
my style
(I think I am getting a style well I am trying hard !)

When getting data from the sqlserver...

Get Data and severe all ties with database - this is my OOP approach anyway!

Modify Data in object using object to handle rules for validation, and as I
have
in all my objects a broken rules class that ensures all data is Ok for save.

Then open a command and update the data using your sample ....

        ' Set Command properties
        .ActiveConnection = oConn
        .CommandType = adCmdStoredProc
        .CommandText = "spEmployees_Insert"

' Create Command Parameter(s)
        .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,
adParamReturnValue, 4)
        .Parameters.Append .CreateParameter("EmployeeID", adInteger,
adParamOutput, 4)
        .Parameters.Append .CreateParameter("LastName", adVarWChar,
adParamInput, 20,

Update the database!

Ok I am enlightened thats if you agree with my understanding (Oh it has
taken a while to sink in!)

BUT ...... Why the .........

.Parameters.Append .CreateParameter("LastName", adVarWChar, adParamInput,
20, sLastName)

When I think that

Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnJetFab
    cmd.CommandText = "CompanySelect"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters("@IdComp").Value = mudtCps.IdComp
    cmd.parameters("@EmployeeId").Value = mudtCps.IdEmployee

    Is quite Ok!

Oh and one final little question .....

How would I stop the problem with assigning the 0 value from my object
IdCountry ... which is default value for the Long in the data type....

This as you know is a one to many typical combo situation where they
may not select a Country or many othere values also..

Regards and If you got here I really appreciate your time to help me
with all this ...

VOrtex

"Carl Prothman" <car...@spamcop.net> wrote in message

news:ewck0ThFBHA.1420@tkmsftngp07...
> "VOrtex" <as...@askme.nospam> wrote
> > Well Carl that all looks simple enough?? ...  ummm what is the
> > actual difference using >>

> > Open,update and your method do you think in milleseconds?

> > Really I mean that all looks nice but is it neccessary?

> I don't have actual numbers.  I'm sure someone in this newsgroup knows

where they can be viewed.
> But I have found that using an ADO Command (with Parameter) that calls a
SQL Server stored
> procedure is much faster than using the Recordset Update method, or even
the Execute method!

> As I understand it, the MDAC 2.5 ODBC Driver and OLE DB Provider for SQL
Server have been
> tuned to access the "sp_executesql" system stored procedure. It's support

for parameter subsitution
> makes more efficient because it generates execution plans that more likey
to be reused by SQL
> Server.

> > It would take me 10 minutes just to write it all...

> > Ummm 30 seconds ..... this took

> Is 10 mins of your dev time worth the effort to make your application run

fast?  I would think so!
> ;-)

...

read more »

 
 
 

Getting IDENTITY value of newly inserted row

Post by Carl Prothma » Tue, 31 Jul 2001 10:29:20


comments in-line below...


<snip>

> ...........
>     ' Create a new Command object
>     Set oCmd = New ADODB.Command
>     With oCmd

>         ' Set Command properties
>         .ActiveConnection = oConn
>         .CommandType = adCmdStoredProc
>         .CommandText = "spEmployees_Insert"

> ' Create Command Parameter(s)
>         .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,
> adParamReturnValue, 4)
>         .Parameters.Append .CreateParameter("EmployeeID", adInteger,
> adParamOutput, 4)
>         .Parameters.Append .CreateParameter("LastName", adVarWChar,
> adParamInput, 20,

> Can it not be like this then?

> Set cmd = New ADODB.Command
>     cmd.ActiveConnection = cnJetFab
>     cmd.CommandText = "CompanySelect"
>     cmd.CommandType = adCmdStoredProc


> Why do you add all this ....... adVarWChar, adParamInput, 20

The 1st code snipit will always run faster since the Parameters are defined in
code, and hence the OLE DB Provider knows the parameter information.

The 2nd code snipit will run slower since the OLE DB provider will need to
make an extra trip to the database to obtain the parameters information.    

You can see the extra call if you turn on SQL Server Profiler and try both
techniques above.

Quote:> If I am passing all this data from an object I am assuming that is
> controlling the data verification as it happens .. as a good object
> should.

Yes, your business object should enforce the business rules for your system.

Quote:> And was it you or someone else who in the pass said never to use the
> command object to update data .....

Not me.  Must of been someone else... ;-)    

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com

 
 
 

Getting IDENTITY value of newly inserted row

Post by VOrte » Sat, 04 Aug 2001 01:54:24


Thanks Carl

Even though you skipped a few questions  ;-)

VOrtex


> comments in-line below...


> <snip>
> > ...........
> >     ' Create a new Command object
> >     Set oCmd = New ADODB.Command
> >     With oCmd

> >         ' Set Command properties
> >         .ActiveConnection = oConn
> >         .CommandType = adCmdStoredProc
> >         .CommandText = "spEmployees_Insert"

> > ' Create Command Parameter(s)
> >         .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,
> > adParamReturnValue, 4)
> >         .Parameters.Append .CreateParameter("EmployeeID", adInteger,
> > adParamOutput, 4)
> >         .Parameters.Append .CreateParameter("LastName", adVarWChar,
> > adParamInput, 20,

> > Can it not be like this then?

> > Set cmd = New ADODB.Command
> >     cmd.ActiveConnection = cnJetFab
> >     cmd.CommandText = "CompanySelect"
> >     cmd.CommandType = adCmdStoredProc


> > Why do you add all this ....... adVarWChar, adParamInput, 20

> The 1st code snipit will always run faster since the Parameters are
defined in
> code, and hence the OLE DB Provider knows the parameter information.

> The 2nd code snipit will run slower since the OLE DB provider will need to
> make an extra trip to the database to obtain the parameters information.

> You can see the extra call if you turn on SQL Server Profiler and try both
> techniques above.

> > If I am passing all this data from an object I am assuming that is
> > controlling the data verification as it happens .. as a good object
> > should.

> Yes, your business object should enforce the business rules for your
system.

> > And was it you or someone else who in the pass said never to use the
> > command object to update data .....

> Not me.  Must of been someone else... ;-)

> --

> Thanks,
> Carl Prothman
> Microsoft Visual Basic MVP
> http://www.able-consulting.com

 
 
 

Getting IDENTITY value of newly inserted row

Post by VOrte » Sat, 04 Aug 2001 02:06:22


Thanks Carl even though you skipped a few   ;-)

VOrtex


> comments in-line below...


> <snip>
> > ...........
> >     ' Create a new Command object
> >     Set oCmd = New ADODB.Command
> >     With oCmd

> >         ' Set Command properties
> >         .ActiveConnection = oConn
> >         .CommandType = adCmdStoredProc
> >         .CommandText = "spEmployees_Insert"

> > ' Create Command Parameter(s)
> >         .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,
> > adParamReturnValue, 4)
> >         .Parameters.Append .CreateParameter("EmployeeID", adInteger,
> > adParamOutput, 4)
> >         .Parameters.Append .CreateParameter("LastName", adVarWChar,
> > adParamInput, 20,

> > Can it not be like this then?

> > Set cmd = New ADODB.Command
> >     cmd.ActiveConnection = cnJetFab
> >     cmd.CommandText = "CompanySelect"
> >     cmd.CommandType = adCmdStoredProc


> > Why do you add all this ....... adVarWChar, adParamInput, 20

> The 1st code snipit will always run faster since the Parameters are
defined in
> code, and hence the OLE DB Provider knows the parameter information.

> The 2nd code snipit will run slower since the OLE DB provider will need to
> make an extra trip to the database to obtain the parameters information.

> You can see the extra call if you turn on SQL Server Profiler and try both
> techniques above.

> > If I am passing all this data from an object I am assuming that is
> > controlling the data verification as it happens .. as a good object
> > should.

> Yes, your business object should enforce the business rules for your
system.

> > And was it you or someone else who in the pass said never to use the
> > command object to update data .....

> Not me.  Must of been someone else... ;-)

> --

> Thanks,
> Carl Prothman
> Microsoft Visual Basic MVP
> http://www.able-consulting.com

 
 
 

Getting IDENTITY value of newly inserted row

Post by Carl Prothma » Sat, 04 Aug 2001 04:41:37



> Thanks Carl even though you skipped a few   ;-)

VOrtex
Sorry, I thought I answered all your questions.  
Which one do you think I missed?

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com

 
 
 

1. Getting the identity value of a newly inserted row

Hi,

I have a question regarding identity columns in SQL Server 7.

Sometimes, after inserting a row into a table using ADO, you would want to
get the identity created for the newly inserted row. For instance, if table
EMPLOYEE has an identity column called EmployeeId, after calling
Recordset.Update, you can read Recordset("EmployeeId").Value and get the
identity value assigned to that newly inserted row. This works okay unless
EMPLOYEE table has an insert-trigger that inserts rows to some other table.
Say, after inserting a new EMPLOYEE row, you must add into the AUDIT table
that "employee so and so has been added." , and the primary key for the
AUDIT table is another identity column (called AuditID). If EMPLOYEE table
has an insert-trigger as described above, after calling Recordset.Update
method, what you read from Recordset("EmployeeID").Value is the number that
was auto-generated for the identity column of AUDIT table, and not the
number that was auto-generated for the identity column of EMPLOYEE table. I

was added, the identity of the AUDIT table was the one that was returned
instead.

So, me and my comworker think that an alternative solution to reading the
identity value after a recordset update is by doing something like "Select
Max(EmployeeID) From EMPLOYEE". This strategy looks like it's going to work
90% of the time. But our concern is, if the database is busy (many
concurrent users doing concurrent inserts on the same tables), the "Select
Max(IdentityColumn)" strategy might not work.

I guess my main question is: Is there an efficient, bullet-proof way of
retrieving the newly created identity column value for a row that was just
inserted into a table that fires a trigger which in turn inserts a row into
another table that also has an identity column?

Any tips, recommendations would be greatly appreciated. Thanks!

-Gabe

2. xp_readmail & attachments?

3. How to get Identity column values for newly inserted row

4. Database Output Parms and VB

5. Getting IDENTITY value of a row that was just INSERTED

6. Power Designer (Win2K ODBC)->ASA Server on Linux

7. Retrieving Identity value of newly added row

8. Soft pages faults vs hard page faults

9. Retrival of newly created row auto generated identity column values

10. Identity cannot be determined for newly inserted rows

11. get back the identity of a newly inserted row thru ODBC

12. Identity Number of Newly Inserted Row

13. Error - The provider is unable to determine identity for newly inserted rows (0x80040E1B)