首页 新闻 搜索 专区 学院

关于一个存储过程执行的问题

0
悬赏园豆:15 [已关闭问题] 关闭于 2012-03-25 18:18

要求在存储一个存储过程中实现增,删,该,查的操作,,单一的学生表

ALTER proc [dbo].[Students]
(
@typeint int ,--0,表示添加,1表示更新,2,表示删除,3表示查询
@StudentID int,
@StudentNumber int,
@StudentName varchar(50),
@StudentSex varchar(10),
@StudentClass varchar(50),
@StudentNianJi varchar(50),
@StudentCS datetime,
@StudentAdd varchar(50),
@StudentPhone varchar(50),
@StudentBeiZhu text,
@retint int output,
@retval nvarchar(100) output
)
as
declare @intranscount int,@blf_alph nvarchar(50),@bsl_brief nvarchar(4000)
set @intranscount=@@trancount
if @typeint=0
begin
if exists (select * from StudenInfo where StudentNumber=@StudentNumber)
set @retval='已经存在名称为['+@StudentNumber+']的学生编号,添加失败!'
set @retint=-1
end
if @intranscount=0 begin transaction
insert into StudenInfo (StudentNumber,StudentName,StudentSex,StudentClass,StudentNianJi,StudentCS,StudentAdd,StudentPhone,StudentBeiZhu)
values(@StudentNumber,@StudentName,@StudentSex,@StudentClass,@StudentNianJi,@StudentCS,@StudentAdd,@StudentPhone,@StudentBeiZhu)
if @@error<>0
select @bsl_brief = '向数据库中插入了一行学生编号为['+@StudentNumber+'],学生名称为【'+@StudentNumber+'】的数据,添加成功!'
if @@trancount>@intranscount commit transaction
set @retint=1
return 1
--判断更新
if @typeint=1
if not exists(select * from StudenInfo where StudentNumber=@StudentNumber)
set @retval='不存在要更新的学生信息,更新失败!'
set @retint =-1
return -1
--更新的操作
if exists(select * from StudenInfo where StudentNumber<>@StudentNumber and StudentID=@StudentID)
set @retval='已存在名称为['+ @StudentNumber+']的学生编号,更新失败'
set @retint =-1
return -1
--执行的操作
if exists(select * from StudenInfo where StudentNumber<>@StudentNumber and StudentID<>@StudentID)
select @bsl_brief = '学生编号将由['+StudentNumber+'] 更改为 ['+@StudentNumber+'],更新成功,' from StudenInfo where StudentNumber=@StudentNumber
update
StudenInfo
set
StudentNumber=@StudentNumber,
StudentName=@StudentNumber,
StudentSex=@StudentSex,
StudentClass=@StudentClass,
StudentNianJi=@StudentNianJi,
StudentCS=@StudentCS,
StudentAdd=@StudentAdd,
StudentPhone=@StudentPhone,
StudentBeiZhu=@StudentBeiZhu
where StudentID=@StudentID
if @@trancount>@intranscount commit transaction
set @retint=1
return 1
--执行删除的操作
if @typeint=2
if exists(select * from StudenInfo where StudentID<>@StudentID)
set @retval='不存在要删除的信息'
set @retint=-1
return -1
if @intranscount=0 begin transaction
if exists(select * from StudenInfo where StudentID=@StudentID)
select @bsl_brief = '成功删除了编号为['+@StudentID+'],学号为['+@StudentNumber+']的学生信息'
delete from StudenInfo where
StudentID=@StudentID
if @@trancount>@intranscount commit transaction
set @retint=1
return 1
----执行查询的操作
if @typeint=3
select * from StudenInfo
if @@trancount>@intranscount commit transaction
set @retint=1
return 1

我在vs中执行的时候我觉得逻辑上是没有错的,但报错

   SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SQLConnString"].ToString());
conn.Open();
string sql= "Students";
SqlCommand com = new SqlCommand(sql, conn);
com.CommandType = CommandType.StoredProcedure;
SqlParameter[] Parameterss = {
new SqlParameter("@typeint",SqlDbType.Int),
new SqlParameter("@StudentNumber",SqlDbType.Int),
new SqlParameter("@StudentName",SqlDbType.VarChar,50),
new SqlParameter("@StudentSex",SqlDbType.VarChar,10),
new SqlParameter("@StudentClass",SqlDbType.VarChar,50),
new SqlParameter("@StudentNianJi",SqlDbType.VarChar,50),
new SqlParameter("@StudentCS",SqlDbType.DateTime),
new SqlParameter("@StudentAdd",SqlDbType.VarChar,50),
new SqlParameter("@StudentPhone",SqlDbType.VarChar,50),
new SqlParameter("@StudentBeiZhu",SqlDbType.Text),
new SqlParameter("@retint",SqlDbType.Int),
new SqlParameter("@retval",SqlDbType.VarChar,100)
};
Parameterss[0].Value=0;
Parameterss[1].Value = TextBox1.Text;
Parameterss[2].Value = TextBox2.Text;
Parameterss[3].Value = TextBox3.Text;
Parameterss[4].Value = TextBox4.Text;
Parameterss[5].Value = TextBox5.Text;
Parameterss[6].Value = TextBox6.Text;
Parameterss[7].Value = TextBox7.Text;
Parameterss[8].Value = TextBox8.Text;
Parameterss[9].Value = TextBox9.Text;
Parameterss[10].Direction= ParameterDirection.Output;
Parameterss[11].Direction = ParameterDirection.Output;
com.Parameters.Add(Parameterss[0]);
com.Parameters.Add(Parameterss[1]);
com.Parameters.Add(Parameterss[2]);
com.Parameters.Add(Parameterss[3]);
com.Parameters.Add(Parameterss[4]);
com.Parameters.Add(Parameterss[5]);
com.Parameters.Add(Parameterss[6]);
com.Parameters.Add(Parameterss[7]);
com.Parameters.Add(Parameterss[8]);
com.Parameters.Add(Parameterss[9]);
com.Parameters.Add(Parameterss[10]);
com.Parameters.Add(Parameterss[11]);
com.ExecuteNonQuery();
if (com.ExecuteNonQuery() > 0)
{
if (int.Parse(Parameterss[10].Value.ToString()) > 0)
{

Response.Write(Parameterss[11].Value.ToString());
Response.Write("成功");


}

else
{
Response.Write(Parameterss[11].Value.ToString());
Response.Write("失败");


}



}
else
{
Response.Write("数据库执行不成");


}

报这个错
 

 

求高手指点,刚学习,这个前台就这样的 啊

求解释清楚

zon的主页 zon | 初学一级 | 园豆:137
提问于:2012-03-24 17:23
< >
分享
所有回答(1)
0

建议这样添加SqlParameter:

com.Parameters.AddWithValue("@typeint", 0);
com.Parameters.AddWithValue("@StudentNumber", TextBox1.Text);
com.Parameters.AddWithValue("@StudentName", TextBox2.Text);
dudu | 园豆:37126 (高人七级) | 2012-03-24 17:31

好像 少一個 對象。學生學號。你的第二個 變量。是文本,類型不匹配。

支持(0) 反对(0) 無限遐想 | 园豆:3740 (老鸟四级) | 2012-03-24 18:10

这个怎样理解啊

支持(0) 反对(0) zon | 园豆:137 (初学一级) | 2012-03-24 23:22

@無限遐想: 学号是自增的啊,那是在数据库里改变number变量的值还是在这里转换成int啊

支持(0) 反对(0) zon | 园豆:137 (初学一级) | 2012-03-24 23:26

那我的输出参数咋写啊

支持(0) 反对(0) zon | 园豆:137 (初学一级) | 2012-03-25 11:59
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册