Saving survey answers to a database

Question

I'm working on a project that requires me to create an evaluation portal (survey) where users login and answer 20 questions and its all rating questions (Strongly Disagree, Disagree, Agree and Strongly Agree).

I'm using ASP.net and C# and radio button list in every page.

I was able to create the login page using sessions.

I wanted to display every question in a separate page but when I do that I start having trouble with saving survey responses to the database. the result for each question get display in a separate row in the database. to be more specific, every user will have 20 rows of data in the database.

My question how to get each user responses in the same line or row in the database??

Any help will be greatly appreciated.

Thanks

Here is my login page:

        SqlConnection con = new SqlConnection("Data Source=.;Initial    Catalog=Student;Integrated Security=True");
        con.Open();
        SqlCommand cmd = new SqlCommand("select s.First_Name, s.Last_Name,c.Class_Title, c.Course_ID, c.Instructor_Last, c.Instructor_First, c.Term, c.Section_ID, c.Course_Number,e.Instructor_ID from Student S Join Student_Course e ON (s.Student_ID = e.Student_ID) Join Course c ON(c.Course_ID = e.Course_ID) where UserName =@username and Password=@password",con);

        cmd.Parameters.AddWithValue("@username", txtUserName.Text);
        cmd.Parameters.AddWithValue("@password", txtPassword.Text);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            Session["USER_ID"] = dt;
            Response.Redirect("Successful_Login.aspx");
        } 

---

Here is my successful login page This page to get user information base on their login, I'm using a gridview to show students courses base on their login and from there they can click on the course they want to rate and start the survey.

      protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        dt = (DataTable)Session["USER_ID"];
        lblName.Text = dt.Rows[0][0].ToString() + " " + dt.Rows[0][1].ToString();//your cloumn name;

        DataTable dt2 = (DataTable)Session["USER_ID"];
        GridView1.DataSource = dt2;
        GridView1.DataBind();
    }


    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {
        string instructorName = GridView1.SelectedRow.Cells[4].Text + ", " + GridView1.SelectedRow.Cells[5].Text;
        string courseSession= GridView1.SelectedRow.Cells[7].Text + "-" + GridView1.SelectedRow.Cells[8].Text;
        string term = GridView1.SelectedRow.Cells[6].Text;


        Session["USER_ID"] = instructorName;
        Session["USER_ID2"] = courseSession;
        Session["USER_ID3"] = term;


        Response.Redirect("Q1.aspx");
    }

---

Here is the first question (Q1) When the user click next button to go the next question, I would like for all response to go to the database and they go to the next page(Q2), I would like to capture all the results in the same row.

         protected void btnNext_Click(object sender, EventArgs e)
         {
        SqlConnection con = new SqlConnection(@"Data Source=.;Initial                  Catalog=Student;Integrated Security=True");
        SqlCommand cmd = new SqlCommand("insert into Survey (Q1,Q1_Comments) values (@Q1,@Q1_Comments)", con);
        cmd.Parameters.AddWithValue("Q1", radListQ1.SelectedValue);
        cmd.Parameters.AddWithValue("Q1_Comments", txtQ1Comments.Text);

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        }

Answer 1

Just to provide the complete code. this is Q1

      protected void btnNext_Click(object sender, EventArgs e)
      {
        if (Session["USER_ID"] != null )
        {
            SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=Student;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("insert into Survey (Q1,Q1_Comments) values (@Q1,@Q1_Comments)", con);
            cmd.Parameters.AddWithValue("Q1", radListQ1.SelectedValue);
            cmd.Parameters.AddWithValue("Q1_Comments", txtQ1Comments.Text);

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
     }

And this is Q2

       protected void btnQ2Next_Click(object sender, EventArgs e)
       {
          if (Session["USER_ID"] != null)
          {
        SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=Student;Integrated Security=True");
        SqlCommand cmd = new SqlCommand("UPDATE Survey SET (Q2 = @Q2, Q2_Comments = @Q2_Comments)", con);
        cmd.Parameters.AddWithValue("Q2", radListQ2.SelectedValue);
        cmd.Parameters.AddWithValue("Q2_Comments", txtQ2Comments.Text);

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

        Response.Redirect("Q3.aspx");
          }
       }

Answer 2

I strongly suggest you store in 20 rows... But if you insist using just one row.. I would suggest UPDATE command instead of INSERT...

Answer 3

For starter, if you want to display all 20 responses from the same user in one row, then you should construct a table that has more columns in database. e.g.

Survey
-----------------------------------------------------
studentID | Q1 | Q1_comments | Q2 | Q2_comments | ...

For the first question Q1, you can still use your original insert statement above:

SqlCommand cmd = new SqlCommand("insert into Survey (Q1,Q1_Comments) values (@Q1,@Q1_Comments)", con);
cmd.Parameters.AddWithValue("Q1", radListQ1.SelectedValue);
cmd.Parameters.AddWithValue("Q1_Comments", txtQ1Comments.Text);

For the second question and onwards, you need to perform update instead of inserting to database. e.g.

SqlCommand cmd = new SqlCommand("UPDATE Survey SET (Q2=@Q2,Q2_comment=@Q2_Comments)", con);
cmd.Parameters.AddWithValue("Q2", radListQ2.SelectedValue);
cmd.Parameters.AddWithValue("Q2_Comments", txtQ2Comments.Text);

As a little bit of advise, you might want to use transaction to do insert,update or delete, and surround them using try ... catch ... detect/handle potential error. More reading from MSDN

Hope this helps.