首先我发下我的存储过程:
create proc InsertTbl
@insertTime datetime,
@updateTime datetime,
@question1 varchar(10),
@question2 varchar(10),
@question3 varchar(50),
@question3and1 varchar(200),
@question4 varchar(50),
@question4and1 varchar(200),
@question5 varchar(50),
@question5and1 varchar(200),
@question6 varchar(10),
@question7 varchar(10),
@question8 varchar(10),
@question9 varchar(10),
@question10 varchar(10),
@question11 varchar(10),
@question12 varchar(10),
@question13 varchar(50),
@question13and1 varchar(200),
@question14 varchar(50),
@question14and1 varchar(200),
@question15 varchar(10),
@question15and1 varchar(200),
@question16 varchar(50),
@question16and1 varchar(200),
@question17 varchar(50),
@question17and1 varchar(200),
@question18 varchar(10),
@question19 varchar(10),
@question20 varchar(40),
@question20and1 varchar(200),
@question21 varchar(10),
@question22 varchar(40),
@question23 varchar(40),
@question24 varchar(10),
@question25 varchar(200),
@id int output
as
insert into tblQuestion(insertTime,updateTime,question1,question2,question3,question3and1,question4,question4and1,question5,question5and1,question6,question7,question8,question9,question10,question11,question12,question13,question13and1,question14,question14and1,question15,question15and1,question16,question16and1,question17,question17and1,question18,question19,question20,question20and1,question21,question22,question23,question24,question25)
values(getdate(),getdate(),@question1,@question2,@question3,@question3and1,@question4,@question4and1,@question5,@question5and1,@question6,@question7,@question8,@question9,@question10,@question11,@question12,@question13,@question13and1,@question14,@question14and1,@question15,@question15and1,@question16,@question16and1,@question17,@question17and1,@question18,@question19,@question20,@question20and1,@question21,@question22,@question23,@question24,@question25)
set @id=@@identity
return 1然后我发下我的CLICK事件的后台代码:
//如何取值我就不发了,都是FOREACH遍历一些的结果SqlConnection cn = new SqlConnection(@"server=.\sqlexpress;uid=sa;pwd=;database=test");
SqlCommand cmd = new SqlCommand("InsertTbl", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@question1", SqlDbType.VarChar).Value = Question1;
cmd.Parameters.Add("@question2", SqlDbType.VarChar).Value = Question2;
cmd.Parameters.Add("@question3", SqlDbType.VarChar).Value = Question3;
cmd.Parameters.Add("@question3and1", SqlDbType.VarChar).Value = Question3and1;
cmd.Parameters.Add("@question4", SqlDbType.VarChar).Value = Question4;
cmd.Parameters.Add("@question4and1", SqlDbType.VarChar).Value = Question4and1;
cmd.Parameters.Add("@question5", SqlDbType.VarChar).Value = Question5;
cmd.Parameters.Add("@question5and1", SqlDbType.VarChar).Value = Question5and1;
cmd.Parameters.Add("@question6", SqlDbType.VarChar).Value = Question6;
cmd.Parameters.Add("@question7", SqlDbType.VarChar).Value = Question7;
cmd.Parameters.Add("@question8", SqlDbType.VarChar).Value = Question8;
cmd.Parameters.Add("@question9", SqlDbType.VarChar).Value = Question9;
cmd.Parameters.Add("@question10", SqlDbType.VarChar).Value = Question10;
cmd.Parameters.Add("@question11", SqlDbType.VarChar).Value = Question11;
cmd.Parameters.Add("@question12", SqlDbType.VarChar).Value = Question12;
cmd.Parameters.Add("@question13", SqlDbType.VarChar).Value = Question13;
cmd.Parameters.Add("@question13and1", SqlDbType.VarChar).Value = Question13and1;
cmd.Parameters.Add("@question14", SqlDbType.VarChar).Value = Question14;
cmd.Parameters.Add("@question14and1", SqlDbType.VarChar).Value = Question14and1;
cmd.Parameters.Add("@question15", SqlDbType.VarChar).Value = Question15;
cmd.Parameters.Add("@question15and1", SqlDbType.VarChar).Value = Question15and1;
cmd.Parameters.Add("@question16", SqlDbType.VarChar).Value = Question16;
cmd.Parameters.Add("@question16and1", SqlDbType.VarChar).Value = Question16and1;
cmd.Parameters.Add("@question17", SqlDbType.VarChar).Value = Question17;
cmd.Parameters.Add("@question17and1", SqlDbType.VarChar).Value = Question17and1;
cmd.Parameters.Add("@question18", SqlDbType.VarChar).Value = Question18;
cmd.Parameters.Add("@question19", SqlDbType.VarChar).Value = Question19;
cmd.Parameters.Add("@question20", SqlDbType.VarChar).Value = Question20;
cmd.Parameters.Add("@question20and1", SqlDbType.VarChar).Value = Question20and1;
cmd.Parameters.Add("@question21", SqlDbType.VarChar).Value = Question21;
cmd.Parameters.Add("@question22", SqlDbType.VarChar).Value = Question22;
cmd.Parameters.Add("@question23", SqlDbType.VarChar).Value = Question23;
cmd.Parameters.Add("@question24", SqlDbType.VarChar).Value = Question24;
cmd.Parameters.Add("@question25", SqlDbType.VarChar).Value = Question25;
cmd.Parameters["@id"].Direction = ParameterDirection.Output;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();然后是我运行后的结果:
此 SqlParameterCollection 中未包含带有 ParameterName“@id”的 SqlParameter。
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.IndexOutOfRangeException: 此 SqlParameterCollection 中未包含带有 ParameterName“@id”的 SqlParameter。源错误:
行 680: cmd.Parameters.Add("@question24", SqlDbType.VarChar).Value = Question24;
行 681: cmd.Parameters.Add("@question25", SqlDbType.VarChar).Value = Question25;
行 682: cmd.Parameters["@id"].Direction = ParameterDirection.Output;行 683: cn.Open();
行 684: cmd.ExecuteNonQuery();
源文件: h:\QuestionnaireSurvey11\Default.aspx.cs 行: 682 我看了半天我的代码应该没有问题啊,可就是找不出来,麻烦大家了,我先去睡了
create proc InsertTbl
@insertTime datetime,
@updateTime datetime,
@question1 varchar(10),
@question2 varchar(10),
@question3 varchar(50),
@question3and1 varchar(200),
@question4 varchar(50),
@question4and1 varchar(200),
@question5 varchar(50),
@question5and1 varchar(200),
@question6 varchar(10),
@question7 varchar(10),
@question8 varchar(10),
@question9 varchar(10),
@question10 varchar(10),
@question11 varchar(10),
@question12 varchar(10),
@question13 varchar(50),
@question13and1 varchar(200),
@question14 varchar(50),
@question14and1 varchar(200),
@question15 varchar(10),
@question15and1 varchar(200),
@question16 varchar(50),
@question16and1 varchar(200),
@question17 varchar(50),
@question17and1 varchar(200),
@question18 varchar(10),
@question19 varchar(10),
@question20 varchar(40),
@question20and1 varchar(200),
@question21 varchar(10),
@question22 varchar(40),
@question23 varchar(40),
@question24 varchar(10),
@question25 varchar(200),
@id int output
as
insert into tblQuestion(insertTime,updateTime,question1,question2,question3,question3and1,question4,question4and1,question5,question5and1,question6,question7,question8,question9,question10,question11,question12,question13,question13and1,question14,question14and1,question15,question15and1,question16,question16and1,question17,question17and1,question18,question19,question20,question20and1,question21,question22,question23,question24,question25)
values(getdate(),getdate(),@question1,@question2,@question3,@question3and1,@question4,@question4and1,@question5,@question5and1,@question6,@question7,@question8,@question9,@question10,@question11,@question12,@question13,@question13and1,@question14,@question14and1,@question15,@question15and1,@question16,@question16and1,@question17,@question17and1,@question18,@question19,@question20,@question20and1,@question21,@question22,@question23,@question24,@question25)
set @id=@@identity
return 1然后我发下我的CLICK事件的后台代码:
//如何取值我就不发了,都是FOREACH遍历一些的结果SqlConnection cn = new SqlConnection(@"server=.\sqlexpress;uid=sa;pwd=;database=test");
SqlCommand cmd = new SqlCommand("InsertTbl", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@question1", SqlDbType.VarChar).Value = Question1;
cmd.Parameters.Add("@question2", SqlDbType.VarChar).Value = Question2;
cmd.Parameters.Add("@question3", SqlDbType.VarChar).Value = Question3;
cmd.Parameters.Add("@question3and1", SqlDbType.VarChar).Value = Question3and1;
cmd.Parameters.Add("@question4", SqlDbType.VarChar).Value = Question4;
cmd.Parameters.Add("@question4and1", SqlDbType.VarChar).Value = Question4and1;
cmd.Parameters.Add("@question5", SqlDbType.VarChar).Value = Question5;
cmd.Parameters.Add("@question5and1", SqlDbType.VarChar).Value = Question5and1;
cmd.Parameters.Add("@question6", SqlDbType.VarChar).Value = Question6;
cmd.Parameters.Add("@question7", SqlDbType.VarChar).Value = Question7;
cmd.Parameters.Add("@question8", SqlDbType.VarChar).Value = Question8;
cmd.Parameters.Add("@question9", SqlDbType.VarChar).Value = Question9;
cmd.Parameters.Add("@question10", SqlDbType.VarChar).Value = Question10;
cmd.Parameters.Add("@question11", SqlDbType.VarChar).Value = Question11;
cmd.Parameters.Add("@question12", SqlDbType.VarChar).Value = Question12;
cmd.Parameters.Add("@question13", SqlDbType.VarChar).Value = Question13;
cmd.Parameters.Add("@question13and1", SqlDbType.VarChar).Value = Question13and1;
cmd.Parameters.Add("@question14", SqlDbType.VarChar).Value = Question14;
cmd.Parameters.Add("@question14and1", SqlDbType.VarChar).Value = Question14and1;
cmd.Parameters.Add("@question15", SqlDbType.VarChar).Value = Question15;
cmd.Parameters.Add("@question15and1", SqlDbType.VarChar).Value = Question15and1;
cmd.Parameters.Add("@question16", SqlDbType.VarChar).Value = Question16;
cmd.Parameters.Add("@question16and1", SqlDbType.VarChar).Value = Question16and1;
cmd.Parameters.Add("@question17", SqlDbType.VarChar).Value = Question17;
cmd.Parameters.Add("@question17and1", SqlDbType.VarChar).Value = Question17and1;
cmd.Parameters.Add("@question18", SqlDbType.VarChar).Value = Question18;
cmd.Parameters.Add("@question19", SqlDbType.VarChar).Value = Question19;
cmd.Parameters.Add("@question20", SqlDbType.VarChar).Value = Question20;
cmd.Parameters.Add("@question20and1", SqlDbType.VarChar).Value = Question20and1;
cmd.Parameters.Add("@question21", SqlDbType.VarChar).Value = Question21;
cmd.Parameters.Add("@question22", SqlDbType.VarChar).Value = Question22;
cmd.Parameters.Add("@question23", SqlDbType.VarChar).Value = Question23;
cmd.Parameters.Add("@question24", SqlDbType.VarChar).Value = Question24;
cmd.Parameters.Add("@question25", SqlDbType.VarChar).Value = Question25;
cmd.Parameters["@id"].Direction = ParameterDirection.Output;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();然后是我运行后的结果:
此 SqlParameterCollection 中未包含带有 ParameterName“@id”的 SqlParameter。
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.IndexOutOfRangeException: 此 SqlParameterCollection 中未包含带有 ParameterName“@id”的 SqlParameter。源错误:
行 680: cmd.Parameters.Add("@question24", SqlDbType.VarChar).Value = Question24;
行 681: cmd.Parameters.Add("@question25", SqlDbType.VarChar).Value = Question25;
行 682: cmd.Parameters["@id"].Direction = ParameterDirection.Output;行 683: cn.Open();
行 684: cmd.ExecuteNonQuery();
源文件: h:\QuestionnaireSurvey11\Default.aspx.cs 行: 682 我看了半天我的代码应该没有问题啊,可就是找不出来,麻烦大家了,我先去睡了
SqlParameter prammesid = new SqlParameter("@mesid", SqlDbType.Int, 4);
prammesid.Direction = ParameterDirection.Output;
mycommand.Parameters.Add(prammesid);
cmd.Parameters["@id"].Direction = ParameterDirection.Output;注释掉试试int rows = cmd.ExecuteNonQuery(); 返回值rows就是自动增长列的最大值
少了这一句吧?
不加参数集合,你写这句就 cmd.Parameters["@id"].Direction = ParameterDirection.Output是非法的,因为你没有加入参数集合中.
==
cmd.Parameters.Add("@id", SqlDbType.VarChar);
cmd.Parameters["@id"].Direction = ParameterDirection.Output; 从编程来说
cmd.Parameters.Add("@question1", SqlDbType.VarChar).Value = Question1;
可以修改为
cmd.Parameters.AddWithValue("@question1", Question1);还有命名规范..还有如果参数如此众多,可以考虑拼成一个参数在存储过程里分解
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="labid" runat="server" Text="输入学生编号:"></asp:Label>
<asp:TextBox ID="txtStudentid" runat="server"></asp:TextBox><br />
<br />
<br />
<br />
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="查询学生姓名" />
<br />
<br />
<br />
<asp:Label ID="labname" runat="server" Text="查询出的学生姓名:"></asp:Label><asp:TextBox ID="txtStudentName"
runat="server"></asp:TextBox></div>
</form>
</body>
</html>protected void Page_Load(object sender, EventArgs e)
{
/*
sql脚本-创建学生表
create table student(id int,name varchar(50))
insert into student select 1,'小张'
insert into student select 2,'小王'
存储过程 功能:查询学生名称
create proc proc_getName
@id int,
@name varchar(50) output
as
begin
if exists(select 1 from student where id=@id)
select @name=name from student where id=@id
else
set @name='--未找到学生--'
end
*/
}
//执行存储过程
public void RunProcedure(string storedProcName, IDataParameter[] parameters)
{
string connectionString = "server=.;database=my;uid=sa;pwd=bb";//数据库连接串
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;//类型为存储过程
if (parameters != null)//检查过程参数
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
sqlDA.SelectCommand = command;
command.ExecuteNonQuery();//执行过程
connection.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
//声明参数
SqlParameter[] parameters = {
new SqlParameter("@id", SqlDbType.Int),
new SqlParameter("@name", SqlDbType.VarChar,50)
};
parameters[0].Value = txtStudentid.Text.Trim(); ;//学生编号 id
parameters[1].Direction = ParameterDirection.Output;//设置参数类型
RunProcedure("proc_getName", parameters);//执行过程方法 proc_getName为存储过程名称
txtStudentName.Text = parameters[1].Value.ToString();//取得返回值
}