locked
help with INSERT statement RRS feed

  • Question

  • User-2060576634 posted

    hi everyone.. this piece of code generates a  number of multi-choice questions from a table and displays them to the user:

    <form method="post">
    @
    foreach (var row in db.Query(selectitemid1)) { <h6>@row.Order . @row.Text</h6> <input type="radio" name="@("Q" + row.QuestionId)" value="1" >@row["Choice1"] <br> <input type="radio" name="@("Q" + row.QuestionId)" value="2" >@row["Choice2"] <br> <input type="radio" name="@("Q" + row.QuestionId)" value="3" >@row["Choice3"] <br> <input type="radio" name="@("Q" + row.QuestionId)" value="4">@row["Choice4"] <br> <div > <input type="radio" name="@("Q" + row.QuestionId)" value="0" checked >Don't Know </div> }
    <input type="submit" value="Submit"/>
    </form>

    when the user clicks the submit button every user answer to every question will create  a new row in the Answers table which has the following structure:
    [AnswerId][UserId][QuestionId] 

    the AnswerId is auto incremented , the UserId comes from WebSecurity.CurrentUserId and the questionId will be feched from each form name.

    What is the correct INSERT statement to perform this task?

    Wednesday, January 21, 2015 6:42 PM

Answers

  • User1853794821 posted

    I have not tested this, but here is a pattern

    	var sql = "INSERT into Answers (userId, questionId, answerId) VALUES(@0, @1, @2))";
    	var userid = WebSecurity.CurrentUserId;
    	foreach(var q in questions)
    	{
    		var id = q.id;
    		var answer = Request["Q" + q.id.ToString()];
    		db.Insert(sql, @userid, @qid, @answer);
    			
    		
    	}
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 22, 2015 9:46 AM
  • User1853794821 posted

    Without knowing the error you got, I can only suspect it is because of the extra parenthesis in the INSERT query

    "INSERT into Answers (UserId,QuestionId,Answer) VALUES(@0, @1, @2))"
    
    should be
    "INSERT into Answers (UserId,QuestionId,Answer) VALUES(@0, @1, @2)"

    Also, not seeing your table design for the answer table, I do not know what the name and type of the column for the answer is.  If it is INT, then your Request[""] should have the "AsInt()" helper appended to it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 22, 2015 12:01 PM

All replies

  • User379720387 posted

    http://www.w3schools.com/sql/sql_insert.asp

    I would recommend that you look at some of the tutorials. I personally like the soccer website.

    https://msdn.microsoft.com/en-us/library/hh145667%28v=vs.99%29.aspx

    Wednesday, January 21, 2015 8:44 PM
  • User-2060576634 posted
    Its not that simple sir... It involves iteration.. And i need to insert several rows in one go.. I dont think it has been covered in those resources.
    Thursday, January 22, 2015 6:37 AM
  • User1853794821 posted

    In your IsPost, iterate through the questions.  For each question:

    --construct the radio button name and

    --get the value for the radio button and

    --use the answer, question and userid in an insert to your answer table

    Thursday, January 22, 2015 8:15 AM
  • User-2060576634 posted

    dear rrrsr7205 .. I already know the idea.. I just don't know how to code it.. it would be appreciated if you offer the code or at least the pattern or a resource that precisely discusses a similar issue..

    Thursday, January 22, 2015 8:20 AM
  • User1853794821 posted

    I have not tested this, but here is a pattern

    	var sql = "INSERT into Answers (userId, questionId, answerId) VALUES(@0, @1, @2))";
    	var userid = WebSecurity.CurrentUserId;
    	foreach(var q in questions)
    	{
    		var id = q.id;
    		var answer = Request["Q" + q.id.ToString()];
    		db.Insert(sql, @userid, @qid, @answer);
    			
    		
    	}
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 22, 2015 9:46 AM
  • User-2060576634 posted

    ok I realized that I have forgotten to add an Answer column to my Answers table.. I added it and arranged this code with a few naming changes.. but i still get an error on the last line or markup upon submit..

    @{
        
        var db = Database.Open("Azmoon");
        var selectquestions = "SELECT * From Questions WHERE ExamId=1";
        var userid = WebSecurity.CurrentUserId;
    
                    
           if(IsPost)
           {
               foreach(var row in db.Query(selectquestions))
    	{
    		
                var questionid = row.QuestionId;
    	    var answer = Request["Q" + row.QuestionId.ToString()];
                var insertanswers = "INSERT into Answers (UserId,QuestionId,Answer) VALUES(@0, @1, @2))";
    		db.Execute(insertanswers, @userid, @questionid, @answer);
    	}		
    }
    <form method="post"> @foreach (var row in db.Query(selectquestions)) { <h6>@row.Order . @row.Text</h6> <input type="radio" name="@("Q" + row.QuestionId)" value="1" >@row["Choice1"] <br> <input type="radio" name="@("Q" + row.QuestionId)" value="2" >@row["Choice2"] <br> <input type="radio" name="@("Q" + row.QuestionId)" value="3" >@row["Choice3"] <br> <input type="radio" name="@("Q" + row.QuestionId)" value="4" >@row["Choice4"] <br> <div > <input type="radio" name="@("Q" + row.QuestionId)" value="0" checked >Don't Know </div> } <input type="submit" value="Submit"/> </form>


    Thursday, January 22, 2015 11:52 AM
  • User1853794821 posted

    Without knowing the error you got, I can only suspect it is because of the extra parenthesis in the INSERT query

    "INSERT into Answers (UserId,QuestionId,Answer) VALUES(@0, @1, @2))"
    
    should be
    "INSERT into Answers (UserId,QuestionId,Answer) VALUES(@0, @1, @2)"

    Also, not seeing your table design for the answer table, I do not know what the name and type of the column for the answer is.  If it is INT, then your Request[""] should have the "AsInt()" helper appended to it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 22, 2015 12:01 PM
  • User-2060576634 posted

    yes.. thanks a lot it worked..

    Thursday, January 22, 2015 1:09 PM