Following on from my previous question posted here (Two insert queries with linked fields), why does the following code insert 4 new rows in the 'question_m' table when I want to insert only 1 row? Also, the following code inserts 16 new rows in the 'answers' table instead of 4 rows?
Here are screencaps of the tables after this code was executed:
'questions_m' table - https://i.stack.imgur.com/f3DrZ.png
'answers' table - https://i.stack.imgur.com/PaTvO.png
string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
using (MySqlConnection conn = new MySqlConnection(connStr))
{
conn.Open();
using (MySqlTransaction tr = conn.BeginTransaction())
{
string queryUpdateQuestions = @"INSERT INTO questions_m (module_id, author_id, approved, question, type) VALUES (@module_id, @author_id, @approved, @question, @type);
SELECT LAST_INSERT_ID()";
using (MySqlCommand cmdUpdateQuestions = new MySqlCommand(queryUpdateQuestions, conn, tr))
{
cmdUpdateQuestions.Parameters.Add("@module_id", MySqlDbType.VarChar);
cmdUpdateQuestions.Parameters["@module_id"].Value = ddlModules.SelectedValue.ToString();
cmdUpdateQuestions.Parameters.Add("@author_id", MySqlDbType.VarChar);
cmdUpdateQuestions.Parameters["@author_id"].Value = Session["UserID"].ToString();
cmdUpdateQuestions.Parameters.Add("@approved", MySqlDbType.VarChar);
cmdUpdateQuestions.Parameters["@approved"].Value = 'N';
cmdUpdateQuestions.Parameters.Add("@question", MySqlDbType.VarChar);
cmdUpdateQuestions.Parameters["@question"].Value = txtQuestion.Text;
cmdUpdateQuestions.Parameters.Add("@type", MySqlDbType.VarChar);
cmdUpdateQuestions.Parameters["@type"].Value = ddlQuestionType.SelectedValue.ToString();
int lastQuestionID = Convert.ToInt32(cmdUpdateQuestions.ExecuteScalar());
ViewState["lastQuestionID"] = lastQuestionID;
}
string queryUpdateAnswers = @"INSERT INTO answers (question_id, answer, correct) VALUES (@question_id, @answer, @correct);
SELECT LAST_INSERT_ID()";
using (MySqlCommand cmdUpdateAnswers = new MySqlCommand(queryUpdateAnswers, conn, tr))
{
cmdUpdateAnswers.Parameters.Add("@answer", MySqlDbType.VarChar);
cmdUpdateAnswers.Parameters.Add("@question_id", MySqlDbType.Int32);
cmdUpdateAnswers.Parameters.Add("@correct", MySqlDbType.VarChar);
int lastAnswerID = 0;
int a = Convert.ToInt32(ddlNoOfAnswers.SelectedValue.ToString());
for (int b = 1; b <= a; b++)
{
cmdUpdateAnswers.Parameters["@answer"].Value = ((TextBox)this.FindControl("txtAnswer" + b)).Text;
cmdUpdateAnswers.Parameters["@question_id"].Value = Convert.ToInt32(ViewState["lastQuestionID"]);
if (b == 1)
{
cmdUpdateAnswers.Parameters["@correct"].Value = "Y";
}
else
{
cmdUpdateAnswers.Parameters["@correct"].Value = null;
}
lastAnswerID = Convert.ToInt32(cmdUpdateAnswers.ExecuteScalar());
}
}
tr.Commit();
}
}
