multiple inserts

Results 1 to 2 of 2

Thread: multiple inserts

  1. #1
    Join Date
    Dec 1969

    Default multiple inserts

    I am planning a website for quiz. I have 3 pages and each page will ask users about 10 questions each. so a total of 30 questions and answers. After collecting all the 30 answers I want to store the answers in my database using stored procedures. How should I do them?<BR><BR>Method 1) Should I store all the 30 answers in one field seperated by a comma or tilt etc.<BR><BR>i.e.<BR><BR>tableAnswers<BR>ID¬*¬*User ID¬*¬*Answers<BR>1¬*¬*¬*¬*1¬*¬*¬*¬*¬*¬*yes~no~yes~ no~10~10~10~yes<BR>2¬*¬*¬*¬*5¬*¬*¬*¬*¬*¬*no~no~yes ~yes~20~20~10~no<BR><BR>Method 2) or should I store my answers as 30 rows<BR><BR>i.e.<BR><BR>tableAnswers<BR>ID¬*¬*User ID¬*¬*QuestionID¬*¬*Answers<BR>1¬*¬*¬*¬*1¬*¬*¬*¬*¬ *¬*¬*¬*¬*¬*1¬*¬*¬*¬*¬*¬*¬*¬*Yes<BR>2¬*¬*¬*¬*1¬*¬*¬ *¬*¬*¬*¬*¬*¬*¬*2¬*¬*¬*¬*¬*¬*¬*¬*No<BR>3¬*¬*¬*¬*1¬* ¬*¬*¬*¬*¬*¬*¬*¬*¬*3¬*¬*¬*¬*¬*¬*¬*¬*Yes<BR>4¬*¬*¬*¬ *1¬*¬*¬*¬*¬*¬*¬*¬*¬*¬*4¬*¬*¬*¬*¬*¬*¬*¬*No<BR>5¬*¬* ¬*¬*1¬*¬*¬*¬*¬*¬*¬*¬*¬*¬*5¬*¬*¬*¬*¬*¬*¬*¬*Yes<BR>< BR>and so on. Which method should i use. If I use method 2 how will my stored procedures be. Will it contain 30 insert statements? I haven&#039;t got a clue how I am going to do this. could anyone share your experience please.

  2. #2
    Join Date
    Dec 1969

    Default Use One Row For Each Response Group

    I would probably have one table with 30 fields...If I had say 10 pages of 5 questions, I would separate so my table would not have too many fields...If you had multiple tables, you would have an insert for each table...Same principle as one...<BR><BR>I would then take my responses and build my insert...<BR><BR>I&#039;m not taking into account radio/checkbox or text, so you will have to build your parameters accordingly...I&#039;m gonna presume all form elements are text...<BR><BR>lsInsertSql = ""<BR>lsInsertSql = "StoredProcedureName " -&#062; Be Sure to include space<BR>lsInsertSql = lsInsertSql & "&#039;" & Trim(Request.Form("q1") & "&#039;"<BR>lsInsertSql = lsInsertSql & ",&#039;" & Trim(Request.Form("q2") & "&#039;"<BR><BR>Continue until you have parameter for each question...<BR><BR>Set up your asp connections, etc...<BR><BR>For stored procedure use be sure to use...<BR><BR>Dim adCmdStoredProc<BR><BR>adCmdStoredProc = 4<BR><BR>cnObject.Execute(lsInsertSql)<BR><BR>Here &#039;s what your stored procedure will look like...Again, I presume all character parms...If you have different datatypes, make sure you take care of when setting up your input parameters...(i.e. Int will not have quotes around)...<BR><BR><BR>Create Procedure StoredProcedureName<BR><BR>@InputParm1 Char(Size),<BR>@InputParm2 char(Size)<BR><BR>As<BR><BR>Insert Into TableName <BR> (Field1, Field2, Field3, etc)<BR> Values (@InputParm1, @InputPart2, etc)<BR><BR>Return<BR><BR>When your user submits the data, your asp will just call the stored procedure once...(if using one table)...If two tables were used, then one insert for each table...<BR><BR>So, if you decided to use one table, each row will represent a set of pages that were answered...<BR><BR>There&#039;s other ways to do also...<BR><BR>Hope this helps...<BR><BR>Howie<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts