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?
 
samic400Connect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
All Courses

From novice to tech pro — start learning today.