如何为一对多关系表编写insert语句以插入dataTable [英] how to write insert statements for one to many relationships tables to insert with dataTable

查看:88
本文介绍了如何为一对多关系表编写insert语句以插入dataTable的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在数据集中有3个表

当我点击保存按钮时,

i想要使用数据适配器将这些表添加到数据库表中

所有这3个表的主键都是 sql生成的自动编号



关系出货船Invoice,InvoiceProduct,InvoiceProductExp表是:

InvoiceNo有很多InvoiceProductNo

InvoiceProductNo有很多InvoiceProductExpNo



以下代码无法解决这些关系



  DECLARE   @ InvoiceNo   INT  
DECLARE @ InvoiceProductNo INT

INSERT INTO 发票([日期])
VALUES (GETDAT E())

SELECT @ InvoiceNo = SCOPE_IDENTITY ()

INSERT INTO InvoiceProduct ([InvoiceNo])
VALUES @ InvoiceNo

SELECT @ InvoiceProductNo = SCOPE_IDENTITY ()

INSERT INTO InvoiceProductExp([InvoiceProductNo],[InvoiceNo])
VALUES @ InvoiceProductNo @ InvoiceNo

解决方案

您需要使用:@@IDENTITY(T-SQL) [<小时ef =http://msdn.microsoft.com/en-us/library/ms187342.aspxtarget =_ blanktitle =New Window> ^ ]而不是 SCOPE_IDENTITY( )

  DECLARE   @ InvoiceNo   INT  
DECLARE @ InvoiceProductNo INT

INSERT INTO 发​​票([日期])
VALUES (GETDATE( ))

SELECT @ InvoiceNo = @@ IDENTITY ()

INSERT INTO InvoiceProduct ([InvoiceNo])
VALUES @ InvoiceNo

SELECT @ InvoiceProductNo = @@ IDENTITY ()

INSERT INTO InvoiceProductExp([ InvoiceProductNo],[InvoiceNo])
VALUES @ InvoiceProductNo @ InvoiceNo


i have 3 tables in dataset
when i click save button,
i want to add these tables to database tables using data adapter
all these 3 tables primary keys are sql generated auto number.

relation ships Invoice, InvoiceProduct , InvoiceProductExp tables are:
InvoiceNo has many InvoiceProductNo
InvoiceProductNo has many InvoiceProductExpNo

the following code can not solve these relaionship

DECLARE @InvoiceNo INT
DECLARE @InvoiceProductNo INT

INSERT INTO Invoice ([Date])
VALUES (GETDATE())

SELECT @InvoiceNo = SCOPE_IDENTITY()

INSERT INTO InvoiceProduct([InvoiceNo])
VALUES (@InvoiceNo)

SELECT @InvoiceProductNo = SCOPE_IDENTITY()

INSERT INTO InvoiceProductExp ([InvoiceProductNo], [InvoiceNo])
VALUES (@InvoiceProductNo, @InvoiceNo)

解决方案

You need to use: @@IDENTITY(T-SQL)[^] instead SCOPE_IDENTITY()

DECLARE @InvoiceNo INT
DECLARE @InvoiceProductNo INT

INSERT INTO Invoice ([Date])
VALUES (GETDATE())

SELECT @InvoiceNo = @@IDENTITY()

INSERT INTO InvoiceProduct([InvoiceNo])
VALUES (@InvoiceNo)

SELECT @InvoiceProductNo = @@IDENTITY()

INSERT INTO InvoiceProductExp ([InvoiceProductNo], [InvoiceNo])
VALUES (@InvoiceProductNo, @InvoiceNo)


这篇关于如何为一对多关系表编写insert语句以插入dataTable的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆