«

Is there an elegant way to check for the REFERENTIAL INTEGRITY VIOLATION in
Access via ASP. The reason I ask is that if the user attempts to refresh the
screen after implementing an INSERT he will be trying to insert an identical
record which thows up the following error (below). I could place On error
resume next before but I find this dangerous or am I overworrying. I could
preface the insert by doing a check for the record but this creates longer
code which warps my anti-debugging frame of mind - is there a better way?


----------- ERROR GENERATED -- WHEN EXECUTING INSERT -------
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again.
/catamaranco/listings/tracker.asp, line 93


'On error resume next
SQL = "INSERT INTO tblPageWatch (ListingsID, CustomerID) VALUES ("
SQL=SQL & "'" & Trim(ListingsID) & "', "
SQL=SQL & "'" & CustomerID & "')"

Response.Write "<BR>CustomerID" & CustomerID & "<BR>"
Response.Write SQL
cnn.Execute SQL, , adCmdText

sql = "IF NOT EXISTS (SELECT 1 FROM tblPageWatch WHERE ListingsID = '" &
ListingsID & "' AND CustomerID = '" & customerID & "')" & _
"INSERT ... "




"jason" <> wrote in message
news:...
> Is there an elegant way to check for the REFERENTIAL INTEGRITY VIOLATION
in
> Access via ASP. The reason I ask is that if the user attempts to refresh
the
> screen after implementing an INSERT he will be trying to insert an
identical
> record which thows up the following error (below). I could place On error
> resume next before but I find this dangerous or am I overworrying. I could
> preface the insert by doing a check for the record but this creates longer
> code which warps my anti-debugging frame of mind - is there a better
way?
>
>
> ----------- ERROR GENERATED -- WHEN EXECUTING INSERT -------
> The changes you requested to the table were not successful because they
> would create duplicate values in the index, primary key, or relationship.
> Change the data in the field or fields that contain duplicate data, remove
> the index, or redefine the index to permit duplicate entries and try
again.
> /catamaranco/listings/tracker.asp, line 93
>
>
> 'On error resume next
> SQL = "INSERT INTO tblPageWatch (ListingsID, CustomerID) VALUES ("
> SQL=SQL & "'" & Trim(ListingsID) & "', "
> SQL=SQL & "'" & CustomerID & "')"
>
> Response.Write "<BR>CustomerID" & CustomerID & "<BR>"
> Response.Write SQL
> cnn.Execute SQL, , adCmdText
>
>
>
>

Access 2000: Thanks, but it I am picking up an error for this SELECT-INSERT.
Is this a concantenation issue or could it be related to the data type
delimiters. I am ListingsID and CustomerID are both access numbers/numeric?
Do I have to assign something for adCmdText? Appreicate further help....

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'.
/catamaranco/listings/tracker2.asp, line 144


SQL = "IF NOT EXISTS (SELECT * FROM tblPageWatch WHERE ListingsID ='" &_
Trim(ListingsID) & "' AND CustomerID = '" & customerID & "')" &_

SQL = "INSERT INTO tblPageWatch (ListingsID, CustomerID) VALUES ("
SQL=SQL & "'" & Trim(ListingsID) & "', "
SQL=SQL & "'" & CustomerID & "')"

Response.Write "<P>" & SQL & "<P>"
cnn.Execute SQL, , adCmdText '//Line 144



"Aaron Bertrand - MVP" <> wrote in message
news:...
> sql = "IF NOT EXISTS (SELECT 1 FROM tblPageWatch WHERE ListingsID = '" &
> ListingsID & "' AND CustomerID = '" & customerID & "')" & _
> "INSERT ... "
>
>
>
>
> "jason" <> wrote in message
> news:...
> > Is there an elegant way to check for the REFERENTIAL INTEGRITY
VIOLATION
> in
> > Access via ASP. The reason I ask is that if the user attempts to refresh
> the
> > screen after implementing an INSERT he will be trying to insert an
> identical
> > record which thows up the following error (below). I could place On
error
> > resume next before but I find this dangerous or am I overworrying. I
could
> > preface the insert by doing a check for the record but this creates
longer
> > code which warps my anti-debugging frame of mind - is there a better
> way?
> >
> >
> > ----------- ERROR GENERATED -- WHEN EXECUTING INSERT -------
> > The changes you requested to the table were not successful because they
> > would create duplicate values in the index, primary key, or
relationship.
> > Change the data in the field or fields that contain duplicate data,
remove
> > the index, or redefine the index to permit duplicate entries and try
> again.
> > /catamaranco/listings/tracker.asp, line 93
> >
> >
> > 'On error resume next
> > SQL = "INSERT INTO tblPageWatch (ListingsID, CustomerID) VALUES ("
> > SQL=SQL & "'" & Trim(ListingsID) & "', "
> > SQL=SQL & "'" & CustomerID & "')"
> >
> > Response.Write "<BR>CustomerID" & CustomerID & "<BR>"
> > Response.Write SQL
> > cnn.Execute SQL, , adCmdText
> >
> >
> >
> >
>
>

> I am ListingsID and CustomerID are both access numbers/numeric?

Then why do you use quotes, and why would you need to trim a number?
Earlier, you sent:

> SQL = "INSERT INTO tblPageWatch (ListingsID, CustomerID) VALUES ("
> SQL=SQL & "'" & Trim(ListingsID) & "', "
> SQL=SQL & "'" & CustomerID & "')"

?

> Access 2000:

Then use:


sql = "SELECT COUNT(ListingsID) FROM tblPageWatch WHERE ListingsID = " &
ListingsID & " AND CustomerID = " & customerID

set rs = conn.execute(sql)

if clng(rs(0)) = 0 then
conn.execute "INSERT ...", , 129
else
response.write "The record already exists."
end if


You could also just say:

on error resume next
conn.execute "INSERT ... ", , 129
if err.number <> 0 then response.write "I assume the record already exists."
on error goto 0




What purpose does the tbl prefix serve, btw?

Perhaps he's paid by the keystroke? :>)

tblThingsAboutTheCutomerInformationGoHereInThisTab le = $5.00

Bob Lehmann

"Aaron Bertrand - MVP" <> wrote in message
news:...
> > I am ListingsID and CustomerID are both access numbers/numeric?
>
> Then why do you use quotes, and why would you need to trim a number?
> Earlier, you sent:
>
> > SQL = "INSERT INTO tblPageWatch (ListingsID, CustomerID) VALUES ("
> > SQL=SQL & "'" & Trim(ListingsID) & "', "
> > SQL=SQL & "'" & CustomerID & "')"
>
> ?
>
> > Access 2000:
>
> Then use:
>
>
> sql = "SELECT COUNT(ListingsID) FROM tblPageWatch WHERE ListingsID = " &
> ListingsID & " AND CustomerID = " & customerID
>
> set rs = conn.execute(sql)
>
> if clng(rs(0)) = 0 then
> conn.execute "INSERT ...", , 129
> else
> response.write "The record already exists."
> end if
>
>
> You could also just say:
>
> on error resume next
> conn.execute "INSERT ... ", , 129
> if err.number <> 0 then response.write "I assume the record already
exists."
> on error goto 0
>
>
>
>
> What purpose does the tbl prefix serve, btw?
>
>

- I guess there must be a better way - what is the definative way to name
tables and queries and modules - everyone seems to have a different way?
"Bob Lehmann" <> wrote in message
news:...
> Perhaps he's paid by the keystroke? :>)
>
> tblThingsAboutTheCutomerInformationGoHereInThisTab le = $5.00
>
> Bob Lehmann
>
> "Aaron Bertrand - MVP" <> wrote in message
> news:...
> > > I am ListingsID and CustomerID are both access numbers/numeric?
> >
> > Then why do you use quotes, and why would you need to trim a number?
> > Earlier, you sent:
> >
> > > SQL = "INSERT INTO tblPageWatch (ListingsID, CustomerID) VALUES ("
> > > SQL=SQL & "'" & Trim(ListingsID) & "', "
> > > SQL=SQL & "'" & CustomerID & "')"
> >
> > ?
> >
> > > Access 2000:
> >
> > Then use:
> >
> >
> > sql = "SELECT COUNT(ListingsID) FROM tblPageWatch WHERE ListingsID = " &
> > ListingsID & " AND CustomerID = " & customerID
> >
> > set rs = conn.execute(sql)
> >
> > if clng(rs(0)) = 0 then
> > conn.execute "INSERT ...", , 129
> > else
> > response.write "The record already exists."
> > end if
> >
> >
> > You could also just say:
> >
> > on error resume next
> > conn.execute "INSERT ... ", , 129
> > if err.number <> 0 then response.write "I assume the record already
> exists."
> > on error goto 0
> >
> >
> >
> >
> > What purpose does the tbl prefix serve, btw?
> >
> >
>
>

> am using the "tbl" prefix for all my tables and I use "qry_" for all my
> tables as I find I get confused switching back btw the query and table
pane

Tables shouldn't need a prefix. If it looks like a table, smells like a
table, quacks like a table... it's probably a table. Or something that acts
just like one, such as a view in SQL Server. Feel free to use q_ prefix for
queries, but isn't that enough to differentiate? Why also a tbl prefix on
tables? I can pick out the tables in both of these lists:

qryA
qryB
tblA
tblB

A
B
qryA
qryB

> Also, you mentioned a while ago PITA types for field names. I have been
> using myID for primary keys;

What is an ID? And what the heck does "my" have to do with anything? How
about naming the entity for what it represents, e.g. OrderID, CustomerID? I
have no idea what myID would contain... I might expect this table to contain
one row, with a single column containing the creator's Social Security
Number???

> and my_ID for foreign keys

I fail to see how an underscore is, in any fashion, an intuitive way to
denote or identify a foreign key. If you really, really, really, need to
know that a column is a foreign key, a prefix FK_ makes much more sense than
comparing myID and my_ID. This information is available in the metadata of
the database, and should not be complicating the schema itself.

You should investigate NCITS L8 Metadata Standards Committee naming
conventions, Celko summed it up here:
http://tinyurl.com/k5k9

> As a matter of interest which is your preferred method for Access in this
situation.

Not using Access. You should consider MSDE.

> Also, what is the signifcance of "129".....

Tells the connection object the kind of statement you're sending, and that
you don't expect any records back (
adExecuteNoRecords + adCmdText). Both of these things make the object's job
easier and improve efficiency. For other ideas, see
http://www.aspfaq.com/2424#db