将数据类型nvarchar转换为数字时出错。 [英] getting error as converting data type nvarchar to numeric.
问题描述
我有colums的表答案作为answer_id主键，student_id外键，question_id外键，student_answer varchar（400）
和问题表包含colums作为question_id主键，teacher_id外键，tea_que varchar（400）
和表计算包含colum student_id，标记数字
i我正在尝试在正确执行的答案表中插入student_id，question_id，student_answer。
在我比较老师和学生的答案并计算分数并存储在标签或变量中。
同时我试图在计算表中插入/取值student_id和计算标记
代码是：
公共部分类WebForm9：System.Web.UI.Page
{
SqlCommand cmd = new SqlCommand（）;
DataTable dt = new DataTable（）;
DataRow drr;
public string ans_tea;
public string ans_stu;
SqlConnection con = new SqlConnection（@Data Source = .\sqlexpress; Initial Catalog = swatidb; Integrated Security = True; Pooling = False）;
protected void insert_Click（object sender，EventArgs e）
{
try
{
String que_id = Label3.Text;
// String que = Label2.Text;
String stu_id = Label4.Text;
String ans = TextBox1.Text;
cmd = new SqlCommand（插入答案值（@ que_id，@ stu_id，@ ans），con）;
cmd.Parameters.AddWithValue（@ que_id，que_id）;
cmd.Parameters.AddWithValue（@ stu_id，stu_id）;
cmd.Parameters.AddWithValue（@ ans，ans）;
int i = cmd.ExecuteNonQuery（）;
using（SqlConnection con1 = new SqlConnection（@Data Source = .\sqlexpress; Initial Catalog = swatidb; Integrated Security = True; Pooling = False））
{
int qu_i = Convert.ToInt32（Label3.Text）;
con1.Open（）;
using（SqlCommand cmd1 = new SqlCommand（SELECT teacher_answer FROM Question WHERE question_id = @ qu_i，con））
{
cmd1.Parameters.AddWithValue（@ qu_i，qu_i）;
using（SqlDataReader reader = cmd.ExecuteReader（））
{
while（reader.Read（））
{
ans_tea =（string）reader [teacher_answer ];
}
}
//Label4.Text = ans_tea;
}
int qu_id = Convert.ToInt32（Label3.Text）;
使用（SqlCommand cmd1 = new SqlCommand（从answer中选择student_answer，其中question_id = @ qu_id，con））
{
cmd1.Parameters.AddWithValue（@ qu_id，qu_id）;
using（SqlDataReader reader = cmd.ExecuteReader（））
{
while（reader.Read（））
{
ans_stu =（string）reader [student_answer ];
}
}
}
}
MatchsMaker match = new MatchsMaker（ans_tea，ans_stu）;
// b = match.Score;
// Response.Write（b）;
Response.Write（match.Score）;
Label6.Text = Convert.ToString（match.Score）;
string mrk = Label6.Text;
cmd = new SqlCommand（插入到cal_marks_of_each值（@ stu_id，@ mrk），con）;
cmd.Parameters.AddWithValue（@ stu_id，stu_id）;
cmd.Parameters.AddWithValue（@ mrk，mrk）;
int j = cmd.ExecuteNonQuery（）;
TextBox1.Text =;
}
catch（例外e1）
{
Label5.Text =未插入值，因为您已插入alredy问题+ e1;
}
}
}
}
查看代码：
cmd = new SqlCommand（ < span class =code-string>插入答案值（@ que_id，@ stu_id，@ ans），con）;
现在，使用参数化查询做得很好，但是......看看你的表定义：
我的表格以colums作为answer_id主键，student_id外键，question_id外键，student_answer varchar（400）所以你的表是：
answer_id INT，主键，可能是IDENTITY
student_id INT，外键
question_id INT，外键
student_answer varchar（400）但是......你不要告诉SQL将哪些列INSERT值转换成。所以它从第一列开始，然后向上移动。由于你只设置了三个值，它决定前三个参数是INT值，并尝试将它们转换为 - 但第三个不是这样，它失败了。
总是列出一个好的做法要插入的列：
cmd = new SqlCommand（ INSERT INTO Answer（question_id，student_id，student_answer）VALUES（@ que_id，@ stu_id，@ ans），con）;
这将解决您的问题。
与参数匹配的列：doh： - OriginalGriff [/ edit]
i have table answer with colums as answer_id primary key,student_id foreign key,question_id foreign key,student_answer varchar(400)
and question table contains colums as question_id primary key,teacher_id foreign key,tea_que varchar(400)
and table calculation contains colum student_id,marks numeric
i am trying insert student_id,question_id,student_answer in answer table which is executing properly.
after i am comparing teacher's and students's answer and calculating score and storing in label or variable.
at same time i am trying to insert/take values student_id and calculated marks in calculation table
code is:
public partial class WebForm9 : System.Web.UI.Page
{
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
DataRow drr;
public string ans_tea;
public string ans_stu;
SqlConnection con = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=swatidb;Integrated Security=True;Pooling=False");
protected void insert_Click(object sender, EventArgs e)
{
try
{
String que_id = Label3.Text;
// String que = Label2.Text;
String stu_id = Label4.Text;
String ans = TextBox1.Text;
cmd = new SqlCommand("insert into Answer values(@que_id,@stu_id,@ans)", con);
cmd.Parameters.AddWithValue("@que_id", que_id);
cmd.Parameters.AddWithValue("@stu_id", stu_id);
cmd.Parameters.AddWithValue("@ans", ans);
int i = cmd.ExecuteNonQuery();
using (SqlConnection con1 = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=swatidb;Integrated Security=True;Pooling=False"))
{
int qu_i = Convert.ToInt32(Label3.Text);
con1.Open();
using (SqlCommand cmd1 = new SqlCommand("SELECT teacher_answer FROM Question WHERE question_id=@qu_i", con))
{
cmd1.Parameters.AddWithValue("@qu_i", qu_i);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ans_tea = (string)reader["teacher_answer"];
}
}
//Label4.Text = ans_tea;
}
int qu_id = Convert.ToInt32(Label3.Text);
using (SqlCommand cmd1 = new SqlCommand("select student_answer from Answer where question_id=@qu_id", con))
{
cmd1.Parameters.AddWithValue("@qu_id", qu_id);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ans_stu = (string)reader["student_answer"];
}
}
}
}
MatchsMaker match = new MatchsMaker(ans_tea, ans_stu);
//b = match.Score;
// Response.Write(b);
Response.Write(match.Score);
Label6.Text = Convert.ToString(match.Score);
string mrk = Label6.Text;
cmd = new SqlCommand("insert into cal_marks_of_each values(@stu_id,@mrk)", con);
cmd.Parameters.AddWithValue("@stu_id", stu_id);
cmd.Parameters.AddWithValue("@mrk", mrk);
int j = cmd.ExecuteNonQuery();
TextBox1.Text = "";
}
catch (Exception e1)
{
Label5.Text = "values are not inserted because you have alredy inserted question"+e1;
}
}
}
}Look at your code:
cmd = new SqlCommand("insert into Answer values(@que_id,@stu_id,@ans)", con);Now, well done for using parameterized queries, but...look at your table definition:
"i have table answer with colums as answer_id primary key,student_id foreign key,question_id foreign key,student_answer varchar(400)"So your table is:
answer_id INT, primary key, probably IDENTITY student_id INT, foreign key question_id INT, foreign key student_answer varchar(400)But...you don;t tell SQL which columns to INSERT values into. So it starts with the first column, and move up through the table. Since you only set three values, It decides the first three parameters are INT values, and tries to convert them as such - but the third isn't so it fails.
It is good practice to always list the columns you want to INSERT:
cmd = new SqlCommand("INSERT INTO Answer (question_id, student_id, student_answer) VALUES (@que_id,@stu_id,@ans)", con);Which will solve your problem.
[edit]Matched up columns with parameters :doh: - OriginalGriff[/edit]
这篇关于将数据类型nvarchar转换为数字时出错。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
