Handling Ref. Integ Error?

Discussion in 'ASP General' started by jason, Aug 15, 2003.

  1. jason

    jason Guest

    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
     
    jason, Aug 15, 2003
    #1
    1. Advertising

  2. 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
    >
    >
    >
    >
     
    Aaron Bertrand - MVP, Aug 15, 2003
    #2
    1. Advertising

  3. jason

    jason Guest

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

    >
    >
     
    jason, Aug 15, 2003
    #3
  4. > 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?
     
    Aaron Bertrand - MVP, Aug 15, 2003
    #4
  5. jason

    Bob Lehmann Guest

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

    tblThingsAboutTheCutomerInformationGoHereInThisTable = $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?
    >
    >
     
    Bob Lehmann, Aug 15, 2003
    #5
  6. jason

    jason Guest

    :) - 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? :>)
    >
    > tblThingsAboutTheCutomerInformationGoHereInThisTable = $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?
    > >
    > >

    >
    >
     
    jason, Aug 15, 2003
    #6
  7. > 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
     
    Aaron Bertrand - MVP, Aug 15, 2003
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Stylus Studio
    Replies:
    0
    Views:
    548
    Stylus Studio
    Oct 5, 2004
  2. Replies:
    0
    Views:
    368
  3. Replies:
    22
    Views:
    800
    peter koch
    Apr 30, 2008
  4. Replies:
    6
    Views:
    380
    James Kanze
    Apr 29, 2008
  5. Greg Hauptmann
    Replies:
    6
    Views:
    307
    ara.t.howard
    Aug 6, 2008
Loading...

Share This Page