Hi All

[LovinIt]

I am hoping someone can help me out...

Maybe I am being stupid...

Trying to get the autonumber on an ADO ADDNEW. And I been going around and around and I am starting to get phsyically ill from the dizziness

So now I am working with a simple example meaning nothing else is in the ACCESS file. I created a simple form with a button to run the code below and I still can't get it to work.

The Connection String is Accessing a SQL Server 2000 database

    Dim con As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    con.Open CONNECTSTR
    rst.Open "torders", con, adOpenDynamic, adLockOptimistic
    rst.AddNew
        rst.Fields("orderclientID") = 1
    rst.Update
    MsgBox rst.Fields("orderindex")
    rst.Close
    rst.Open "Select ID=@@Identity", con
    MsgBox rst.Fields("ID").Value

The inserting of the new record does work and show up in the table.

MsgBox rst.Fields("orderindex") Return NOTHING
rst.Open "Select ID=@@Identity", con RETURNS the Actual Number

AND if I use this Provider=Microsoft.Access.OLEDB.10.0 as the provider then MsgBox rst.Fields("orderindex") statement all of a sudden returns the number

I am worried using the select ID=@@identity is a clouge and may not always work right. A clouge more becuase it is combined with the ADDNEW instead of a traditional INSERT into SQL Statement.

Now isn't a sqloledb as provider SUPPOSE to return the autonumber when using the ADDNEW and UPDATE together?

I have even tried putting the msgbox before the Update to see if that would get it and it doesn't.

I really DO NOT want to do an insert statement as I have between 10-15 fields in each of the 5 tables that need to be inserted into. And the record size is NOT over 64kb.

And I do not want to use that funky provider as I am developing in ACCESS 2003 with file format of 2000 because my end client is using 2002

Any Help would be so tremendously appreciated. I been going around with this for I say about 16 hours of coding trying and trying to figure out what is up.

Many Thanks!

Angela

 

[RoyVidar]

Don't know, but have you tried just:

[tt]set rs=con.execute("select @@identity",,adcmdtext)
msgbox rs.fields(0).value[/tt]

- i e, without the ID thingie in the string?

Roy-Vidar

 

[LovinIt]

Hi Roy!

Thanks for the response.

Actually my SELECT ID=@@Identity works.

But what I am afraid of is using that in conjunction with an ADDNEW. Documentation I found has stated use it in conjunction with a INSERT sql statement hard coded. And I have 5 tables with 10-15 fields in each. I rather not worry about special characters that could mess up the SQL code when doing an INSERT (ie single qoutes in a text field)

As this is for a client I do not want to use something that might only work intermittantly and cause errors down the road.

Did you see ANY reason why the addnew code should not produce the new index value created with the insert?

As I said I can get it to work in quirky ways. And I am afraid that they aren't reliable.

BTW I am using ADO 2.8

Many thanks for your help!

Angela

 

[PHV]

(ie single qoutes in a text field)
Simply use something like this:
Replace([text field], [tt]"'", "''")[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

I'm not a very great fan of using recordsets for updates/inserts, I find it to be slightly worse than dynamic SQL. In stead I prefer executing stored queries, and supply/resolve the parameters through the command objects parameters collection. So again, I don't know about how it works, or not, when doing recordset thingies.

Here's a couple of threads illustrating my preferences thread709-1042219, thread709-1019057. The first one, using "dynamic SQL", but resolving the parameters through the parameter collection.

Using this is
1 - faster
2 - more reliable
3 - don't have to bother with special characters
4 - more secure from SQL injection

Tad more code, but I think that's outweighed by the advantages.

Roy-Vidar

 

[LovinIt]

Thanks too ALL who responded,

I figured out I was using the wrong Cursor Type.

Once I set it to adOpenKeyset the recordset.Field(<ID>) began showing up...

So basically I had to use the following to setup the recordset. DIfferent example but it does work.

rstRemote1.Open  "tOrders", cnRemote, adOpenKeyset, adLockOptimistic, adCmdTable

rstRemote1.Addnew
   rstRemote1.fields(i) = rstLocal1.Fields(i)
rstRemote1.Update
lindex=rstRemote1.fields("orderindex")

Then lindex was finally coming back with the right value...

Gees.....

This is what happens when you are constantly switching from .NET, to Access, to ASP to VB... Some facts leaks out of your head that is very basic and important.

I should look at getting my head leak repaird

Thanks again for all that responded.

Angela