Autonumber column - How do you find out what the most recent inserted number is?

[emaduddeen]

Hi Everyone,

In one of the forms is a Parent / Child situation.

The program inserts the parent then allows the children to be inserted or updated.

The parent and children are related by an autonumber database table column called CustomerID.

After a parent is inserted into the database I would like to find out what that autonumber ID is so I can use it for the child data. Can you show me code that does this?

Thanks.

Truly,
Emad

 

[jmcilhinney]

Is your application multi-user, or will there only ever be one user connecting to the database? I ask because, if there may be multiple users, what you're asking for simply cannot be done reliably using Access. If it is single-user then it can be done, but it's messy. It would be better if you used a server-based database, e.g. SQL Server Express.

To get the last ID generated by the database, you must execute a query. Because SqlClient supports multiple SQL statements per command, it's a doddle for a SQL Server database. Instead of SQL code like this:

INSERT INTO Person (GivenName, FamilyName) VALUES (@GivenName, @FamilyName)

you simply change it to this:

INSERT INTO Person (GivenName, FamilyName) VALUES (@GivenName, @FamilyName); SELECT ID = SCOPE_IDENTITY()

That query tacked on the end gets the last identity generated within the current scope and assigns it to the ID column of the source row. That updates the parent DataRow and the DataRelation in the DataSet cascades that change to the child DataRows. That means that the child DataTable is ready to be saved to the database with the correct parent IDs without any manual intervention.

With Access, you can't do that because the Jet OLE DB provider doesn't support multiple SQL statements per command. As such, you must execute a separate query after each insert. That means that you cannot insert multiple parent records in a batch. You must insert one record, query the database for the last ID and update that parent row in the DataSet before you can insert the next parent record. The DataRelation will still cascade the IDs to the child DataTable, so you can then insert the child records in a batch.

In Access, I believe that you would use @@IDENTITY, which is a global variable containing the last generated ID. You are using a separate query to the one that performed the insert so, even if SCOPE_IDENTITY is supported, it wouldn't do you any good here. The problem for multi-user systems is that, if another user inserts after you do but before you get the ID, you will be getting their ID instead of yours and your child rows will be related to the wrong parent. There's just no way around that that I'm aware of.

 

[emaduddeen]

Hi,

Thanks for the quick reply. The code will definitively help.

At present it's a single user point of sale program but the owner will eventually be using it on a server to run several jewelry stores on it.

We started out thinking that MS Access was the database to use because it was easy to include the access file with the setup.

Is it better to use a SQL Server database file instead of Access? It's still early in development and that changes should be easy enough to implement.

Will INSERT INTO Person (GivenName, FamilyName) VALUES (@GivenName, @FamilyName); SELECT ID = SCOPE_IDENTITY() work ok in a multi-user system?

Truly,
Emad

 

[cjard]

Give up on Access now, and switch to SQL Server Express or you WILL have massive headaches in the future. Access is NOT a good tool for distributed apps

 

[emaduddeen]

Hi,

Thanks for the advise. We are making the switch.

Truly,
Emad

 

[emaduddeen]

Hi,

We made the switch but we get this error when trying to fill the data set:

The data types nvarchar and varchar are incompatible in the boolean AND operator.

We are not using nvarchar columns. We are using varchar columns in the table.

Can you check this code to make sure we are using the correct syntax for the sql query and parameter since sql server syntax is different than access?

        strSQLByLastName = _
            "Select FirstName + ' ' + LastName AS [Full Name], ID, " & _
                   "PhoneCell, PhoneHome, PhoneWork " & _
              "From Customers " & _
             "Where LastName Like @SearchCriteriaLastName & '%'  " & _
             "Order By 1 "

            objDataAdapterCustomerNamesByLastName = _
                New SqlDataAdapter(strSQLByLastName, objConnection)

            objDataAdapterCustomerNamesByLastName.SelectCommand.Parameters _
               .Add(New SqlParameter("@SearchCriteriaLastName", ""))

   ' This line fails.
   '--------------
            objDataAdapterCustomerNamesByLastName.Fill(objDataSetCustomerNames, _
                                               "CustomersByLastName")

Truly,
Emad

 

[jmcilhinney]

'&' is not a concatenation operator in SQL.

 

[emaduddeen]

Hi,

Thanks for the help.

I changed & to + and it worked.

Truly,
Emad