Preventing duplicates

I need some help with a dynamic SQL query in Access. I want to insert
records if they are not duplicates. I thought the following would work as
it does prevent duplicates but it inserts a record for each record found
that is not a duplicate. I'm having trouble getting it to insert just one
record when a duplicate is not found.

Arrays:
filename has file names
filepath has virtual path names
Ex. file.asp /asp/file.asp

for i = 0 to ubound(filename)
strSQL = "INSERT INTO listing (filename, filepath) SELECT " & filename(i)
& ", " & filepath(i) & " FROM listing WHERE NOT EXISTS (SELECT " &
filepath(i) & " FROM listing WHERE filepath = '" & filepath(i) & "'"
next

TIA...

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

Re: Preventing duplicates

Roland Hall wrote:
> I need some help with a dynamic SQL query in Access. I want to insert
> records if they are not duplicates. I thought the following would
> work as it does prevent duplicates but it inserts a record for each
> record found that is not a duplicate. I'm having trouble getting it
> to insert just one record when a duplicate is not found.
>
> Arrays:
> filename has file names
> filepath has virtual path names
> Ex. file.asp /asp/file.asp
>
> for i = 0 to ubound(filename)
> strSQL = "INSERT INTO listing (filename, filepath) SELECT " &
> filename(i) & ", " & filepath(i) & " FROM listing WHERE NOT EXISTS
> (SELECT " & filepath(i) & " FROM listing WHERE filepath = '" &
> filepath(i) & "'"
> next
>
Try running just the "SELECT ... " portion using non-duplicate inputs. That
should show you what the problem is.

Jet supports an operation that has been coined an "upsert", in which a query
can be written that will update a record if it exists, and insert a new
record if it does not exist. We had a good thread about it a few months
back. I'd google it but I'm sure you can handle that part yourself ... ;-)
Anyways, find the thread, it should get you started.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Preventing duplicates

"Bob Barrows [MVP]" wrote in message
news:evpQsDNfFHA.2472@TK2MSFTNGP15.phx.gbl...
: Roland Hall wrote:
: > I need some help with a dynamic SQL query in Access. I want to insert
: > records if they are not duplicates. I thought the following would
: > work as it does prevent duplicates but it inserts a record for each
: > record found that is not a duplicate. I'm having trouble getting it
: > to insert just one record when a duplicate is not found.
: >
: > Arrays:
: > filename has file names
: > filepath has virtual path names
: > Ex. file.asp /asp/file.asp
: >
: > for i = 0 to ubound(filename)
: > strSQL = "INSERT INTO listing (filename, filepath) SELECT " &
: > filename(i) & ", " & filepath(i) & " FROM listing WHERE NOT EXISTS
: > (SELECT " & filepath(i) & " FROM listing WHERE filepath = '" &
: > filepath(i) & "'"
: > next
: >
: Try running just the "SELECT ... " portion using non-duplicate inputs.
That
: should show you what the problem is.

Hi Bob. I'm not sure what you're saying re: 'using non-duplicate inputs.'
I know what the problem is, I'm telling it to return rows where the filepath
is not found and it's returning those rows and then I'm inserting a record *
the number of records returned with the new data. The problem is my SQL
query. If it finds a record already in there, it works. It's when it
doesn't find one that it then returns a row for each one not found and that
is the issue. I just don't know how to get around it.

: Jet supports an operation that has been coined an "upsert", in which a
query
: can be written that will update a record if it exists, and insert a new
: record if it does not exist. We had a good thread about it a few months
: back. I'd google it but I'm sure you can handle that part yourself ... ;-)
: Anyways, find the thread, it should get you started.

I'll look for upsert and report back if I'm still stumped.

Currently I'm using a different approach, two statements.

for i = 0 to ubound(filename)
strSQL = "SELECT COUNT(*) FROM listing WHERE filepath = '" & filepath(i) &
"';"
found = conn.Execute(strSQL)
if found(0) = 0 then
strSQL = "INSERT INTO listing (filename, filepath) VALUES ('" &
filename(i) & "','" & filepath(i) & "');"
end if
next

I got information that changed so this is no longer necessary but I want to
know anyway because I know I'll see something similar in the future.

Thanks for the help.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

Re: Preventing duplicates

Roland Hall wrote:
> "Bob Barrows [MVP]" wrote in message
> news:evpQsDNfFHA.2472@TK2MSFTNGP15.phx.gbl...
>> Roland Hall wrote:
>>> I need some help with a dynamic SQL query in Access. I want to
>>> insert records if they are not duplicates. I thought the following
>>> would work as it does prevent duplicates but it inserts a record
>>> for each record found that is not a duplicate. I'm having trouble
>>> getting it to insert just one record when a duplicate is not found.
>>>
>>> Arrays:
>>> filename has file names
>>> filepath has virtual path names
>>> Ex. file.asp /asp/file.asp
>>>
>>> for i = 0 to ubound(filename)
>>> strSQL = "INSERT INTO listing (filename, filepath) SELECT " &
>>> filename(i) & ", " & filepath(i) & " FROM listing WHERE NOT EXISTS
>>> (SELECT " & filepath(i) & " FROM listing WHERE filepath = '" &
>>> filepath(i) & "'"
>>> next
>>>
>> Try running just the "SELECT ... " portion using non-duplicate
>> inputs. That should show you what the problem is.
>
> Hi Bob. I'm not sure what you're saying re: 'using non-duplicate
> inputs.'

I meant, using filename and filepath values that do not exist in the table.
(I assume you realize there were syntax errors in your query ... )

I just tested the following query and it works as you wish it to:

INSERT INTO listing (filename,filepath)
SELECT TOP 1 'E','F' FROM listing WHERE NOT EXISTS (SELECT * FROM listing
WHERE filepath = 'F')

The first time I ran it, it inserted a record. The second time I ran it,
zero records were inserted since the F record already existed.
There is a flaw:
If listing is empty, you will never insert a record with this query. There
needs to be at least one record in listing for it to work. I would suggest
changing it to:
INSERT INTO listing (filename,filepath)
SELECT TOP 1 'E','F' FROM MSysObjects WHERE NOT EXISTS (SELECT * FROM
listing WHERE filepath = 'F')


If I were you, I would use this sql to create a saved query, parameterize
it, and run the saved query from ASP.

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Preventing duplicates

"Bob Barrows [MVP]" wrote in message
news:%23Db8DCPfFHA.3936@tk2msftngp13.phx.gbl...
: Roland Hall wrote:
: > "Bob Barrows [MVP]" wrote in message
: > news:evpQsDNfFHA.2472@TK2MSFTNGP15.phx.gbl...
: >> Roland Hall wrote:
: >>> I need some help with a dynamic SQL query in Access. I want to
: >>> insert records if they are not duplicates. I thought the following
: >>> would work as it does prevent duplicates but it inserts a record
: >>> for each record found that is not a duplicate. I'm having trouble
: >>> getting it to insert just one record when a duplicate is not found.
: >>>
: >>> Arrays:
: >>> filename has file names
: >>> filepath has virtual path names
: >>> Ex. file.asp /asp/file.asp
: >>>
: >>> for i = 0 to ubound(filename)
: >>> strSQL = "INSERT INTO listing (filename, filepath) SELECT " &
: >>> filename(i) & ", " & filepath(i) & " FROM listing WHERE NOT EXISTS
: >>> (SELECT " & filepath(i) & " FROM listing WHERE filepath = '" &
: >>> filepath(i) & "'"
: >>> next
: >>>
: >> Try running just the "SELECT ... " portion using non-duplicate
: >> inputs. That should show you what the problem is.
: >
: > Hi Bob. I'm not sure what you're saying re: 'using non-duplicate
: > inputs.'
:
: I meant, using filename and filepath values that do not exist in the
table.
: (I assume you realize there were syntax errors in your query ... )

No. What, the single quotes?

: I just tested the following query and it works as you wish it to:
:
: INSERT INTO listing (filename,filepath)
: SELECT TOP 1 'E','F' FROM listing WHERE NOT EXISTS (SELECT * FROM listing
: WHERE filepath = 'F')

So TOP 1 is the key?

: The first time I ran it, it inserted a record. The second time I ran it,
: zero records were inserted since the F record already existed.
: There is a flaw:
: If listing is empty, you will never insert a record with this query. There
: needs to be at least one record in listing for it to work. I would suggest
: changing it to:
: INSERT INTO listing (filename,filepath)
: SELECT TOP 1 'E','F' FROM MSysObjects WHERE NOT EXISTS (SELECT * FROM
: listing WHERE filepath = 'F')

: If I were you, I would use this sql to create a saved query, parameterize
: it, and run the saved query from ASP.

As soon as I figure out how to do that, I will.

BTW...

Microsoft JET Database Engine error '80040e09'

Record(s) cannot be read; no read permission on 'MSysObjects'.

/lab/recurse3.asp, line 53


--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

Re: Preventing duplicates

Roland Hall wrote:
>
>> If I were you, I would use this sql to create a saved query,
>> parameterize it, and run the saved query from ASP.
>
> As soon as I figure out how to do that, I will.

Does this help?
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/b3d322b882a604bd

>
> BTW...
>
> Microsoft JET Database Engine error '80040e09'
>
> Record(s) cannot be read; no read permission on 'MSysObjects'.
>
> /lab/recurse3.asp, line 53

Oh! I didn't test it from ASP!

Hmm, you're right. No read permissions. It looks like you need to use
workgroup security in order to enable IUSR to access the system tables.

That's too much trouble.

Instead of MSysObjects, create a table containing a single row (it does not
matter how many columns it contains) and use that in your query. You just
need to have a table that is guaranteed to contain at least one row. If you
make sure it never contains more than one row, you can eliminate the "TOP 1"
from the subquery. So the query should look like:

INSERT INTO listing (filename,filepath)
SELECT 'E','F' FROM SingleRowTable WHERE NOT EXISTS (SELECT * FROM
listing WHERE filepath = 'F')

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Preventing duplicates

"Bob Barrows [MVP]" wrote in message
news:u53naaWfFHA.3460@TK2MSFTNGP10.phx.gbl...
: Roland Hall wrote:
: >
: >> If I were you, I would use this sql to create a saved query,
: >> parameterize it, and run the saved query from ASP.
: >
: > As soon as I figure out how to do that, I will.
:
: Does this help?
:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/b3d322b882a604bd

Yes! Very helpful. Thank you.

: > BTW...
: >
: > Microsoft JET Database Engine error '80040e09'
: >
: > Record(s) cannot be read; no read permission on 'MSysObjects'.
: >
: > /lab/recurse3.asp, line 53
:
: Oh! I didn't test it from ASP!
:
: Hmm, you're right. No read permissions. It looks like you need to use
: workgroup security in order to enable IUSR to access the system tables.
:
: That's too much trouble.

It's hosted also.

: Instead of MSysObjects, create a table containing a single row (it does
not
: matter how many columns it contains) and use that in your query. You just
: need to have a table that is guaranteed to contain at least one row. If
you
: make sure it never contains more than one row, you can eliminate the "TOP
1"
: from the subquery. So the query should look like:
:
: INSERT INTO listing (filename,filepath)
: SELECT 'E','F' FROM SingleRowTable WHERE NOT EXISTS (SELECT * FROM
: listing WHERE filepath = 'F')

You know, that was the most confusing part for me. Every example I saw used
two tables and I had just one.
Thanks Bob.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

Re: Preventing duplicates

"Bob Barrows [MVP]" wrote in message
news:u53naaWfFHA.3460@TK2MSFTNGP10.phx.gbl...
: Roland Hall wrote:
: >
: >> If I were you, I would use this sql to create a saved query,
: >> parameterize it, and run the saved query from ASP.
: >
: > As soon as I figure out how to do that, I will.
:
: Does this help?
:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/b3d322b882a604bd
:
: >
: > BTW...
: >
: > Microsoft JET Database Engine error '80040e09'
: >
: > Record(s) cannot be read; no read permission on 'MSysObjects'.
: >
: > /lab/recurse3.asp, line 53
:
: Oh! I didn't test it from ASP!
:
: Hmm, you're right. No read permissions. It looks like you need to use
: workgroup security in order to enable IUSR to access the system tables.
:
: That's too much trouble.
:
: Instead of MSysObjects, create a table containing a single row (it does
not
: matter how many columns it contains) and use that in your query. You just
: need to have a table that is guaranteed to contain at least one row. If
you
: make sure it never contains more than one row, you can eliminate the "TOP
1"
: from the subquery. So the query should look like:
:
: INSERT INTO listing (filename,filepath)
: SELECT 'E','F' FROM SingleRowTable WHERE NOT EXISTS (SELECT * FROM
: listing WHERE filepath = 'F')

Ok, it appears I have it.

I created a dummy table, called dummy, same two fields. I put one dummy
record in it.

My parameterized query is:
INSERT INTO listing ( filename, filepath )
SELECT [qfilename], [qfilepath]
FROM dummy
WHERE NOT EXISTS (SELECT * FROM listing WHERE filepath = [qfilepath]);

It was saved as qlisting

It prompts me for the filepath first when run in Access so... my ADO
statement is:
conn.qlisting filepath(i), filename(i)

Thanks for all the help and the info.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp