Hi,
Is there an easy way in ASP/VBscript to grab an autonumber (primary
key) field just after an SQL insert? This is probably easy, but I'm
stuck ..
Cheers. 3 7893
I think you would have to do a query to pull up the the record after
it's inserted, because the SQL insert statement is like a for loop in
that you can't insert records one at a time and then examine the
results such as an autonumber field.
This would be somewhat hackish IMHO. You could literally insert
records one at a time, and just before you insert the record, save it's
values locally, and after you insert, then do a query to find that
record(assuming you can find some combination of values that will be
unique) and examine it's autonumber field. I don't know alot about
SQL, but this would probably run much slower than an insert statement
that isnerts all the records as a batch.
Perhaps you should consider generating the primary key yourself? So
that you know what it is. Instead of having an autonumber field, setup
a primary key field that is some concatination of other fields or just
a number you generate based on what numbers already don't exist in that
column. This is similar to using multiple primary keys, but without
dealing with seperate keys. Even with 2 primary keys then normally
complicated queries get super complicated(at least for me).
I'm a noob BTW, so take my suggestions with a grain of salt.
"SQL insert statement is like a for loop in"
This should have read:
"SQL insert statement is not like a for loop in"
And I'm referring to an isenrt statement that inserts multiple records
in one statement. You can't somehow look at the results of each record
insert as the insert statement is running. That's why you would need
to make you're own for loop. But I think making your own for loop is
fvery hackish round about way of solving the problem. I really think
you should somehow generate a fully deterministic primary key if it is
important you know what the primary key is of a record when it is
inserted, rather than using an autonumber field.
Hi!
You probably have a connection variable,
on which you execute the insert. Use
select @@identity on it:
cnn.execute "your insert....."
set rs=cnn.execute( "select @@identity",,ad cmdtext)
msgbox rs.fields(0).va lue
I don't know much about script syntax,
but the above should work in VBA, given
rs is declared as adodb.recordset .
ben.werdmul...@ sbs.ox.ac.uk wrote: Hi,
Is there an easy way in ASP/VBscript to grab an autonumber (primary key) field just after an SQL insert? This is probably easy, but I'm stuck ..
Cheers. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dr. Know |
last post by:
I am working on an ASP page that writes to several databases, ranging
from MDBs to x-base.
One of the tasks involves using an existing highest value from the DB
and incrementing it before inserting a new record.
I am using Application.Lock and .Unlock together with an application
variable to negotiate access to the DB routine to one session (user)
at a time. This is to ensure that the ID numbers are cleanly
incremented, and that no...
|
by: Ilan Sebba |
last post by:
I have a 'supertype' table with only one field: autonumber. Call this table
the 'parent' table. There are two subtypes, 'androids' and 'martians'.
Martian have only one thing in common: they give birth to identical mules.
So each android and a martian have primary key which is a foreign key to the
parent table. Now, I want to insert a new record in 'android' or 'martian'.
This can be done easily using MS-Access forms. But I want to do...
|
by: bigbinc |
last post by:
I am in autonumber hell, it is important for me to get the AutoNumber
even in case of previous deleted records, and I cant get the value
through a 'test' insert and then delete method. There is no VBA
function that has that value in memory somewhere. That would be the
ideal.
For example:
AutoNumber-Field:
1
|
by: loreille |
last post by:
To insert a record in a Ms Access database and be able to retrieve the
newly
created ID (autonumber) I used the code below (code 1).
Now, the problem is that this is not very secure and that, if for
example an
insertion contains a ' or a " this code fails. It is much better to
work
with @parameters.
So could someone change my code to make it work with @parameters and
|
by: petebeatty |
last post by:
I have created a SQL string the properly inserts a record in the table.
However, the insert does not occur at the end of the table. Instead
it inserts a record after the last record that I viewed. This would be
OK, except it assigns a autonumber to be one greater than the last
viewed record. This causes a duplicate autonumber. I know I can
change the autonumber index (Primary Key) to not allow duplicates.
How can I force the insert...
| |
by: Alan Mailer |
last post by:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any AutoNumber* fields
in them.
Correct me if I'm wrong, but I'm assuming this means that I cannot now
alter these existing Access tables and change their primary key to an
"AutoNumber" type.
If I'm right about this, I need some suggestions as to the...
|
by: kev |
last post by:
Hi all,
I have created a database for equipments. I have a form to register the
equipment meaning filling in all the particulars (ID, serial, type,
location etc). I have two buttons at the end of the form which is
submit and cancel. After i have clicked submit, the information is
stored directly into my corresponding database table. My problem here
is i need to retrieve back the information submitted to display all the
data that the...
|
by: Lyn |
last post by:
Hi,
Is there an "easy" way to write a full record (all fields) using "INSERT
INTO..." into a table which has an AutoNumber field?
Normally, to write a full new record I would use:
INSERT INTO VALUES (Value1, Value2, ...);
If (say) the first field (Value1) is an AutoNumber, what value can you put
that will preserve the auto-increment function?
|
by: ashes |
last post by:
Hi,
I am creating an ecommerce website using Microsoft Visual Studio, VB.Net and MS Access 2003. I am new to VB.Net
When someone wants to register on the website, they fill out a form and the contents of the form is inserted into the MS Access database.
The Customer table in the database already has 30 records (with CustomerIDs 1 - 30) in it (from when the database was first created). The CustomerID field in the database is an...
|
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: 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: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |