Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    96

    Unanswered: Referential Integrity - Which Column violates this?

    Hi All,

    I am inserting into a table that hold several foreign keys from several tables.
    I'm performing this via a client (VB) and I only how to capture the error, but unable to determine which column/field is the one that violates referential integrity.

    Any one can shed some light here? Many thanks!

    Cyherus

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    Do you have any ddl that's behind the VB application? Can you match it with the values you want to insert?

  3. #3
    Join Date
    Jun 2004
    Posts
    96
    nope, I am basically connecting to the SQL server via odbc.. and using INSERT INTO queries to add records as I read from a text file..
    Since the SQL Server is able to throw me an error description that says which table and column name violates the RI, I thought I would be able to manage this with codes. That is create the primary record in the primary table (affected table) and resume to insert the record in the foreign table again..

    Cyherus

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It should be done with sp, not with FE-based action queries.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The text of the error message (in the errors collection) will tell you which foreign key caused the problem. The code is the same for them all, so it only tells you that a foreign key was the problem.

    -PatP

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If you do it in sp you can customize the way errors are returned to the client. And of course Errors (rdoErrors) collection should be looped to retrieve ALL the errors that came from the server.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2004
    Posts
    96
    Alright.. as per your advise.. I am now performing these actions on the BE via sp.
    now, I am using both return value, out parameters and capture @@ERROR.
    Issue is that the system it prompting me before I can capture the errors returned.. sp example:

    ALTER PROCEDURE dbo.sp_insert_bl
    (
    @AAA varchar(12),
    @BBB int,
    )
    AS
    DECLARE @err int

    SET NOCOUNT ON
    BEGIN TRAN
    INSERT INTO dbo.Table ([AAA], [BBB])
    VALUES (@AAA, @BBB)
    SELECT @err = @@ERROR
    IF @err <> 0
    BEGIN
    ROLLBACK TRAN
    RETURN @err
    END
    ELSE
    BEGIN
    SELECT @blid = SCOPE_IDENTITY()
    COMMIT TRAN
    RETURN 0
    END

    oh yes, using ado to execute exec the sp
    cmd.parameter...........
    cmd.execute
    cmd.parameter("return value")

    do you suggest using RAISERROR?

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Of course, so that you can interrogate Errors collection. How else were you planning to see the errors?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •