benoitmarcotte
asked on
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
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
Bob Butcher
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?
ASKER
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.
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.
ASKER
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)
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?
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?
ASKER
Sorry it took a while:
Yes it is possible.
B.
Yes it is possible.
B.
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.