关于一个存储过程执行的问题
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("数据库执行不成");
}
报这个错
求高手指点，刚学习，这个前台就这样的 啊
求解释清楚
所有回答(1)
0
建议这样添加SqlParameter：
com.Parameters.AddWithValue("@typeint", 0);
com.Parameters.AddWithValue("@StudentNumber", TextBox1.Text);
com.Parameters.AddWithValue("@StudentName", TextBox2.Text);
好像 少一個 對象。學生學號。你的第二個 變量。是文本，類型不匹配。
这个怎样理解啊
@無限遐想: 学号是自增的啊，那是在数据库里改变number变量的值还是在这里转换成int啊
那我的输出参数咋写啊
