How to get Identity value of Last Inserted Row
Identity Column in SQL Server View
Access 2007 front end with SQL Server 2005 Express back end.
Using SQL Server Profiler, I have observed the following:
When an Access datasheet form is bound to a table (or view containing one table), with an Identity (Access Autonumber) column, and a new record is inserted via the bound form, Access uses sp_executesql to insert the new values into the table (or view).
If I look at design view of the table (or view) in Access, the identity column appears as an autonumber.
Since Access recognizes the Identity (Autonumber) column, it uses SELECT @@Identity to retrieve the new identity value.
select @@identity returns 0
I have been doing some reading, but without success in figuring this out. How do I group two queries an insert and then an @@Identity so that @@identity won't return 0, but will return the primary key, which is an autonumber?
I am running access 2007 and am trying to run this query in response to a control button being pressed so it is in vba.
update with trigger - need current row identity
I have an encryption function that changes a particular value with a given row identifier. I would like to automate this with a trigger during updating. Is it possible to get the identifierof the row that was just updated through a trigger? If so, I could then encrypt the un-encrypted values as they are being updated.
Retrieve the value of an Autonumber field after an INSERT
I've read the tutorial from pablo about retrieving the number of the last inserted record. My question is : is @@IDENTITY working if my database is under ms access 2000 ?
Disconnected DataSet not recognizing seed value of SQL Server table
I have a parent table and a child table. The Parent table has an identity column as the primary key. The dataset does not recognize the current state of the identity column, I.e. even though there is no data in the table, the seed may be at 11, or some other random number due to deletes, etc.
So when I insert into the parent dataset, and view what the value is during runtime it always starts at 1 even though when it commits (via Update(datatable)) back it might be 11 or 12.
The problem for me is, I want to leverage the correct identity value during the process to insert a child row into the child dataset.
Of course, it uses the same values that are in the Parent dataset, which is 1,2,3. etc and then when I go to commit the child data to the database, it errors on foreign key constraint because its trying to insert a 1 when it should be 11.
My question is this: Is there an way to capture the correct identity value from the database? How can I get around this?
I am using ADO.NET 2.0 and SQL Server 2005 sp2.
RETURN @@IDENTITY and SELECT @@IDENTITY in SQL stored procedure
I have a number of stored procedures for inserting records and at the and I return the ID of the newly created record for the calling code.
I'm curious as to why 'RETURN @@IDENTITY' seems to return NULL whereas 'SELECT @@IDENTITY' returns exactly what I want.
What's the difference? What is the SQL 'RETURN' statement supposed to be used for, if not this?
My troublesome stored procedures follow the same basic pattern below.
Insert column into a table only when match found, row by row
I have a question, I am comparing two columns from different tables.There are common figures in the two tables.I have compared the two tables using a query.I want the findings from the query to be inserted to the first table, in a new column.Though when inserted in the first table, I need the new column to match the adjacent row.
Table A contains figures = A,B,C
Table B contains figures = A,F,c
The match will be figure A,C
I want to compare the match table with Table A and insert that information in a new column.
Not just inserting it cause I know how to do that but I need the figures to align with the compared row
I have had to upsize an Access 2003 BE to SQL Server 2005. This means for each table in SQL Server that has an IDENTITY field I will need to add the optional parameter dbSeeChanges to each recordset used.
What I would like to know is, does it matter if I use the dbSeeChanges option on tables that don't have an IDENTITY field?
Also can it be used on local access tables?
The reason I ask is that it is easier to change all OpenRecordset commands than just the ones that use the IDENTITY field.
Stuck on Access "CurrentDB" variable
I've written some fairly fancy Access/VB apps in my time, but it's been a while, and I guess I've even forgotten my ABCs.
I'm making an Access 2002 database. Here's the problem.
1. The DB has person, organization, position, and pop tables (pop is short for"person, organization, or position"). Every person, organization, and position is a pop. Every pop is identified by an AutoNumber PK.
Every person is identified by person_id, a numeric PK that is not AutoNumbered.
2. Whenever a user creates (for example) a new person, what needs to happen is (in this order): (a) Before the person row is inserted, a row is added to the pop table, and so it gets an AutoNumber PK value.
(b) The PK value assigned to the new pop row is grabbed and assigned to person_id for the new person row. (c) The new person row is inserted.
3. I'm trying to implement this by attaching a before insert event procedure to the form that creates persons. Here is one version of the procedure:
4. when I try to insert a person through the form, I get"Runtime error 13: type mismatch". It sticks on the line that includes the CurrentDB variable. When I set a watch on it, it says the variable has no value.
Sort records while they get inserted in the table
When I try to insert a record from Access form in the table then it doesn't appear in the table in the right order. E.g if it is the 6th record then it should appear in the 6th row but it appears in between other records. CAn anyone please tell me how to change the order in which records get inserted in the tbale.
Calculate age using a persons identity number
I am using 2010 and have a bound field called Policyholder ID number in a table called Policy Information. I would like to calculate how old a person is by using the identity number which is 13 digits (South African ID) and the format is as follows - 7009215069084.
Using the first 6 digits this person was born on 21 Sept 1970 and is 42 years old ( Current year of 2012minus year of birth of 1970 = 42)
Any ideas as to how I can include a calculated field so once I type in the identity number the age of the person reflects in a field called Current Age.
How to detect user pasting multiple rows into a datasheet
I have a datasheet application that updates additional fields based on the input of one field using VBA code. Due to the design constraints, I use the following technique.
1. After the field is updated, I do a Refresh to save the row
2. Then I retrieve the other values I need using VBA code and save them.
3. Finally, I read the saved row, update the additional fields, and update the row.
This works fine as long as the operator enters the new row manually or even if he/she does a copy & paste of one row. However, if he/she copies and pastes multiple rows, then the Refresh doesn't insert the multiple rows and my step 3 above fails. Is there a way I can detect that multiple rows are being inserted so I can handle the event accordingly?
How to get the last value of an AutoNumber Column
I've a table that has 3 Columns:
Column A : Type - Auto Number.
Column B : Type - String.
Column C : Type - String.
The Current Data in the table is as follows:
2, "USA, "Michigan"
3, "USA", "New York"
If a add new Row, the Column A, being Auto Number, takes my Count to 4 i.e.
4, "USA", "Texas"
Suppose I've deleted Row 3 and Row 4. The table data is now:
2, "USA, "Michigan"
If I try to add a new Row, the Column A, being Auto Number, takes my Count to 5 (instead of 3). Its OK as Auto Number, by default, will follow this numbering.
My question is: Is there a way to programmatically figure out that my row is going to get added at 5, before I actually insert a row and figure it out by myself using @@Identity
Inserting multiple lines to multiple rows
I want to create an access form with a text box linked to a table. I want users to be able to copy cells from an excel column and paste them into the text box. I then want each row in the text box to be inserted into consecutive row of each table field.
Can I customize an identity increment?
I have an ASP website that a user enters their information on. I setup my table with the appropriate columns and set one of them as the primary key.
When the user clicks the submit button, I want the primary key auto increment to include the date and the number.
Is there anyway to customize the identity increment?
Access Data Base - Input new Row at top or Bottom?
I am doing some .net programming using a simple Access database. I do not know much about Access but here's whats happening. I have a database table used as a counter, that is in cell 1 row 1 it starts out as 200 then my .net program executes and does its thing reads in the 200 and adds a 1 to it uses 201 and then stores a new row as 201 (at the bottom) the program reads thebottom row and the new write to the database table say 202 goes at the bottom,
After testing I delete the new rows added and I am back at 200, But now the input is at the top so 201 is at the top and 200 is at the bottom? Why? How do I fix that so new input is at or inserted at the bottom record where my program is reading?
How to find the most recent inserted auto number is?
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.
Update both 1st and last row
When I add a new row to the database it updates both first row and last row. Therefore I need to stop the updating of first row. I coded as follows and I used MS Access as the database.
Get new ID from the table BEFORE insert the new row.
I'm programming with VB .NET 2010 and using a Microsoft Access Database file which is bound to my project as a Dataset. Now I want to know: BEFORE inserting a new row into my dataset tablewhat will be the new ID number,
Surely you know the autonumber ID of a table is different with row count number, for there may be deleted rows between the table rows. Like the following example:
Row 0: ID=1
Row 1: ID=2
Row 2: ID=4 (ID number 3 is deleted)
Row 3: ID=5
Row 4: ID=9 (ID numbers 6,7,8 are deleted)
Now how do we know what ID number of Row 5 will be before inserting the new row? Perhaps ID numbers 10 and 11 are either deleted or not, so it may be 10, may be 11 or 12 or whatever! Isthere a way to know it BEFORE adding or inserting the new row into the table?
How Often Is Now() Calculated During a Query?
I'm using MS Access 2003. I'm wondering whether Now() gets calculated once during a query or once for each record that it applies to. For example, consider the following query:
In practice, every record in tbl_liaImport will have the same CommunicationHeader when this Append query is run.
If every record in tblLiaImport has the same CommunicationHeader value, is it ever possible for the above query to insert two or more records? (I want to insert that single CommunicationHeader, along with the time that it was inserted.) So does the query evaluate Now() once at the beginning of the query, then use that same value for all rows, or does Now() getevaluated for each row under consideration.
If for every row, then I can see the possibility that the query might find more than one distinct record, even though there's only one distinctCommunicationHeader.