Problem using @@IDENTITY when connecting to Access XP HELP project deadline looming! VB.Net Visual Studio 2005

Hot Topic

How can I edit values in an XML document?
Click Event for Member of Label Array
Multiple application domains and multiple processes
Writing dates to XML documents
Lingering temporary DLLs from XSLT with script.
How to use Configuration.GetSection() and ConfigurationManager.OpenMappedExeConfiguration()
Capturing console window close event
Message box diplayed in timer.tick handler, not becoming modal
IBM.Data.DB2.iSeries .NET Provider - Will it work with VS 2005?
Web service client doesn't handle returned sequence.

.NET Development

TableAdapter Update problem
Delay on first call to web service
Using Windows Service without Web Service to send emails from web application!!
The Undo operation encountered a context that is different...
Detecting .NET Framworks installed in System using Reflection
Specifying a server for COM+ class instancing
newly added record not available without debug restart
Cannot install .net 2003 on AMD Atholon 64bit 3000
Database explorer and oledb connections
Migrating from Vs 2003

Problem using @@IDENTITY when connecting to Access XP HELP project deadline looming! VB.Net Visual Studio 2005

Hi i have read loads of other threads. But none solved this problem.

I want to return the correct identity after a bindingsource.addnew command.

So i do a tableapdater.update.

It always returns 0! after the update call.

Maybe the connection.closes.

I use this inline in a function called New Estimate.

Private Function EstimateNoNewID() As Integer

Dim newId As Integer = 0

Dim idCMD As New OleDb.OleDbCommand("SELECT @@IDENTITY FROM Estimates", EstimatesTableAdapter.Connection)

newId = CInt(idCMD.ExecuteScalar())

EstimatesTableAdapter.Connection.Close()

Return newId 'identity retrieved and stored

End Function

i also tried it inline on the estimates datatable, but had difficulties getting a reference to a connection.  So i gave up! and put the function at form level

Partial Class EstimatesDataTable

Private Sub EstimatesDataTable_EstimatesRowChanged(ByVal sender As Object, ByVal e As EstimatesRowChangeEvent) Handles Me.EstimatesRowChanged

'pseudo code

if Action = row.added then

 Dim newId As Integer = 0

 Dim idCMD As New OleDb.OleDbCommand("SELECT @@IDENTITY FROM Estimates", Connection)

 newId = CInt(idCMD.ExecuteScalar())

 row("Estimate No") = newID

end if

End Sub

'Could not get a connection!!!

i tried datadatasetTableadapters.EstimatesTableAdapter.Connection, There was some problem with shared and references etc. Couldnt work it out!

 

 

When viewing the dataDataset, estimates table in dataDataset.xsd, click configure, advanced, the option for refresh is disabled and not checked. Also i am not getting default values, so i have to code them in too!

Any solutiuon will be great as ebough pulled hair out. I will have to drop all keys, put in an idcounter table and recode all forms for insert!!!!!

The application i have written really rocks, this is the only bit left!! please help!

 

 

 

 

 

 

 

 

Answer this question

Problem using @@IDENTITY when connecting to Access XP HELP project deadline looming! VB.Net Visual Studio 2005

  • Phil Spokas

    @@identity is limited to the database session, so, if you close your connection after the insert, and then re-open a new connection to call @@identity, you receive zero because, in the session where you call @@identity, you haven't done any insert, so, @@identity cannot return the identity of the last inserted row within the current session.
    You should do an insert, and directly call @@identity after it, or , at least, call @@identity in the same db - session (so , using the same connection)


  • eddy1421

    Thanks for the help. I decided to explicitly open and close the table adapter to do the update with. It appears to be working, i will test if further. Many thanks

  • witkamp

    The wining code at form level was:

    Me.EstimatesBindingSource.AddNew()

    row = CType(CType(Me.EstimatesBindingSource.Current, DataRowView).Row, dataDataSet.EstimatesRow)

    row._Date = Date.Today.ToShortDateString

    row.Subject = JobDescription

    row.Path = "<<new>>"

    EstimatesBindingSource.EndEdit()

    EstimatesTableAdapter.Connection.Open()

    EstimatesTableAdapter.Update(DataDataSet.Estimates)

    Dim newId As Integer = 0

    Dim idCMD As New OleDb.OleDbCommand("SELECT @@IDENTITY FROM Estimates", EstimatesTableAdapter.Connection)

    newId = CInt(idCMD.ExecuteScalar())

    EstimatesTableAdapter.Connection.Close()

    MessageBox.Show(newId)
  • Problem using @@IDENTITY when connecting to Access XP HELP project deadline looming! VB.Net Visual Studio 2005
Answer this question