I am trying to get my ID added from a people table in an access table
"MyContacts.mdb " i just added.
Its basically a contact file i created in access:
PK Person_ID - Unique - autoincrement
PersonName varchar(30)
so after I edit the screen on 'add' mode, i call this cod here:
PeopleBindingSo urce.EndEdit()
PeopleTableAdap ter.Update(Me.M yDataDS.People)
'Do SCOPE_IDENTITY( ) SOMEHOW HERE
Me.MyDataDataDS .AcceptChanges( )
I cannot figure out how to somehow add a new query to the
"PeopleTableAda pter" to return the ID.
SQL express seems to use the "Refresh the data table" option under advanced
options but this is not the case for jet. It does not have that option
available.
I know my other option is to re-fill the whole dataset ( there are not a lot
of contacts ) but I am trying to see how to use a scope_identity for an
access table - as this is all done behind the scenes for me in an sql
express table.
Thanks,
Miro
3  1857 
I think my easy solution is to add a DateTimeStamp column...set it to a
value,
and then run 1 more query against the database that returns the ID_KEY value
Then upate the row in the database before accept changes is run.
-thats my current solution I am working on.
"Miro" <mi**@beero.com wrote in message
news:eD******** ******@TK2MSFTN GP02.phx.gbl...
>I am trying to get my ID added from a people table in an access table
"MyContacts.md b" i just added.
Its basically a contact file i created in access:
PK Person_ID - Unique - autoincrement
PersonName varchar(30)
so after I edit the screen on 'add' mode, i call this cod here:
PeopleBindingSo urce.EndEdit()
PeopleTableAdap ter.Update(Me.M yDataDS.People)
'Do SCOPE_IDENTITY( ) SOMEHOW HERE
Me.MyDataDataDS .AcceptChanges( )
I cannot figure out how to somehow add a new query to the
"PeopleTableAda pter" to return the ID.
SQL express seems to use the "Refresh the data table" option under
advanced options but this is not the case for jet. It does not have that
option available.
I know my other option is to re-fill the whole dataset ( there are not a
lot of contacts ) but I am trying to see how to use a scope_identity for
an access table - as this is all done behind the scenes for me in an sql
express table.
Thanks,
Miro
Miro wrote:
I am trying to get my ID added from a people table in an access table
"MyContacts.mdb " i just added.
Its basically a contact file i created in access:
PK Person_ID - Unique - autoincrement
PersonName varchar(30)
so after I edit the screen on 'add' mode, i call this cod here:
PeopleBindingSo urce.EndEdit()
PeopleTableAdap ter.Update(Me.M yDataDS.People)
'Do SCOPE_IDENTITY( ) SOMEHOW HERE
Me.MyDataDataDS .AcceptChanges( )
I cannot figure out how to somehow add a new query to the
"PeopleTableAda pter" to return the ID.
SQL express seems to use the "Refresh the data table" option under
advanced options but this is not the case for jet. It does not have
that option available.
I know my other option is to re-fill the whole dataset ( there are not a
lot of contacts ) but I am trying to see how to use a scope_identity for
an access table - as this is all done behind the scenes for me in an sql
express table.
Thanks,
Miro
The MS Access database doesn't have any scope_identity( ) function. You
have to use the @@identity variable.
The identity has session scope, so you need to use the same database
connection as for the insert.
--
Göran Andersson
_____ http://www.guffa.com
"Göran Andersson" <gu***@guffa.co mwrote in message
news:eG******** ******@TK2MSFTN GP05.phx.gbl...
Miro wrote:
>I am trying to get my ID added from a people table in an access table
"MyContacts.md b" i just added.
Its basically a contact file i created in access:
PK Person_ID - Unique - autoincrement
PersonName varchar(30)
so after I edit the screen on 'add' mode, i call this cod here:
PeopleBindingSo urce.EndEdit()
PeopleTableAdap ter.Update(Me.M yDataDS.People)
'Do SCOPE_IDENTITY( ) SOMEHOW HERE
Me.MyDataDataDS .AcceptChanges( )
I cannot figure out how to somehow add a new query to the
"PeopleTableAd apter" to return the ID.
SQL express seems to use the "Refresh the data table" option under
advanced options but this is not the case for jet. It does not have that
option available.
I know my other option is to re-fill the whole dataset ( there are not a
lot of contacts ) but I am trying to see how to use a scope_identity for
an access table - as this is all done behind the scenes for me in an sql
express table.
Thanks,
Miro
The MS Access database doesn't have any scope_identity( ) function. You
have to use the @@identity variable.
The identity has session scope, so you need to use the same database
connection as for the insert.
--
Göran Andersson
_____
http://www.guffa.com
Yes I did find some googling about @@identity
I did find this line - looks like a full example on the bottom: http://www.codeguru.com/forum/archiv.../t-392246.html
But I could not find exactly how to create the query in the dataset
designer.
I went to the Dataset Designer, then to the People Table, right clicked,
add new query.
Then I picked the second one from the list "SELECT which returns a single
value",
Then for the query I did try:
Select @@Identity
or
Select @@Identity from people
and a couple others.
Basically I do know that with jet I have to use the '?' as a place holder
for a parameter in, but am unsure of how to write the query here to return
the int. The query builder complains about the @ symbol.
Or is it that I CANNOT do the @@Identity as part of the dataset designer and
must create a sub -and somehow add a handler to my tableadapter update that
I am calling from my dataset
My datetimestamp idea did the trick but I know that is not the 'proper'
solution.
Thanks,
Miro This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Scrappy |
last post by:
I have an ASP front end on SQL 2000 database. I have a form that submits to
an insert query. The entry field is an "identity" and the primary key. I
have used scope_identity() to display the entry# of the record just entered
on the confirmation page. Now I need to insert the entry into another
table. This is my query:
SET NOCOUNT ON
INSERT wo_main
(site_id, customer, po_number)
VALUES ('::site_id::', '::customer::',...
|
by: Lauren Quantrell |
last post by:
I'm using an Access2K front end on a SQL Server2K backend.
I use Scope_Identity() in a lot of stored procedures to pass the
newwly inserted record's unique ID to the next select statement in the
same stored procedure.
What I'm wondering is how I can pass the Scope_Identity back to the
calling application.
I'm hoping someone can show me the SP code and the aceess code needed
to accomplish the following:
I insert a new record in a table...
|
by: Larry |
last post by:
I am seeing a problem with an ASP application, where I have 2 tables.
In the first table, the ASP inserts just 1 row and retrieves the
primary key of the new row using SCOPE_IDENTITY. It then uses that
primary key in the column of a second table (foreign key) to insert
many rows.
What I'm seeing is an intermittent problem where the foreign key in the
second table is not what it should be. I think the problem may be due
to the fact...
|
by: Hardy Wang |
last post by:
Hi all,
I have the following codes, but SCOPE_IDENTITY() just returns NULL to
me. If I comment out SCOPE_IDENTITY() line and run @@IDENTITY line, it works
fine!! Since I have a trigger on the table, I have to use SCOPE_IDENTITY().
Any ideas?
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
//Create the dataadapter
|
by: Mr Newbie |
last post by:
I was using this fine
sqlIDQuery = "SELECT SectionID FROM SECTIONS WHERE
(SectionID=SCOPE_IDENTITY());"
UNTIL. . . .
I added a parameter, now it fails, it doesent matter if I use the parameter
or not, when I add a param, it falls over, Any Ideas ?
| | |
by: Mr Newbie |
last post by:
The following code is comprises a function to create a section of a
documemt.
Each section is stored in a table. This successfully inserts a record in the
Sections table, but the
scope_identity() returns DbNull. If I remove the parameter 'pSectionName'
and replace it with dummy value, it works fine. Alternatively, if I use
@@IDENTITY with or withour the parameter, that works fine too.
It seems there is a problem with using...
|
by: Justyn |
last post by:
Hi everyone, I hope someone can help me out!
I have added a trigger that gets fired on INSERT and which itself inserts data into another table WITHOUT an identity
I used to get back the @@IDENTITY and I changed this to get back the SCOPE_IDENTITY so I was not affected by the trigger.
However, after adding the trigger I get this error regardless of whether I use @@IDENTITY or SCOPE_IDENTITY:
Microsoft OLE DB Provider for ODBC Drivers...
|
by: needin4mation |
last post by:
Hi, I have generated my queries and selected the refresh the table
option. This creates a select after the insert to get the
scope_identity() so that I can use that key value in another table,
i.e. foreign keys. I have verified the existence of the select in the
DAL .xsd file.
I simply do not know how to get that value, the scope_identity() value.
I know that it is selecting the value for me, but I have no idea how
to get that selected...
|
by: Martin Z |
last post by:
INSERT INTO dbo.Transmission
(TransmissionDate, TransmissionDirection,
Filename, TransmittedData)
VALUES
(@TransmissionDate,@TransmissionDirection,@Filename,@TransmittedData);
SELECT @retVal = SCOPE_IDENTITY();
Pretty simple. There is an additional TransmissionID column that is
an autonumber and primary key. @retVal is always null in my table
adapter function, where I'm setting @retval (in the Parameters
|
by: Mike |
last post by:
Sql Server, Scope_Identity(), Ado.NET:
Which is better? Using an output parameter to return Scope_Identity through
ExecuteNonQuery(), or adding Select Scope_Identity() to the end of the
procedure or ad hoc SQL and using ExecuteScalar()?
Thanks.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
