Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2003
    Posts
    6

    Unanswered: Inserting Multiple Rows Into a database

    Hi,

    I am trying to insert 9 rows into an table at the same time. My situation is this...

    I have a survey page. There are 9 parts with each part ment for an individual person. Each part has 8 questions, and each part has the same 8 questions

    The questions are answered using one of the answers in a drop down box.

    So when the surveyer clicks submit, all the 9 parts should be entered into the table.

    If this is confusing, I have the form up on the Internet at...

    http://www.lavenderlane.ie/wage_survey_test.asp

    I can insert one part no problem, (when I reduce the form to only 1 part) but i need to insert all of the 9 parts simultaneously. I reckon its some sort of for loop but if u could help me out i would appreciate it!

    Thanks a lot

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Not sure if your table design is correct

    what you want to do is set up a table like this

    Create Table FormResponse

    (
    Customer_id int identity (1,1) not null,
    ResponseOne varchar (50) not null default 'not known',
    ResponseTwo varchar (50) not null default 'not known',
    ResponseThree varchar (50) not null default 'not known',
    ResponseFour varchar (50) not null default 'not known',
    ResponseFive varchar (50) not null default 'not known',
    ResponseSix varchar (50) not null default 'not known',
    ResponseSeven varchar (50) not null default 'not known',
    ResponseEight varchar (50) not null default 'not known',
    ResponseNine varchar (50) not null default 'not known'
    )

    This should be the basis for your table. The identity column will create an autonumber for every entry. You should change this if you are setting this sort of ID somewhere else in the schema of the database.

    What you need to do is on your form code declare 9 variables and set eac variable to the form objects text boxes.

    Something like

    declare @var1 varchar (50)
    declare @var2 varchar (50)
    etc

    set @var1 = form.object1.txt
    set @var2 = form.object2.txt
    etc

    then to perform the insert all you have to do is :
    insert into FormResponse (ResponseOne, responseTwo etc, etc)
    values (@var1, @var2 etc)

    no loop required

    hth

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey aldo, and if the number of questions increases to 40, - what would be "the correct" table structure? Thought of that?



    bsheridan1,
    You can build a SELECT statement on the front-end using UNION and then use INSERT <table_name> SELECT ... UNION ... SELECT ... etc.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jan 2003
    Posts
    6
    Is there no way to have an INSERT wrapped with in a for loop or something like that, that will take all the values for each question and put these values into their own seperate row in the db?

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Certainly you can put your INSERT...VALUES into a FOR loop in your front-end code, but your original intent was to insert them all at once. That's why I gave you a UNION option.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Mar 2004
    Posts
    80
    are u trying to call a stored proc or building an insert statement at front end?

  7. #7
    Join Date
    Jan 2003
    Posts
    6
    No just building an insert statement at the front end. I have the following


    pos=Request.form("pos")
    contract=Request.form("contract")
    service=Request.form("service")
    ...

    sSQL="INSERT INTO wage (pos,contract,service,basic, bonus, total,car, pension, category) " & _
    "VALUES ('" & pos & "', '" & contract & "', '" & service & "',)"



    This works fine if I only have one question on the survey page, but when I put the same question 9 times using a while loop, i get an error saying...

    String or binary data would be truncated

    Thanks 4 the help

  8. #8
    Join Date
    Mar 2004
    Posts
    80
    I guess the problem is with width of the fields in your tables
    not with loop plz check it first

  9. #9
    Join Date
    Jan 2003
    Posts
    6
    But why would it work for one question and not when there's two?

    The same data was submitted for all parts of the questions.

  10. #10
    Join Date
    Mar 2004
    Posts
    80
    "The same data was submitted for all parts of the questions".

    you mean the same answer for all the nine question

  11. #11
    Join Date
    Jan 2003
    Posts
    6
    Yes, it inserts the answers fine with just one question in the form, but if i put more than one question I get that error.

    I reckon its because there are answers for the 9 questions but only 1 insert statement corresponding to one row.

    I did make it work by repeating the code for each of the questions, instead of putting it in a loop, and then for the SQL i made a seperate insert statement for each question. But this is very bad programming practice as the amount of code increases rapidly.

    am i losing you in all of this?

  12. #12
    Join Date
    Mar 2004
    Posts
    80
    I try to forget about programming practices and think about resources(SERVER) in situations like this.
    Building an insert string using loop and executing it out side the loop is what i prefer to do.

  13. #13
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    "if the number of questions increases to 40"

    Then the design was flawed to begin with and more effort should have been put into the design phase.

    In this instance some error handling should be introduced to check each variable against the length of the database field that they propose to insert into. This should be done at the form level by disallowing invalid entires into the form. i.e if the destination column is a char (10) then the form should disallow any sting larger than that.

    hth

Posting Permissions

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