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!

CyherusDo you have any ddl that's behind the VB application? Can you match it with the values you want to insert?|||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|||It should be done with sp, not with FE-based action queries.|||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|||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.|||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?|||Of course, so that you can interrogate Errors collection. How else were you planning to see the errors?

Referencing textbox values in expressions

I have a table displaying output from a query. I have cells "a" and "b"
that contain expressions performing calculations on fields in the query. I
would like to create an expression in cell "c" that performs a calculation
on the values in cells "a" and "b".
I can't seem to figure out how to reference the values of the textboxes in
the table. Even putting something as simple as "=a.text" or "=a.value"
returns an error that "a" is not declared ("a" being the name of the
textbox control).
Considering you need "Fields!" for referencing fields and "Parameters!" for
referencing parameters, I suspect that there is something comparable for
referencing the report controls, but I can't find any references to it in
the Books Online.
Does anyone know if this is possible, and if so how?
Thanks.One thing you might want to consider is adding a field to your dataset. When
you have the list of fields that you would drag and drop from when in the
layout tab, do a right mouse click, add. Pick calculated field. Add the
expressions for cell a and cell b. Now these are considered a field like
anything else. You can now create an expression in c that refers to field a
and field b. I find things more understandable and cleaner. Plus, let's say
you want to do a sum or something else on a grouping. No problem, it is just
a field like any other.
This is not real discoverable but in some situations it really makes things
simplier.
HTH,
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kursplat" <newsgroup-spam@.microsoft.com> wrote in message
news:%23$H72k1%23FHA.2676@.TK2MSFTNGP10.phx.gbl...
>I have a table displaying output from a query. I have cells "a" and "b"
> that contain expressions performing calculations on fields in the query.
> I
> would like to create an expression in cell "c" that performs a calculation
> on the values in cells "a" and "b".
> I can't seem to figure out how to reference the values of the textboxes in
> the table. Even putting something as simple as "=a.text" or "=a.value"
> returns an error that "a" is not declared ("a" being the name of the
> textbox control).
> Considering you need "Fields!" for referencing fields and "Parameters!"
> for
> referencing parameters, I suspect that there is something comparable for
> referencing the report controls, but I can't find any references to it in
> the Books Online.
> Does anyone know if this is possible, and if so how?
> Thanks.|||I would do that if I could (I've tried), but the expression in the
source textboxes are calling functions in an Assembly, which I've
learned the hard way can't be called from the query (it crashes Visual
Studio every time I try).
So, I'm still looking for a way to have the expression of one textbox in
a table reference the value of another textbox in that table.
Thanks for the suggestion, though.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
news:e$iAA91#FHA.952@.TK2MSFTNGP10.phx.gbl:
> One thing you might want to consider is adding a field to your
> dataset. When you have the list of fields that you would drag and drop
> from when in the layout tab, do a right mouse click, add. Pick
> calculated field. Add the expressions for cell a and cell b. Now these
> are considered a field like anything else. You can now create an
> expression in c that refers to field a and field b. I find things more
> understandable and cleaner. Plus, let's say you want to do a sum or
> something else on a grouping. No problem, it is just a field like any
> other.
> This is not real discoverable but in some situations it really makes
> things simplier.
> HTH,
>|||I found it in another post. You use "ReportItems!" to reference the
controls on the report.
Kursplat <newsgroup-spam@.microsoft.com> wrote in news:eHD9dGB$FHA.1028
@.TK2MSFTNGP11.phx.gbl:
> So, I'm still looking for a way to have the expression of one textbox in
> a table reference the value of another textbox in that table.|||Hi Kursplat,
For a workaround, in your query, you can set up the calculations there,
then refer to them in cells a and b in your table. From there, you can add
another cell c, and use the Fields! method to call the values in cells a and
b and manipulate those values as you wish in an expression.
Does that make sense?
Cheers,
Daniel.
"Kursplat" wrote:
> I would do that if I could (I've tried), but the expression in the
> source textboxes are calling functions in an Assembly, which I've
> learned the hard way can't be called from the query (it crashes Visual
> Studio every time I try).
> So, I'm still looking for a way to have the expression of one textbox in
> a table reference the value of another textbox in that table.
> Thanks for the suggestion, though.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
> news:e$iAA91#FHA.952@.TK2MSFTNGP10.phx.gbl:
> > One thing you might want to consider is adding a field to your
> > dataset. When you have the list of fields that you would drag and drop
> > from when in the layout tab, do a right mouse click, add. Pick
> > calculated field. Add the expressions for cell a and cell b. Now these
> > are considered a field like anything else. You can now create an
> > expression in c that refers to field a and field b. I find things more
> > understandable and cleaner. Plus, let's say you want to do a sum or
> > something else on a grouping. No problem, it is just a field like any
> > other.
> >
> > This is not real discoverable but in some situations it really makes
> > things simplier.
> >
> > HTH,
> >
> >
>