How can I INSERT w/Constraints in other tables?

[trint]

This:

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

Here is the tblTravelDetailMember:
CREATE TABLE [tblTravelDetailMember] (
[TravelDetailUplineId] [int] NOT NULL ,
[TravelDetailId] [int] NOT NULL ,
[TravelDetailMemberTypeId] [int] NOT NULL ,
[memberId] [bigint] NOT NULL ,
[rankId] [int] NOT NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDetailMember] PRIMARY KEY CLUSTERED
(
[TravelDetailUplineId]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblTravelDetailMember_tblTravelDetail] FOREIGN KEY
(
[TravelDetailId]
) REFERENCES [tblTravelDetail] (
[TravelDetailId]
),
CONSTRAINT [FK_tblTravelDetailMember_tblTravelDetailMemberType]
FOREIGN KEY
(
[TravelDetailMemberTypeId]
) REFERENCES [tblTravelDetailMemberType] (
[TravelDetailMemberTypeId]
)
) ON [PRIMARY]
GO

I get these errors:

Additional information: INSERT statement conflicted with COLUMN FOREIGN
KEY
constraint 'FK_tblTravelDetailMember_tblTravelDetailMemberTyp e'. The
conflict
occurred in database 'tsNess', table 'tblTravelDetailMemberType',
column
'TravelDetailMemberTypeId'.

Additional information: Cannot insert the value NULL into column
'TravelDetailMemberTypeId', table 'tsNess.dbo.tblTravelDetailMember';
column does not allow nulls. INSERT fails.

Here is the table I am not doing an entry on:
CREATE TABLE [tblTravelDetailMemberType] (
[TravelDetailMemberTypeId] [int] NOT NULL ,
[Description] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDetailMemberType] PRIMARY KEY CLUSTERED
(
[TravelDetailMemberTypeId]
) ON [PRIMARY]
) ON [PRIMARY]
GO

How can I do an INSERT into the tblTravelDetailMember 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 tblTravelDetailMember(memberId, " +
" TravelDetailUplineId, " +
" rankId, " +
" TravelDetailId, " +
" CreatedDateTime, " +
" Operator) " +
"VALUES ('" + insertString2 + "', " +
" '" + insertString3 + "', " +
" '" + insertString4 + "', " +
" '" + insertString5 + "', " +
" '" + insertString6 + "', " +
" '" + insertString7 + "')";

You have a constraint that says that the
tblTravelDetailMember.TravelDetailMemberTypeId must exist in the
tblTravelDetailMemberType.TravelDetailMemberTypeId 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 TravelDetailMemberTypeId (recommended) in
your insert statement or define a default for this column.

HTH
JB
Here is the tblTravelDetailMember:
CREATE TABLE [tblTravelDetailMember] (
[TravelDetailUplineId] [int] NOT NULL ,
[TravelDetailId] [int] NOT NULL ,
[TravelDetailMemberTypeId] [int] NOT NULL ,
[memberId] [bigint] NOT NULL ,
[rankId] [int] NOT NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDetailMember] PRIMARY KEY CLUSTERED
(
[TravelDetailUplineId]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblTravelDetailMember_tblTravelDetail] FOREIGN KEY
(
[TravelDetailId]
) REFERENCES [tblTravelDetail] (
[TravelDetailId]
),
CONSTRAINT [FK_tblTravelDetailMember_tblTravelDetailMemberType]
FOREIGN KEY
(
[TravelDetailMemberTypeId]
) REFERENCES [tblTravelDetailMemberType] (
[TravelDetailMemberTypeId]
)
) ON [PRIMARY]
GO

I get these errors:

Additional information: INSERT statement conflicted with COLUMN FOREIGN
KEY
constraint 'FK_tblTravelDetailMember_tblTravelDetailMemberTyp e'. The
conflict
occurred in database 'tsNess', table 'tblTravelDetailMemberType',
column
'TravelDetailMemberTypeId'.

Additional information: Cannot insert the value NULL into column
'TravelDetailMemberTypeId', table 'tsNess.dbo.tblTravelDetailMember';
column does not allow nulls. INSERT fails.

Here is the table I am not doing an entry on:
CREATE TABLE [tblTravelDetailMemberType] (
[TravelDetailMemberTypeId] [int] NOT NULL ,
[Description] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDetailMemberType] PRIMARY KEY CLUSTERED
(
[TravelDetailMemberTypeId]
) ON [PRIMARY]
) ON [PRIMARY]
GO

How can I do an INSERT into the tblTravelDetailMember 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***********@gmail.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 TravelDetailMemberTypeId from the
tblTravelDetailMemberType 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
TravelDetailMemberType names from the tblTravelDetailMemberType table.

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

Also, TravelDetailMemberTypeId 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_General_CP1_CI_AS NULL ,
[TravelEventId] [int] NOT NULL ,
[OrderId] [int] NULL ,
[ItemID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PeriodID] [int] NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDetail] PRIMARY KEY CLUSTERED
(
[TravelDetailId]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblTravelDetail_tblTravelEvent] 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 ('" + deiinsertString2 + "', " +
" '" + deiinsertString3 + "', " +
" '" + eiinsertString1 + "', " +
" '" + deiinsertString5 + "', " +
" '" + deiinsertString6 + "', " +
" '" + deiinsertString7 + "', " +
" '" + deiinsertString8 + "', " +
" '" + insertString7 + "') " +
"SELECT @@IDENTITY AS 'Identity' ";

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

Thanks,
Trint

..Net programmer
tr***********@gmail.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_General_CP1_CI_AS NULL ,
[TravelEventId] [int] NOT NULL ,
[OrderId] [int] NULL ,
[ItemID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PeriodID] [int] NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDetail] PRIMARY KEY CLUSTERED
(
[TravelDetailId]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblTravelDetail_tblTravelEvent] 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 ('" + deiinsertString2 + "', " +
" '" + deiinsertString3 + "', " +
" '" + eiinsertString1 + "', " +
" '" + deiinsertString5 + "', " +
" '" + deiinsertString6 + "', " +
" '" + deiinsertString7 + "', " +
" '" + deiinsertString8 + "', " +
" '" + insertString7 + "') " +
"SELECT @@IDENTITY AS 'Identity' ";

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

Thanks,
Trint

.Net programmer
tr***********@gmail.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", deiinsertString2);
cmd.Parameters.Add("@Comments", deiinsertString3);
cmd.Parameters.Add("@TravelEventID", deiinsertString3);

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", deiinsertString2);
cmd.Parameters.Add("@Comments", deiinsertString3);
cmd.Parameters.Add("@TravelEventID", deiinsertString3);

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