How can I INSERT w/Constraints in other tables?

[trint]

This:

string strSQL2 = "INSERT INTO tblTravelDetail Member(memberId , " +
" TravelDetailUpl ineId, " +
" rankId, " +
" TravelDetailId, " +
" CreatedDateTime , " +
" Operator) " +
"VALUES ('" + insertString2 + "', " +
" '" + insertString3 + "', " +
" '" + insertString4 + "', " +
" '" + insertString5 + "', " +
" '" + insertString6 + "', " +
" '" + insertString7 + "')";

Here is the tblTravelDetail Member:
CREATE TABLE [tblTravelDetail Member] (
[TravelDetailUpl ineId] [int] NOT NULL ,
[TravelDetailId] [int] NOT NULL ,
[TravelDetailMem berTypeId] [int] NOT NULL ,
[memberId] [bigint] NOT NULL ,
[rankId] [int] NOT NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDet ailMember] PRIMARY KEY CLUSTERED
(
[TravelDetailUpl ineId]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblTravelDet ailMember_tblTr avelDetail] FOREIGN KEY
(
[TravelDetailId]
) REFERENCES [tblTravelDetail] (
[TravelDetailId]
),
CONSTRAINT [FK_tblTravelDet ailMember_tblTr avelDetailMembe rType]
FOREIGN KEY
(
[TravelDetailMem berTypeId]
) REFERENCES [tblTravelDetail MemberType] (
[TravelDetailMem berTypeId]
)
) ON [PRIMARY]
GO

I get these errors:

Additional information: INSERT statement conflicted with COLUMN FOREIGN
KEY
constraint 'FK_tblTravelDe tailMember_tblT ravelDetailMemb erType'. The
conflict
occurred in database 'tsNess', table 'tblTravelDetai lMemberType',
column
'TravelDetailMe mberTypeId'.

Additional information: Cannot insert the value NULL into column
'TravelDetailMe mberTypeId', table 'tsNess.dbo.tbl TravelDetailMem ber';
column does not allow nulls. INSERT fails.

Here is the table I am not doing an entry on:
CREATE TABLE [tblTravelDetail MemberType] (
[TravelDetailMem berTypeId] [int] NOT NULL ,
[Description] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NOT NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDet ailMemberType] PRIMARY KEY CLUSTERED
(
[TravelDetailMem berTypeId]
) ON [PRIMARY]
) ON [PRIMARY]
GO

How can I do an INSERT into the tblTravelDetail Member table if it won't
let me and what will I have to do in order to do this please?
Thanks,
Trint

[John B]

trint wrote:
This would probably be better on one of the SQL groups but

See In-line

This:

string strSQL2 = "INSERT INTO tblTravelDetail Member(memberId , " +
" TravelDetailUpl ineId, " +
" rankId, " +
" TravelDetailId, " +
" CreatedDateTime , " +
" Operator) " +
"VALUES ('" + insertString2 + "', " +
" '" + insertString3 + "', " +
" '" + insertString4 + "', " +
" '" + insertString5 + "', " +
" '" + insertString6 + "', " +
" '" + insertString7 + "')";

You have a constraint that says that the
tblTravelDetail Member.TravelDe tailMemberTypeI d must exist in the
tblTravelDetail MemberType.Trav elDetailMemberT ypeId column.
You are not specifying a value for this column in your insert statement
therefore it will use the default value or null if no default is defined.

Either specify a value for TravelDetailMem berTypeId (recommended) in
your insert statement or define a default for this column.

HTH
JB
Here is the tblTravelDetail Member:
CREATE TABLE [tblTravelDetail Member] (
[TravelDetailUpl ineId] [int] NOT NULL ,
[TravelDetailId] [int] NOT NULL ,
[TravelDetailMem berTypeId] [int] NOT NULL ,
[memberId] [bigint] NOT NULL ,
[rankId] [int] NOT NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDet ailMember] PRIMARY KEY CLUSTERED
(
[TravelDetailUpl ineId]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblTravelDet ailMember_tblTr avelDetail] FOREIGN KEY
(
[TravelDetailId]
) REFERENCES [tblTravelDetail] (
[TravelDetailId]
),
CONSTRAINT [FK_tblTravelDet ailMember_tblTr avelDetailMembe rType]
FOREIGN KEY
(
[TravelDetailMem berTypeId]
) REFERENCES [tblTravelDetail MemberType] (
[TravelDetailMem berTypeId]
)
) ON [PRIMARY]
GO

I get these errors:

Additional information: INSERT statement conflicted with COLUMN FOREIGN
KEY
constraint 'FK_tblTravelDe tailMember_tblT ravelDetailMemb erType'. The
conflict
occurred in database 'tsNess', table 'tblTravelDetai lMemberType',
column
'TravelDetailMe mberTypeId'.

Additional information: Cannot insert the value NULL into column
'TravelDetailMe mberTypeId', table 'tsNess.dbo.tbl TravelDetailMem ber';
column does not allow nulls. INSERT fails.

Here is the table I am not doing an entry on:
CREATE TABLE [tblTravelDetail MemberType] (
[TravelDetailMem berTypeId] [int] NOT NULL ,
[Description] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NOT NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDet ailMemberType] PRIMARY KEY CLUSTERED
(
[TravelDetailMem berTypeId]
) ON [PRIMARY]
) ON [PRIMARY]
GO

How can I do an INSERT into the tblTravelDetail Member table if it won't
let me and what will I have to do in order to do this please?
Thanks,
Trint

[Trint Smith]

John,
I've got most of it figured out now except this:

@@IDENTITY AS 'Identity'
How do I get the 'Identity' into a string?
Thanks,
Trint

.Net programmer
tr***********@g mail.com

*** Sent via Developersdex http://www.developersdex.com ***

[John B]

Trint Smith wrote:

John,
I've got most of it figured out now except this:

@@IDENTITY AS 'Identity'
How do I get the 'Identity' into a string?
Thanks,
Trint

@@IDENTITY will select the last identity (this is database wide and not
limited to a specific table (AFAIK).
Why do you need this?
What you want is a TravelDetailMem berTypeId from the
tblTravelDetail MemberType table.

Lets say you are filling these details out on a form.
You create controls for all the values.
You will probably have a combo box that lists all the
TravelDetailMem berType names from the tblTravelDetail MemberType table.

Then when the user presses save, you will want to get the
TravelDetailMem berTypeId from the combo box and pass that as part of
your insert statement so it saves the correct TravelDetailMem berType value.

Also, TravelDetailMem berTypeId is not defined as an IDENTITY column
(AutoIncrement) so @@IDENTITY will have no bearing on this.

JB

[Trint Smith]

John,
It is defined in another table:
CREATE TABLE [tblTravelDetail] (
[TravelDetailId] [int] IDENTITY (1, 1) NOT NULL ,
[MemberId] [bigint] NOT NULL ,
[Comments] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[TravelEventId] [int] NOT NULL ,
[OrderId] [int] NULL ,
[ItemID] [nvarchar] (15) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PeriodID] [int] NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDet ail] PRIMARY KEY CLUSTERED
(
[TravelDetailId]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblTravelDet ail_tblTravelEv ent] FOREIGN KEY
(
[TravelEventId]
) REFERENCES [tblTravelEvent] (
[TravelEventId]
)
) ON [PRIMARY]
GO

And here is my code on this table so far (That don't get the identity I
might add):

string strSQL5 = "INSERT INTO tblTravelDetail (MemberId, " +
" Comments, " +
" TravelEventId, " +
" OrderId, " +
" ItemID, " +
" PeriodID, " + //here is where we do the PeriodID lookup
calculation
" CreatedDateTime , " +
" Operator) " +
"VALUES ('" + deiinsertString 2 + "', " +
" '" + deiinsertString 3 + "', " +
" '" + eiinsertString1 + "', " +
" '" + deiinsertString 5 + "', " +
" '" + deiinsertString 6 + "', " +
" '" + deiinsertString 7 + "', " +
" '" + deiinsertString 8 + "', " +
" '" + insertString7 + "') " +
"SELECT @@IDENTITY AS 'Identity' ";

displayIdentity = Convert.ToStrin g(rs5.Fields["Identity"].Value);

Thanks,
Trint

..Net programmer
tr***********@g mail.com

*** Sent via Developersdex http://www.developersdex.com ***

[John B]

Trint Smith wrote:

This is a totally different table definition from your first post.
What is the question?

If it is the same error as earlier,
You must supply a value that exists in your foreign key table/column for
any foreign key columns.
tblTravelDetail .TravelEventId is your foreign key column.
You must supply a value for this column that exists in the
tblTravelEvent. TravelEventId column.

JB

John,
It is defined in another table:
CREATE TABLE [tblTravelDetail] (
[TravelDetailId] [int] IDENTITY (1, 1) NOT NULL ,
[MemberId] [bigint] NOT NULL ,
[Comments] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[TravelEventId] [int] NOT NULL ,
[OrderId] [int] NULL ,
[ItemID] [nvarchar] (15) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[PeriodID] [int] NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDet ail] PRIMARY KEY CLUSTERED
(
[TravelDetailId]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblTravelDet ail_tblTravelEv ent] FOREIGN KEY
(
[TravelEventId]
) REFERENCES [tblTravelEvent] (
[TravelEventId]
)
) ON [PRIMARY]
GO

And here is my code on this table so far (That don't get the identity I
might add):

string strSQL5 = "INSERT INTO tblTravelDetail (MemberId, " +
" Comments, " +
" TravelEventId, " +
" OrderId, " +
" ItemID, " +
" PeriodID, " + //here is where we do the PeriodID lookup
calculation
" CreatedDateTime , " +
" Operator) " +
"VALUES ('" + deiinsertString 2 + "', " +
" '" + deiinsertString 3 + "', " +
" '" + eiinsertString1 + "', " +
" '" + deiinsertString 5 + "', " +
" '" + deiinsertString 6 + "', " +
" '" + deiinsertString 7 + "', " +
" '" + deiinsertString 8 + "', " +
" '" + insertString7 + "') " +
"SELECT @@IDENTITY AS 'Identity' ";

displayIdentity = Convert.ToStrin g(rs5.Fields["Identity"].Value);

Thanks,
Trint

.Net programmer
tr***********@g mail.com

*** Sent via Developersdex http://www.developersdex.com ***

[Alan Samet]

This doesn't answer your immediate question, but it'll make your life a
whole lot easier when embedding SQL Statements into your CSharp code:

string strSQL5 = @"INSERT INTO tblTravelDetail
(
MemberID
, Comments
, TravelEventID
, ...
)
VALUES
(
@MemberID
, @Comments
, @TravelEventID
)";
cmd.Parameters. Add("@MemberID" , deiinsertString 2);
cmd.Parameters. Add("@Comments" , deiinsertString 3);
cmd.Parameters. Add("@TravelEve ntID", deiinsertString 3);

The @ literal string operator prefix allows you to write multiline
strings. Very useful if you must embed SQL statements in your C# code.
Also, using parameterized SQL Statements is very important, not only
for security (it prevents SQL injection), but also for performance as
it increases the chance of your execution plan being reused by SQL
Server. See SQL Server's Books Online for sp_executesql, which is what
ADO.NET uses behind the scenes.

-Alan

[trint]

Thanks Alan,
The only thing I need though at this point, is how can I get the last
Identity after an insert, using my c# app?
Thanks,
Trint

Alan Samet wrote:

This doesn't answer your immediate question, but it'll make your life a whole lot easier when embedding SQL Statements into your CSharp code:

string strSQL5 = @"INSERT INTO tblTravelDetail
(
MemberID
, Comments
, TravelEventID
, ...
)
VALUES
(
@MemberID
, @Comments
, @TravelEventID
)";
cmd.Parameters. Add("@MemberID" , deiinsertString 2);
cmd.Parameters. Add("@Comments" , deiinsertString 3);
cmd.Parameters. Add("@TravelEve ntID", deiinsertString 3);

The @ literal string operator prefix allows you to write multiline
strings. Very useful if you must embed SQL statements in your C# code. Also, using parameterized SQL Statements is very important, not only
for security (it prevents SQL injection), but also for performance as
it increases the chance of your execution plan being reused by SQL
Server. See SQL Server's Books Online for sp_executesql, which is what ADO.NET uses behind the scenes.

-Alan