首先插入2张带有ID的表格(Insert into 2 tables with id from first)

首先插入2张带有ID的表格(Insert into 2 tables with id from first)

我有两张桌子

tbl_orgs ：

tbl_orgs

和tbl_location_records ：

tbl_location_records

我正在使用存储过程将数据插入到这些表中。

插入组织

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spInsOrg] (@orgName nvarchar(50), @orgCity nvarchar(50), @orgArea nvarchar(50), @orgTel nvarchar(50), @orgEmail nvarchar(50), @orgType nvarchar(50), @orgStatus nvarchar(50), @strOwner nvarchar(50), @db_tstamp datetime2) AS SET NOCOUNT OFF; INSERT INTO [tbl_orgs] ([orgName], [orgCity], [orgArea], [orgTel], [orgEmail], [orgType], [orgStatus], [strOwner], [db_tstamp]) VALUES (@orgName, @orgCity, @orgArea, @orgTel, @orgEmail, @orgType, @orgStatus, @strOwner, @db_tstamp); SELECT orgID, orgName, orgCity, orgArea, orgTel, orgEmail, orgType, orgStatus, strOwner, db_tstamp FROM tbl_orgs WHERE (orgID = SCOPE_IDENTITY())

插入位置记录

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spInsLoc] (@userID int, @orgID int, @jobID int, @strLat numeric(18, 0), @strLong numeric(18, 0), @strOwner varchar(50), @db_tstamp datetime2) AS SET NOCOUNT OFF; INSERT INTO [tbl_location_records] ([userID], [orgID], [jobID], [strLat], [strLong], [strOwner], [db_tstamp]) VALUES (@userID, @orgID, @jobID, @strLat, @strLong, @strOwner, @db_tstamp); SELECT recordID, userID, orgID, jobID, strLat, strLong, strOwner, db_tstamp FROM tbl_location_records WHERE (recordID = SCOPE_IDENTITY())

一旦组织记录成功插入，我想使用单个表单来添加插入组织的位置记录。

protected void btnSubmit_Click(object sender, EventArgs e) { string cs = ConfigurationManager.ConnectionStrings["IBS_3"].ConnectionString; using (SqlConnection conn = new SqlConnection(cs)) { SqlCommand cmd = new SqlCommand("spInsOrg", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("orgName", SqlDbType.NVarChar).Value = txtOrgName.Text; cmd.Parameters.Add("orgCity", SqlDbType.NVarChar).Value = txtCity.Text; cmd.Parameters.Add("orgArea", SqlDbType.NVarChar).Value = txtArea.Text; cmd.Parameters.Add("orgTel", SqlDbType.NVarChar).Value = txtTele.Text; cmd.Parameters.Add("orgEmail", SqlDbType.NVarChar).Value = txtEmail.Text; cmd.Parameters.Add("orgType", SqlDbType.NVarChar).Value = txtOrgType.Text; cmd.Parameters.Add("orgStatus", SqlDbType.NVarChar).Value = txtStatus.Text; cmd.Parameters.Add("@strOwner", SqlDbType.VarChar).Value = User.Identity.Name; cmd.Parameters.Add("@db_tstamp", SqlDbType.DateTime2).Value = DateTime.Now; conn.Open(); cmd.ExecuteNonQuery(); SqlCommand cmdloc = new SqlCommand("spInsLoc", conn); cmdloc.CommandType = CommandType.StoredProcedure; cmdloc.Parameters.Add("orgID", SqlDbType.Int).Value = } }

试图从这个链接得到一些理解，但我对此毫无头绪..

插入到一个表中，将ID插入到第二个表中

任何帮助赞赏。

谢谢。

I have 2 tables

tbl_orgs:

tbl_orgs

and tbl_location_records:

tbl_location_records

I am using a stored procedure to insert data into these tables.

Insert Organization

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spInsOrg] (@orgName nvarchar(50), @orgCity nvarchar(50), @orgArea nvarchar(50), @orgTel nvarchar(50), @orgEmail nvarchar(50), @orgType nvarchar(50), @orgStatus nvarchar(50), @strOwner nvarchar(50), @db_tstamp datetime2) AS SET NOCOUNT OFF; INSERT INTO [tbl_orgs] ([orgName], [orgCity], [orgArea], [orgTel], [orgEmail], [orgType], [orgStatus], [strOwner], [db_tstamp]) VALUES (@orgName, @orgCity, @orgArea, @orgTel, @orgEmail, @orgType, @orgStatus, @strOwner, @db_tstamp); SELECT orgID, orgName, orgCity, orgArea, orgTel, orgEmail, orgType, orgStatus, strOwner, db_tstamp FROM tbl_orgs WHERE (orgID = SCOPE_IDENTITY())

Insert Location Record

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spInsLoc] (@userID int, @orgID int, @jobID int, @strLat numeric(18, 0), @strLong numeric(18, 0), @strOwner varchar(50), @db_tstamp datetime2) AS SET NOCOUNT OFF; INSERT INTO [tbl_location_records] ([userID], [orgID], [jobID], [strLat], [strLong], [strOwner], [db_tstamp]) VALUES (@userID, @orgID, @jobID, @strLat, @strLong, @strOwner, @db_tstamp); SELECT recordID, userID, orgID, jobID, strLat, strLong, strOwner, db_tstamp FROM tbl_location_records WHERE (recordID = SCOPE_IDENTITY())

I want to use single form to add inserted organization's location record once organization record successfully inserted.

protected void btnSubmit_Click(object sender, EventArgs e) { string cs = ConfigurationManager.ConnectionStrings["IBS_3"].ConnectionString; using (SqlConnection conn = new SqlConnection(cs)) { SqlCommand cmd = new SqlCommand("spInsOrg", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("orgName", SqlDbType.NVarChar).Value = txtOrgName.Text; cmd.Parameters.Add("orgCity", SqlDbType.NVarChar).Value = txtCity.Text; cmd.Parameters.Add("orgArea", SqlDbType.NVarChar).Value = txtArea.Text; cmd.Parameters.Add("orgTel", SqlDbType.NVarChar).Value = txtTele.Text; cmd.Parameters.Add("orgEmail", SqlDbType.NVarChar).Value = txtEmail.Text; cmd.Parameters.Add("orgType", SqlDbType.NVarChar).Value = txtOrgType.Text; cmd.Parameters.Add("orgStatus", SqlDbType.NVarChar).Value = txtStatus.Text; cmd.Parameters.Add("@strOwner", SqlDbType.VarChar).Value = User.Identity.Name; cmd.Parameters.Add("@db_tstamp", SqlDbType.DateTime2).Value = DateTime.Now; conn.Open(); cmd.ExecuteNonQuery(); SqlCommand cmdloc = new SqlCommand("spInsLoc", conn); cmdloc.CommandType = CommandType.StoredProcedure; cmdloc.Parameters.Add("orgID", SqlDbType.Int).Value = } }

Trying to get some understanding from this link but I m clueless on this..

Inserting to one table, insert the ID to second table

Any help appreciated.

Thanks.

最满意答案

将输出参数添加到第一个存储过程中：

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spInsOrg] ( @orgID int output, @orgName nvarchar(50), @orgCity nvarchar(50), @orgArea nvarchar(50), @orgTel nvarchar(50), @orgEmail nvarchar(50), @orgType nvarchar(50), @orgStatus nvarchar(50), @strOwner nvarchar(50), @db_tstamp datetime2 ) AS SET NOCOUNT OFF; INSERT INTO [tbl_orgs] ([orgName], [orgCity], [orgArea], [orgTel], [orgEmail], [orgType], [orgStatus], [strOwner], [db_tstamp]) VALUES (@orgName, @orgCity, @orgArea, @orgTel, @orgEmail, @orgType, @orgStatus, @strOwner, @db_tstamp); SELECT @orgID = SCOPE_IDENTITY()

将输出参数添加到命令..

using (SqlConnection conn = new SqlConnection(cs)) { SqlCommand cmd = new SqlCommand("spInsOrg", conn); cmd.CommandType = CommandType.StoredProcedure; var outParam = cmd.Parameters.Add("@orgID", SqlDbType.Int); outParam.Direction = ParameterDirection.Output; cmd.Parameters.Add("@orgName", SqlDbType.NVarChar).Value = txtOrgName.Text; cmd.Parameters.Add("@orgCity", SqlDbType.NVarChar).Value = txtCity.Text; cmd.Parameters.Add("@orgArea", SqlDbType.NVarChar).Value = txtArea.Text; cmd.Parameters.Add("@orgTel", SqlDbType.NVarChar).Value = txtTele.Text; cmd.Parameters.Add("@orgEmail", SqlDbType.NVarChar).Value = txtEmail.Text; cmd.Parameters.Add("@orgType", SqlDbType.NVarChar).Value = txtOrgType.Text; cmd.Parameters.Add("@orgStatus", SqlDbType.NVarChar).Value = txtStatus.Text; cmd.Parameters.Add("@strOwner", SqlDbType.VarChar).Value = User.Identity.Name; cmd.Parameters.Add("@db_tstamp", SqlDbType.DateTime2).Value = DateTime.Now; conn.Open(); cmd.ExecuteNonQuery(); var orgId = (int)outParam.Value; SqlCommand cmdloc = new SqlCommand("spInsLoc", conn); cmdloc.CommandType = CommandType.StoredProcedure; }

Add output parameter to the first stored procedure:

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spInsOrg] ( @orgID int output, @orgName nvarchar(50), @orgCity nvarchar(50), @orgArea nvarchar(50), @orgTel nvarchar(50), @orgEmail nvarchar(50), @orgType nvarchar(50), @orgStatus nvarchar(50), @strOwner nvarchar(50), @db_tstamp datetime2 ) AS SET NOCOUNT OFF; INSERT INTO [tbl_orgs] ([orgName], [orgCity], [orgArea], [orgTel], [orgEmail], [orgType], [orgStatus], [strOwner], [db_tstamp]) VALUES (@orgName, @orgCity, @orgArea, @orgTel, @orgEmail, @orgType, @orgStatus, @strOwner, @db_tstamp); SELECT @orgID = SCOPE_IDENTITY()

add output param to command..

using (SqlConnection conn = new SqlConnection(cs)) { SqlCommand cmd = new SqlCommand("spInsOrg", conn); cmd.CommandType = CommandType.StoredProcedure; var outParam = cmd.Parameters.Add("@orgID", SqlDbType.Int); outParam.Direction = ParameterDirection.Output; cmd.Parameters.Add("@orgName", SqlDbType.NVarChar).Value = txtOrgName.Text; cmd.Parameters.Add("@orgCity", SqlDbType.NVarChar).Value = txtCity.Text; cmd.Parameters.Add("@orgArea", SqlDbType.NVarChar).Value = txtArea.Text; cmd.Parameters.Add("@orgTel", SqlDbType.NVarChar).Value = txtTele.Text; cmd.Parameters.Add("@orgEmail", SqlDbType.NVarChar).Value = txtEmail.Text; cmd.Parameters.Add("@orgType", SqlDbType.NVarChar).Value = txtOrgType.Text; cmd.Parameters.Add("@orgStatus", SqlDbType.NVarChar).Value = txtStatus.Text; cmd.Parameters.Add("@strOwner", SqlDbType.VarChar).Value = User.Identity.Name; cmd.Parameters.Add("@db_tstamp", SqlDbType.DateTime2).Value = DateTime.Now; conn.Open(); cmd.ExecuteNonQuery(); var orgId = (int)outParam.Value; SqlCommand cmdloc = new SqlCommand("spInsLoc", conn); cmdloc.CommandType = CommandType.StoredProcedure; }