Click here to Skip to main content
15,908,264 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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;
           }
       }
}
}
Posted
Updated 21-Mar-14 5:27am
v2

1 solution

Look at your code:
C#
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:
C#
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]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900