c# - '' (procedure)" 的请求失败,因为它是一个表对象 - 错误
问题描述
我有三张桌子:
- dbo.学生
- dbo.Request
- dbo.RequestLine(前两者的关联表,包含复合主键STUD_ID和REQ_ID,以及属性REQ_DATE)
我正在尝试将记录插入dbo.RequestLine
表中。但是,我收到一条错误消息:
System.Data.SqlClient.SqlException:对过程“RequestLine”的请求失败,因为“RequestLine”是一个表对象。
以下是三个表的 DDL 语句:
CREATE TABLE [dbo].[Request]
(
[REQ_ID] [int] IDENTITY(10000005,1) NOT NULL,
[REQ_STAT] [varchar](12) NULL,
[REQ_CAT] [varchar](15) NULL,
[ADMIN_ID] [smallint] NULL,
[COOR_ID] [smallint] NULL,
CONSTRAINT [PK_Request]
PRIMARY KEY CLUSTERED ([REQ_ID] ASC)
)
ALTER TABLE [dbo].[Request] WITH CHECK
ADD CONSTRAINT [FK_Request_Admin]
FOREIGN KEY([ADMIN_ID]) REFERENCES [dbo].[Admin] ([ADMIN_ID])
GO
ALTER TABLE [dbo].[Request] CHECK CONSTRAINT [FK_Request_Admin]
GO
ALTER TABLE [dbo].[Request] WITH CHECK
ADD CONSTRAINT [FK_Request_Coordinator]
FOREIGN KEY([COOR_ID]) REFERENCES [dbo].[Coordinator] ([COOR_ID])
GO
ALTER TABLE [dbo].[Request] CHECK CONSTRAINT [FK_Request_Coordinator]
GO
CREATE TABLE [dbo].[Student]
(
[STUD_ID] [int] IDENTITY(1000009,1) NOT NULL,
[STUD_NAM] [varchar](30) NOT NULL,
[STUD_EMAIL] [varchar](50) NULL,
[CAMP_NAM] [varchar](50) NULL,
[CAMP_ZIP] [int] NULL,
[STUD_TYP] [char](5) NOT NULL,
CONSTRAINT [PK_Student]
PRIMARY KEY CLUSTERED ([STUD_ID] ASC)
)
GO
ALTER TABLE [dbo].[Student] WITH CHECK
ADD CONSTRAINT [FK_Student_Campus]
FOREIGN KEY([CAMP_NAM], [CAMP_ZIP]) REFERENCES [dbo].[Campus] ([CAMP_NAM], [CAMP_ZIP])
GO
ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_Campus]
GO
CREATE TABLE [dbo].[RequestLine]
(
[STUD_ID] [int] NOT NULL,
[REQ_ID] [int] NOT NULL,
[REQ_DATE] [date] NOT NULL,
CONSTRAINT [PK_RequestLine]
PRIMARY KEY CLUSTERED ([STUD_ID] ASC, [REQ_ID] ASC)
)
GO
ALTER TABLE [dbo].[RequestLine] WITH CHECK
ADD CONSTRAINT [FK_RequestLine_Request]
FOREIGN KEY([REQ_ID]) REFERENCES [dbo].[Request] ([REQ_ID])
GO
ALTER TABLE [dbo].[RequestLine] CHECK CONSTRAINT [FK_RequestLine_Request]
GO
ALTER TABLE [dbo].[RequestLine] WITH CHECK
ADD CONSTRAINT [FK_RequestLine_Student]
FOREIGN KEY([STUD_ID]) REFERENCES [dbo].[Student] ([STUD_ID])
GO
ALTER TABLE [dbo].[RequestLine] CHECK CONSTRAINT [FK_RequestLine_Student]
GO
这是我拥有的三个存储过程:
ALTER PROCEDURE [dbo].[Req_InsertNew]
(@REQ_STAT varchar(12),
@REQ_CAT varchar(15),
@ADMIN_ID smallint,
@COOR_ID smallint)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.Request (REQ_STAT, REQ_CAT, ADMIN_ID, COOR_ID)
VALUES (@REQ_STAT, @REQ_CAT, @ADMIN_ID, @COOR_ID);
DECLARE @REQ_ID INT
SET @REQ_ID = SCOPE_IDENTITY()
SELECT @REQ_ID
END
ALTER PROCEDURE [dbo].[Stud_InsertNew2]
(@STUD_NAM varchar(30),
@STUD_EMAIL varchar(50),
@CAMP_NAM varchar(50),
@CAMP_ZIP int,
@STUD_TYP char(5))
AS
BEGIN
DECLARE @temptable TABLE (newSTUDID int, STUDTYPE char);
SET NOCOUNT ON
INSERT INTO dbo.Student(STUD_NAM, STUD_EMAIL, CAMP_NAM, CAMP_ZIP, STUD_TYP)
VALUES (@STUD_NAM, @STUD_EMAIL, @CAMP_NAM, @CAMP_ZIP, @STUD_TYP);
DECLARE @STUD_ID INT
SET @STUD_ID = SCOPE_IDENTITY()
SELECT @STUD_ID
END
ALTER PROCEDURE [dbo].[ReqLine_Insert]
(@STUD_ID int,
@REQ_ID int,
@REQ_DATE date)
AS
BEGIN
INSERT INTO dbo.RequestLine (STUD_ID, REQ_ID, REQ_DATE)
VALUES (@STUD_ID, @REQ_ID, @REQ_DATE)
END
我正在尝试使用ExecuteScalar()
来检索REQ_ID
andSTUD_ID
并将它们放入RequestLine
表中。
这是 C# 代码:
int campzip = int.Parse(ddlCamp.SelectedValue.Trim());
int adminid = int.Parse(ddlAdmin.SelectedValue.Trim());
int coorid = int.Parse(txtCoorID.Text.Trim());
DateTime ReqDate = DateTime.ParseExact(txtReqDate.Text, "yyyy-MM-dd", null);
using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("cis-laredoConnectionString")))
{
connection.Open();
var cmd = new SqlCommand("dbo.Stud_InsertNew2", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@STUD_NAM", txtStdName.Text);
cmd.Parameters.AddWithValue("@STUD_EMAIL", txtStdEmail.Text);
cmd.Parameters.AddWithValue("@CAMP_NAM", ddlCamp.SelectedItem.ToString());
cmd.Parameters.AddWithValue("@CAMP_ZIP", campzip);
cmd.Parameters.AddWithValue("@STUD_TYP", "Other");
int STUD_ID = (int)cmd.ExecuteScalar();
cmd.Dispose();
connection.Close();
connection.Open();
var cmd1 = new SqlCommand("dbo.Req_InsertNew", connection);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@REQ_STAT", "Submitted");
cmd1.Parameters.AddWithValue("@REQ_CAT", "Uniform");
cmd1.Parameters.AddWithValue("@ADMIN_ID", adminid);
cmd1.Parameters.AddWithValue("@COOR_ID", coorid);;
int REQ_ID = (int)cmd1.ExecuteScalar();
cmd1.Dispose();
connection.Close();
// post back info. to dbo.RequestLine table in sql server
connection.Open();
var cmd2 = new SqlCommand("dbo.ReqLine_Insert", connection);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.AddWithValue("@STUD_ID", STUD_ID);
cmd2.Parameters.AddWithValue("@REQ_ID", REQ_ID);
cmd2.Parameters.Add("@REQ_DATE", SqlDbType.Date).Value = ReqDate;
cmd2.ExecuteNonQuery();
cmd2.Dispose();
connection.Close();
}
任何帮助,将不胜感激!
解决方案
你应该改变这个:
var cmd2 = new SqlCommand("dbo.RequestLine", connection);
有了这个:
var cmd2 = new SqlCommand("dbo.ReqLine_Insert", connection);
该对象dbo.RequestLine
是一个表,而您需要dbo.ReqLine_Insert
将数据插入该表的存储过程。
推荐阅读
- asp.net - gitignore ASP.NET 解决方案的正确路径
- frama-c - 如何使用 Frama-C 证明这个 C is_power_of_2 函数?
- ios - SwiftUI 2 使用 fileImporter 将文件从 Files 应用程序移动到文档目录
- java - 如何在for循环中除以2(Java)
- python - 如何在 Pandas 数据框切片中使用 apply 来设置多列的值
- python - 如何在循环中使用参数分析方法?
- php - 从多个选择中添加多个帖子元键
- powerbi - DAX 计数另一行(在另一个表中)的计数小于 1 的行
- python - 在迭代中将一个列表与其他列表进行比较,并在 Python 中的嵌套列表中返回差异
- windows - Python Watchdog 自定义事件处理程序未正确触发