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 6 2254
sql = "IF NOT EXISTS (SELECT 1 FROM tblPageWatch WHERE ListingsID = '" &
ListingsID & "' AND CustomerID = '" & customerID & "')" & _
"INSERT ... "
"jason" <ja***@catamara nco.com> wrote in message
news:uY******** ******@tk2msftn gp13.phx.gbl... 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" <aa***@TRASHasp faq.com> wrote in message
news:eZ******** ******@TK2MSFTN GP09.phx.gbl... sql = "IF NOT EXISTS (SELECT 1 FROM tblPageWatch WHERE ListingsID = '" & ListingsID & "' AND CustomerID = '" & customerID & "')" & _ "INSERT ... "
"jason" <ja***@catamara nco.com> wrote in message news:uY******** ******@tk2msftn gp13.phx.gbl... 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(ListingsI D) FROM tblPageWatch WHERE ListingsID = " &
ListingsID & " AND CustomerID = " & customerID
set rs = conn.execute(sq l)
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? :>)
tblThingsAboutT heCutomerInform ationGoHereInTh isTable = $5.00
Bob Lehmann
"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote in message
news:eN******** ******@TK2MSFTN GP09.phx.gbl... 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(ListingsI D) FROM tblPageWatch WHERE ListingsID = " & ListingsID & " AND CustomerID = " & customerID
set rs = conn.execute(sq l)
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" <bo*@activeinet .com> wrote in message
news:u4******** ******@TK2MSFTN GP12.phx.gbl... Perhaps he's paid by the keystroke? :>)
tblThingsAboutT heCutomerInform ationGoHereInTh isTable = $5.00
Bob Lehmann
"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote in message news:eN******** ******@TK2MSFTN GP09.phx.gbl... 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(ListingsI D) FROM tblPageWatch WHERE ListingsID = " & ListingsID & " AND CustomerID = " & customerID
set rs = conn.execute(sq l)
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 (
adExecuteNoReco rds + adCmdText). Both of these things make the object's job
easier and improve efficiency. For other ideas, see http://www.aspfaq.com/2424#db This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: WSeeger |
last post by:
When creating a new class, is it encouraged to always include error
handling routines within your LET and GET procedures? It's seems that
most text books never seem to include much about error handling within
classes. Just hoping to hear some programmer's thoughts on error
handling.
|
by: Hans-Joachim Widmaier |
last post by:
Hi all.
Handling files is an extremely frequent task in programming, so most
programming languages have an abstraction of the basic files offered by
the underlying operating system. This is indeed also true for our language
of choice, Python. Its file type allows some extraordinary convenient
access like:
for line in open("blah"):
handle_line(line)
|
by: Christian Christmann |
last post by:
Hi,
assert and error handling can be used for similar purposes.
When should one use assert instead of try/catch and in which
cases the error handling is preferable?
I've read somewhere that assert could be used to start
an interactive debugger automatically. How do I realize that
on a Linux machine using gcc?
|
by: Anthony England |
last post by:
Everyone knows that global variables get re-set in an mdb when an un-handled
error is encountered, but it seems that this also happens when the variable
is defined as private at form-level.
So if "global variables get re-set" doesn't tell the whole story, then what
does?
***please note***
I'm not looking for a solution - I'm looking for a more detailed description
of what happens when an un-handled error occurs - possibly with help file...
|
by: Stefan Johansson |
last post by:
Hi all
I'am moving from Visual Foxpro and have a question
regarding "best practice" error handling in vb .net.
In VFP I have always used a "central" error handling
object in order to have a easy and reusable way of
handling all errors in a program.
The VB 6 coding examples I have seen there has always
been error handling code in each program module.
| |
by: Al Williams |
last post by:
Hi,
I have error handling in place throughout my application. I also start the
application wrapped in error handling code to catch any unexpected
exceptions (i.e. exceptions that occur where I haven't placed error handling
code).
When I run my app from the IDE, the unhandled errors are caught by the error
handling code in my Sub Main routine and the error details are logged to a
text file and optionally e-mailed to me for follow-up.
|
by: Metal Dave |
last post by:
I do not understand the error handling of SQL Server here. Any error in
bulk insert seems to halt the current T-SQL statement entirely, rendering
it impossible to log an error. The first statement below executes as
expected, and were I to replace "print" with something meaningful I could
do some useful error handling. The second statement just seems to totally
bail out after the error, preventing me from doing any useful error
handling....
|
by: Anthony England |
last post by:
(sorry for the likely repost, but it is still not showing on my news server
and after that much typing, I don't want to lose it)
I am considering general error handling routines and have written a sample
function to look up an ID in a table. The function returns True if it can
find the ID and create a recordset based on that ID, otherwise it returns
false.
**I am not looking for comments on the usefulness of this function - it is
|
by: Lysander |
last post by:
Thought I would give something back with a few articles. This article is a bit of code to add error handling. When I have time, I want to write articles on multilingual databases, and Access Security, but I'll start with something short and simple
This code was written in Access 2003 but should be valid in Access 2000
By default, when you start a new module, either in a form or report, or a global module, Access does not declare Option...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |