我有两张桌子
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; }更多推荐
发布评论