Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Handling Ref. Integ Error?

Reply
Thread Tools

Handling Ref. Integ Error?

 
 
jason
Guest
Posts: n/a
 
      08-15-2003
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




 
Reply With Quote
 
 
 
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      08-15-2003
sql = "IF NOT EXISTS (SELECT 1 FROM tblPageWatch WHERE ListingsID = '" &
ListingsID & "' AND CustomerID = '" & customerID & "')" & _
"INSERT ... "




"jason" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
>



 
Reply With Quote
 
 
 
 
jason
Guest
Posts: n/a
 
      08-15-2003
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> sql = "IF NOT EXISTS (SELECT 1 FROM tblPageWatch WHERE ListingsID = '" &
> ListingsID & "' AND CustomerID = '" & customerID & "')" & _
> "INSERT ... "
>
>
>
>
> "jason" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> >
> >
> >

>
>



 
Reply With Quote
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      08-15-2003
> 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?


 
Reply With Quote
 
Bob Lehmann
Guest
Posts: n/a
 
      08-15-2003
Perhaps he's paid by the keystroke? :>)

tblThingsAboutTheCutomerInformationGoHereInThisTab le = $5.00

Bob Lehmann

"Aaron Bertrand - MVP" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> > 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?
>
>



 
Reply With Quote
 
jason
Guest
Posts: n/a
 
      08-15-2003
- 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Perhaps he's paid by the keystroke? :>)
>
> tblThingsAboutTheCutomerInformationGoHereInThisTab le = $5.00
>
> Bob Lehmann
>
> "Aaron Bertrand - MVP" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > > 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?
> >
> >

>
>



 
Reply With Quote
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      08-15-2003
> 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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
signal handling and (structured) exception handling Peter C++ 34 10-17-2009 10:03 AM
python list handling and Lisp list handling Mark Tarver Python 22 04-26-2009 09:36 PM
Is faster handling hexadecimal values than handling chars? IƱaki Baz Castillo Ruby 1 04-15-2008 09:04 AM
cookie handling - accept from domain or specific sub-domains ?? cookie-monster Firefox 2 03-07-2005 01:13 PM
Thunderbirds handling of jpgs and gif attached to email Dan Firefox 6 10-05-2004 01:25 PM



Advertisments