使用外键为ASP.net创建WishList/ShopingCart mvc问题

Question

创建商店应用程序并具有两个主表Product和Customer。目的是创建一个Order表，客户可以从productgetall列表中进行选择，并将其添加到Order表中。

客户表

CREATE TABLE [dbo].[Customer] 
(
[CustomerId] INT IDENTITY (1, 1) NOT NULL,
[FirstName]  NVARCHAR(40)   NOT NULL,
[LastName]   NVARCHAR(20)   NOT NULL,
[Email]      NVARCHAR(60)   NOT NULL,
[Photo]      VARBINARY(MAX) NULL,
[password]   VARCHAR(300)   NULL,
[Country]    VARCHAR(50)    NULL,
CONSTRAINT [PK_Customer] 
PRIMARY KEY CLUSTERED ([CustomerId] ASC)
); 

产品表：

CREATE TABLE [dbo].[Product] 
(
[ProductId]       INT IDENTITY (1, 1) NOT NULL,
[ProductName]     NVARCHAR(50) NOT NULL,
[ProductDetails]  TEXT         NULL,
[ProductPrice]    INT          NOT NULL,
[ProductCategory] NVARCHAR(50) NULL,
PRIMARY KEY CLUSTERED ([ProductId] ASC)
);

订单表

CREATE TABLE [dbo].[Order] 
(
[OrderId]    INT IDENTITY (1, 1) NOT NULL,
[CustomerId] INT     NOT NULL,
[ProductId]  INT     NOT NULL,
[Date]       DATE    NULL,
[Time]       TIME(7) NULL,
CONSTRAINT [ORDER_PK] 
PRIMARY KEY CLUSTERED ([OrderId] ASC),
CONSTRAINT [CUSTOMER_FK] 
FOREIGN KEY ([CustomerId]) 
REFERENCES [dbo].[Customer] ([CustomerId]) 
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT [PRODUCT_FK] 
FOREIGN KEY ([ProductId]) 
REFERENCES [dbo].[Product] ([ProductId]) 
ON DELETE CASCADE ON UPDATE CASCADE
);

添加订单的C#代码

public void AddOrder(Order a)
{
using (DbConnection conn = new SqlConnection(ConnStr))
{
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandTimeout = 120;
cmd.CommandText = @"INSERT INTO [dbo].[Order] (CustomerId, ProductId, Date, Time)
VALUES (@CustomerId, @ProductId, @Date, @Time)";
cmd.AddParameter("@CustomerId", a.CustomerId, DbType.Int32);
cmd.AddParameter("@ProductId", a.ProductId, DbType.Int32);
cmd.AddParameter("@Date", a.Date, DbType.Date);
cmd.AddParameter("@Time", a.Time.ToString(), DbType.String);
conn.Open();
cmd.ExecuteScalar();
}
}
}

当通过web表单创建时，它在ExecuteScalar:上显示错误

系统。数据SqlClient。SqlException：'INSERT语句与FOREIGN KEY约束"CUSTOMER_FK"冲突。冲突发生在数据库"C:\USERS\USER\DESKTOP\2019 5LVL\DBSD\TRYWISHLIST\3\000005466\00005466\APP_DATA\KFCDB.MDF"，表"dbo.Customer"，列"CustomerId"中。声明已终止

Answer 1

您发送的CustomerId在dbo.Customer中不存在，请插入它。