Solved

ADO Recordset Updates ?

Posted on 2010-09-15
4
203 Views
Last Modified: 2012-10-14
I have an ADO recordset, and here is how I set it up:
Dim oConnection As New ADODB.Connection
Dim oRecordset As New ADODB.Recordset
sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"
oConnection.Open(sConnectString)
oRecordset.Open(sSQL, oConnection, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

oRecordset.AddNew()
Orecordset.Fields("Name").value = "xxxxx"
Orecordset.Update

Now here I would like to get the value of the key that is created

x = oRecordSet.Field("ListID").value

But oRecordSet.Field("ListID").value returns Nothing.
If I break here and I query it I will find that the ListID does have a value.

How can i have this oRecordset show me the value of ListID ?
0
Comment
Question by:Jess31
  • 2
4 Comments
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33688401
Try this:
Change tblName to your table name.

        Dim strSQL As String = "SET NOCOUNT ON INSERT INTO tblName(Name) VALUES('xxxxx') SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"

        oConnection.Open(sConnectString)

        oRecordset = oConnection.Execute(strSQL)

        Dim varNewID As Integer = oRecordset("NewID").Value

        oConnection.Close()

Open in new window

0
 

Author Comment

by:Jess31
ID: 33688489
Zhaolai
When it hits the Execute(strSQL) I get this error

[QODBC] Expected lexical element not found: CREATE, DELETE, DROP, INSERT, SELECT, SP_BATCHCLEAR, SP_BATCHSTART, SP_BATCHUPDATE, SP_CATEGORIES, SP_CATEGORYTABLES, SP_CATEGORYREPORTS, SP_CLEARLASTINSERTID, SP_COLUMNS, SP_FOREIGNKEYS, SP_FQSAVETOCACHEROLLBACK, SP_LASTINSERTID, SP_LASTINSERTIDRETURN, SP_OPTIMIZEFULLSYNC, SP_OPTIMIZEUPDATESYNC, SP_PARAMETERS, SP_PRIMARYKEYS, SP_QBFILENAME, SP_REPORT, SP_REPORTCOLUMNS, SP_REPORTS, SP_SPECIALCOLUMNS, SP_STATISTICS, SP_TABLES, SP_VOID or UPDATE
0
 
LVL 10

Accepted Solution

by:
joriszwaenepoel earned 500 total points
ID: 33688985
Have a look at the following article:

HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual Basic .NET
http://support.microsoft.com/kb/815629

0
 

Author Comment

by:Jess31
ID: 33693041
These all seem quite laborious since in MS Access when connecting to SQL Server I was able to just read the key right after the recordset.Update. Maybe this has something do with with this driver/database which is different... I do know that this key is not a numeric. But it for sure does get produced as soon as I do the .Update cause I am able to retrieve it by calling another function that will query that record, but this is slow and I'm doing many records so I would like to avoid that.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

932 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now