Handling Ref. Integ Error?

[jason]

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

 

[Aaron Bertrand - MVP]

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

 

[jason]

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]

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?

 

[Bob Lehmann]

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

tblThingsAboutTheCutomerInformationGoHereInThisTable = $5.00

Bob Lehmann

 

[jason]

- 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?

 

[Aaron Bertrand - MVP]

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