|
 |
asp_databases thread: How to know when to update or insert?
Message #1 by "Laphan" <laphan@u...> on Sat, 18 Nov 2000 22:36:30 -0000
|
|
Dear All
Could anybody show me a good routine for knowing when to use an INSERT
method
if an entry doesn't exist and an UPDATE method if it does, eg:
I have an Excel or Text file list of books. In this list, there are 4 new
books and 8 old books (prices have changed so I want to update these books'
records at the same time).
I have an Access DB and an SQL Server DB and I need a routine (no wizard
features purely code) to import this new data without the prior knowledge of
which should be created as new and which should be just updated.
Can some form of routine identify this 'if exists' procedure?
Many thanks.
Regards
Laphan
mailto:laphan@u...
Message #2 by "Pat Waddington" <paw@s...> on Tue, 21 Nov 2000 01:07:41 -0000
|
|
The most straightforward way is to try to SELECT the record you want to
insert/update then test for BOF and EOF. If either is true you INSERT,
otherwise you UPDATE. Make sure you have included 'On Error Resume Next' in
your script or you will get a database error when the record does not exist.
HTH
Pat
----- Original Message -----
From: "Laphan" <laphan@u...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, November 18, 2000 10:36 PM
Subject: [asp_databases] How to know when to update or insert?
> Dear All
>
> Could anybody show me a good routine for knowing when to use an INSERT
> method
> if an entry doesn't exist and an UPDATE method if it does, eg:
>
> I have an Excel or Text file list of books. In this list, there are 4 new
> books and 8 old books (prices have changed so I want to update these
books'
> records at the same time).
>
> I have an Access DB and an SQL Server DB and I need a routine (no wizard
> features purely code) to import this new data without the prior knowledge
of
> which should be created as new and which should be just updated.
>
> Can some form of routine identify this 'if exists' procedure?
>
> Many thanks.
>
> Regards
>
> Laphan
>
> mailto:laphan@u...
>
>
>
Message #3 by "Ken Schaefer" <ken@a...> on Tue, 21 Nov 2000 11:30:11 +1100
|
|
Do a SELECT first. If the resulting Recordset is .EOF then there are no
matching records, so do the INSERT.
If the Recordset is not .EOF then you need to execute an UPDATE statement.
Cheers
Ken
----- Original Message -----
From: "Laphan" <laphan@u...>
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, November 19, 2000 9:36 AM
Subject: [asp_databases] How to know when to update or insert?
> Dear All
>
> Could anybody show me a good routine for knowing when to use an INSERT
> method
> if an entry doesn't exist and an UPDATE method if it does, eg:
>
> I have an Excel or Text file list of books. In this list, there are 4 new
> books and 8 old books (prices have changed so I want to update these
books'
> records at the same time).
>
> I have an Access DB and an SQL Server DB and I need a routine (no wizard
> features purely code) to import this new data without the prior knowledge
of
> which should be created as new and which should be just updated.
>
> Can some form of routine identify this 'if exists' procedure?
>
> Many thanks.
>
> Regards
>
> Laphan
>
> mailto:laphan@u...
>
>
>
>
Message #4 by "Ken Schaefer" <ken@a...> on Tue, 21 Nov 2000 15:39:31 +1100
|
|
Why do you need the on error resume next to do what you propose below?
Which part would throw the error?
Cheers
Ken
----- Original Message -----
From: "Pat Waddington" <paw@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, November 21, 2000 12:07 PM
Subject: [asp_databases] Re: How to know when to update or insert?
> The most straightforward way is to try to SELECT the record you want to
> insert/update then test for BOF and EOF. If either is true you INSERT,
> otherwise you UPDATE. Make sure you have included 'On Error Resume Next'
in
> your script or you will get a database error when the record does not
exist.
>
> HTH
> Pat
Message #5 by "jigs gandhi" <newsgroup@h...> on Tue, 21 Nov 2000 12:27:53 +0530
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0154_01C053B6.7861C300
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
hi
to save SQL Server the trouble, you can do this
set rs =3D conn.execute("select <primarykey-column> from <tablename>
where <your where conditions>"
if rs.eof then
sql =3D "insert into <tablename> (<column-list>) values
(<value-list>)"
else
primarykey =3D rs(0)
sql =3D "update <tablename> set <column> =3D <value> where
<primaryley-column> =3D primarykey
end if
the best way to make out EOF-BOF condition is to
sql =3D "select 'x' from <tablename> where <your where conditions>"
this will save SQL Server time needed to retrieve actual data from
tables.
Now it will only check no of rows with matching conditions and return
that much 'x's
jigs
----- Original Message -----
From: Laphan
To: ASP Databases
Sent: Sunday, November 19, 2000 4:06 AM
Subject: [asp_databases] How to know when to update or insert?
Dear All
Could anybody show me a good routine for knowing when to use an INSERT
method
if an entry doesn't exist and an UPDATE method if it does, eg:
I have an Excel or Text file list of books. In this list, there are 4
new
books and 8 old books (prices have changed so I want to update these
books'
records at the same time).
I have an Access DB and an SQL Server DB and I need a routine (no
wizard
features purely code) to import this new data without the prior
knowledge of
which should be created as new and which should be just updated.
Can some form of routine identify this 'if exists' procedure?
Many thanks.
Regards
Laphan
mailto:laphan@u...
---
FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS
IN YOUR INBOX!
Get the latest and best HTML, XML, and JavaScript tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb's
FREE IT newsletters at http://www.earthweb.com today!
$subst('Email.Unsub')
|
|
|
View Cart
