ASP Form - Single form insert data with same field name in multiple rows

I would like to know if someone can help getting started with this issue. We used to collect survey responses from a form and store the collected data for each "survey respondent session" in a single record (SQL Server row). I was asked to streamline our system by collecting the data in three tables by datatype (int, small text, long text), each response being linked to its survey by association through foreign keys. I was going to design my form to submit each response to questionnaires one at a time but then I was asked to replicate the forms as we used to have them: one submit button sending all responses (survey data) to questionnaire at once. My first issue with that (and probably not the last) is that I have no idea how to submit at once many data to create multiple records.

When, or if, I get this right, my next issue will be that I will need to find a way to automatically re-distribute the data vertically by "respondent session" for analysis/export in spreadsheets.

Don't quite know where to start here.

Any help will be much appreciated.

Thanks
benoitmarcotteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

samic400Commented:
Are you asking how can I do an insert, from my form data, into multiple tables with that data? And if so, what would the code look like?
0
benoitmarcotteAuthor Commented:
I haven't started coding but yes. In multiple tables and also each field in one form would go in the same field in the db but in different rows. Let's say I have in one form 5 fields with integer values (5, 1, 4, 6, 3), I don't want these values to be stored horizontally, each in the same db field, I want them to be stored in the same column/db field each in a different record in one table as in:

5
1
4
6
3

And in the same form I have three booleans being 1/0, let's say (1, 1, 0) are stored in another table with each value in its own row as in:

1
1
0

But with all inserted with a single "submit" from a single form.

This is how I want the data storage to work. Different fields in a web form would be stored in the same field in the db.

Hope that makes sense.

B.
0
benoitmarcotteAuthor Commented:
Also I should mention that data should be stored by datatype/table. There will be 4 tables storing each a different datatype (int, boolean, short text, long text)
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

samic400Commented:
Is possible for you to name each field in your form so that when you cycle through your collection of fields you know which type each one is? Will make it easier when updating your recordsets.

For each text box starts with a t, integer starts with an i and so on? My thought was you can cycle through the field collection in code and based on the first character of each field, know what database you need to update.

Answer this and I'll throw some code together for you, ok?
0
benoitmarcotteAuthor Commented:
Sorry it took a while:

Yes it is possible.

B.
0
samic400Commented:
first thing is, when you set up your form, i would define each input type name to start with either "i" for integer, "b" for boolean, "s" for short text, and "l" for long text.

So your integer type field would be set up something like this:
<input type='text' name='itextfield1'>

So your boolean type field, letsd say it was a radio button:
<input type='radio' name='bradiofield1'>

<%
'make your connection to your DB
set mConn = server.Createobject("ADODB.Connection")
mConn.Open "<your connection string>"

'next we want to read through all of the form elements
for each fldName in request.form

'fldName represents the fields from your form
mytype = mid(fldName,1,1) 'get value of first character for field
fieldvalue = request.form(fldName)

select case mytype

   case "i" 'integer type
       mConn.Execute "insert stmt into integer table" with fieldvalue
   case "b" ' boolean type
      mConn.Execute "insert stmt into boolean table" with fieldvalue
   case "s" 'short text
       mConn.Execute "insert stmt into short text table" with fieldvalue
   case "l" ' boolean type
      mConn.Execute "insert stmt into long text table" with fieldvalue

end select
next
0
samic400Commented:
couple of typos i fixed:

this is after you submit your form, you would porcess code below:

<%
'make your connection to your DB
set mConn = server.Createobject("ADODB.Connection")
mConn.Open "<your connection string>"

'next we want to read through all of the form elements
for each fldName in request.form

'fldName represents the fields from your form
mytype = mid(fldName,1,1) 'get value of first character for field
fieldvalue = request.form(fldName)

select case mytype

   case "i" 'integer type
       mConn.Execute "insert stmt into integer table" with fieldvalue
   case "b" ' boolean type
      mConn.Execute "insert stmt into boolean table" with fieldvalue
   case "s" 'short text
       mConn.Execute "insert stmt into short text table" with fieldvalue
   case "l" ' long text
      mConn.Execute "insert stmt into long text table" with fieldvalue

end select
next

mConn.Close
set mConn = Nothing
%>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.