Insert Muliple Rows from ASP.NET VB aspx form 1

[jrball]

I am stuck. I have some vars being passed to an aspx page though a post method, and I am reading them through the request.form("varname") for each. I need to dump into a db table in multiple rows, how do I do it? I am going into a SQL database using VS.Net 2003. I was hoping to do a bulk insert or loop, but I don't know where to start or how to do either.

here's the format that the vars come in the page as:

UserID = 46 
k120interaction_id = Interaction_01 
k120correct_response = d 
k120student_response = d 
k120result = C 
k120latency = 00:00:02 

k121interaction_id = Interaction_02 
k121correct_response = c 
k121student_response = c 
k121result = C 
k121latency = 00:00:02 

k122interaction_id = Interaction_03 
k122correct_response = a 
k122student_response = a 
k122result = C 
k122latency = 00:00:03

now here's the format of the database

AnswerID | UserID | InteractionID | CorrectResponse | StudentResponse | QResult | Latency

How do I insert the data to have it be mulitiple rows like:

1 | 46 | Interaction_01 | d | d | C | 00:00:02 
2 | 46 | Interaction_02 | c | c | C | 00:00:02 
3 | 46 | Interaction_03 | a | a | C | 00:00:03

 

[jbenson001]

Do you know how many variables you will have and what the names will be?

 

[vbkris]

>> I have some vars being passed to an aspx page though a post method

and what software is posting these values? another ASPX page or is it flash???

Known is handfull, Unknown is worldfull

 

[bboffin]

Add a SqlDataAdapter to your web form and configure it using the wizard. Create a simple SELECT statement that pulls in the columns you intend to update or insert.

I know that you aren't going to read the data but the wizard will use the field names to create the appropriate INSERT and UPDATE statements for you.

Right click on the SqlDataAdapter and generate the dataset.

This will create an instance of a typed dataset in your aspx page.

Each time you get a new set of values to be entered into the table use the NewRow method of the table in the dataset to create a new row. Populate the fields of this row from the data you have received and then call the add method of the Rows property of the table to add the row to the dataset.

Finally call the Update method of the dataset to apply the changes to the database.

It sounds a bit long winded but if you were to use a control such as a grid bound to the dataset on your web page the row would be populated and added by the control and all you would need to do is call the Update method of the bound dataset to apply the changes.



Bob Boffin

 

[jrball]

Thanks, bboffin that was what I was needing!

 

[ca8msm]

As bboffin has helpfully pointed out, that approach may be a bit long winded but will do the job quite nicely.

However, if you answer jbenson001's question, there may be a simple loop that will insert the data without the need for the overbloated Data Adaptor.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[jrball]

I can answer his question, but I didn't want to post a ton of vars to the page.. there are 175 vars for each userid

        userid = (Request.Form("userid"))

        k12SessionArray0interaction_id = (Request.Form("k12SessionArray0interaction_id"))
        k12SessionArray0correct_response = (Request.Form("k12SessionArray0correct_response"))
        k12SessionArray0student_response = (Request.Form("k12SessionArray0student_response"))
        k12SessionArray0result = (Request.Form("k12SessionArray0result"))
        k12SessionArray0latency = (Request.Form("k12SessionArray0latency"))
        k12SessionArray1interaction_id = (Request.Form("k12SessionArray1interaction_id"))
        k12SessionArray1correct_response = (Request.Form("k12SessionArray1correct_response"))
        k12SessionArray1student_response = (Request.Form("k12SessionArray1student_response"))
        k12SessionArray1result = (Request.Form("k12SessionArray1result"))
        k12SessionArray1latency = (Request.Form("k12SessionArray1latency"))
        k12SessionArray2interaction_id = (Request.Form("k12SessionArray2interaction_id"))
        k12SessionArray2correct_response = (Request.Form("k12SessionArray2correct_response"))
        k12SessionArray2student_response = (Request.Form("k12SessionArray2student_response"))
        k12SessionArray2result = (Request.Form("k12SessionArray2result"))
        k12SessionArray2latency = (Request.Form("k12SessionArray2latency"))
        k12SessionArray3interaction_id = (Request.Form("k12SessionArray3interaction_id"))
        k12SessionArray3correct_response = (Request.Form("k12SessionArray3correct_response"))
        k12SessionArray3student_response = (Request.Form("k12SessionArray3student_response"))
        k12SessionArray3result = (Request.Form("k12SessionArray3result"))
        k12SessionArray3latency = (Request.Form("k12SessionArray3latency"))
        k12SessionArray4interaction_id = (Request.Form("k12SessionArray4interaction_id"))
        k12SessionArray4correct_response = (Request.Form("k12SessionArray4correct_response"))
        k12SessionArray4student_response = (Request.Form("k12SessionArray4student_response"))
        k12SessionArray4result = (Request.Form("k12SessionArray4result"))
        k12SessionArray4latency = (Request.Form("k12SessionArray4latency"))
        k12SessionArray5interaction_id = (Request.Form("k12SessionArray5interaction_id"))
        k12SessionArray5correct_response = (Request.Form("k12SessionArray5correct_response"))
        k12SessionArray5student_response = (Request.Form("k12SessionArray5student_response"))
        k12SessionArray5result = (Request.Form("k12SessionArray5result"))
        k12SessionArray5latency = (Request.Form("k12SessionArray5latency"))
        k12SessionArray6interaction_id = (Request.Form("k12SessionArray6interaction_id"))
        k12SessionArray6correct_response = (Request.Form("k12SessionArray6correct_response"))
        k12SessionArray6student_response = (Request.Form("k12SessionArray6student_response"))
        k12SessionArray6result = (Request.Form("k12SessionArray6result"))
        k12SessionArray6latency = (Request.Form("k12SessionArray6latency"))
        k12SessionArray7interaction_id = (Request.Form("k12SessionArray7interaction_id"))
        k12SessionArray7correct_response = (Request.Form("k12SessionArray7correct_response"))
        k12SessionArray7student_response = (Request.Form("k12SessionArray7student_response"))
        k12SessionArray7result = (Request.Form("k12SessionArray7result"))
        k12SessionArray7latency = (Request.Form("k12SessionArray7latency"))
        k12SessionArray8interaction_id = (Request.Form("k12SessionArray8interaction_id"))
        k12SessionArray8correct_response = (Request.Form("k12SessionArray8correct_response"))
        k12SessionArray8student_response = (Request.Form("k12SessionArray8student_response"))
        k12SessionArray8result = (Request.Form("k12SessionArray8result"))
        k12SessionArray8latency = (Request.Form("k12SessionArray8latency"))
        k12SessionArray9interaction_id = (Request.Form("k12SessionArray9interaction_id"))
        k12SessionArray9correct_response = (Request.Form("k12SessionArray9correct_response"))
        k12SessionArray9student_response = (Request.Form("k12SessionArray9student_response"))
        k12SessionArray9result = (Request.Form("k12SessionArray9result"))
        k12SessionArray9latency = (Request.Form("k12SessionArray9latency"))
        k12SessionArray10interaction_id = (Request.Form("k12SessionArray10interaction_id"))
        k12SessionArray10correct_response = (Request.Form("k12SessionArray10correct_response"))
        k12SessionArray10student_response = (Request.Form("k12SessionArray10student_response"))
        k12SessionArray10result = (Request.Form("k12SessionArray10result"))
        k12SessionArray10latency = (Request.Form("k12SessionArray10latency"))
        k12SessionArray11interaction_id = (Request.Form("k12SessionArray11interaction_id"))
        k12SessionArray11correct_response = (Request.Form("k12SessionArray11correct_response"))
        k12SessionArray11student_response = (Request.Form("k12SessionArray11student_response"))
        k12SessionArray11result = (Request.Form("k12SessionArray11result"))
        k12SessionArray11latency = (Request.Form("k12SessionArray11latency"))

        s10SessionArray0interaction_id = (Request.Form("s10SessionArray0interaction_id"))
        s10SessionArray0correct_response = (Request.Form("s10SessionArray0correct_response"))
        s10SessionArray0student_response = (Request.Form("s10SessionArray0student_response"))
        s10SessionArray0result = (Request.Form("s10SessionArray0result"))
        s10SessionArray0latency = (Request.Form("s10SessionArray0latency"))
        s10SessionArray1interaction_id = (Request.Form("s10SessionArray1interaction_id"))
        s10SessionArray1correct_response = (Request.Form("s10SessionArray1correct_response"))
        s10SessionArray1student_response = (Request.Form("s10SessionArray1student_response"))
        s10SessionArray1result = (Request.Form("s10SessionArray1result"))
        s10SessionArray1latency = (Request.Form("s10SessionArray1latency"))
        s10SessionArray2interaction_id = (Request.Form("s10SessionArray2interaction_id"))
        s10SessionArray2correct_response = (Request.Form("s10SessionArray2correct_response"))
        s10SessionArray2student_response = (Request.Form("s10SessionArray2student_response"))
        s10SessionArray2result = (Request.Form("s10SessionArray2result"))
        s10SessionArray2latency = (Request.Form("s10SessionArray2latency"))
        s10SessionArray3interaction_id = (Request.Form("s10SessionArray3interaction_id"))
        s10SessionArray3correct_response = (Request.Form("s10SessionArray3correct_response"))
        s10SessionArray3student_response = (Request.Form("s10SessionArray3student_response"))
        s10SessionArray3result = (Request.Form("s10SessionArray3result"))
        s10SessionArray3latency = (Request.Form("s10SessionArray3latency"))
        s10SessionArray4interaction_id = (Request.Form("s10SessionArray4interaction_id"))
        s10SessionArray4correct_response = (Request.Form("s10SessionArray4correct_response"))
        s10SessionArray4student_response = (Request.Form("s10SessionArray4student_response"))
        s10SessionArray4result = (Request.Form("s10SessionArray4result"))
        s10SessionArray4latency = (Request.Form("s10SessionArray4latency"))
        s10SessionArray5interaction_id = (Request.Form("s10SessionArray5interaction_id"))
        s10SessionArray5correct_response = (Request.Form("s10SessionArray5correct_response"))
        s10SessionArray5student_response = (Request.Form("s10SessionArray5student_response"))
        s10SessionArray5result = (Request.Form("s10SessionArray5result"))
        s10SessionArray5latency = (Request.Form("s10SessionArray5latency"))
        s10SessionArray6interaction_id = (Request.Form("s10SessionArray6interaction_id"))
        s10SessionArray6correct_response = (Request.Form("s10SessionArray6correct_response"))
        s10SessionArray6student_response = (Request.Form("s10SessionArray6student_response"))
        s10SessionArray6result = (Request.Form("s10SessionArray6result"))
        s10SessionArray6latency = (Request.Form("s10SessionArray6latency"))
        s10SessionArray7interaction_id = (Request.Form("s10SessionArray7interaction_id"))
        s10SessionArray7correct_response = (Request.Form("s10SessionArray7correct_response"))
        s10SessionArray7student_response = (Request.Form("s10SessionArray7student_response"))
        s10SessionArray7result = (Request.Form("s10SessionArray7result"))
        s10SessionArray7latency = (Request.Form("s10SessionArray7latency"))
        s10SessionArray8interaction_id = (Request.Form("s10SessionArray8interaction_id"))
        s10SessionArray8correct_response = (Request.Form("s10SessionArray8correct_response"))
        s10SessionArray8student_response = (Request.Form("s10SessionArray8student_response"))
        s10SessionArray8result = (Request.Form("s10SessionArray8result"))
        s10SessionArray8latency = (Request.Form("s10SessionArray8latency"))
        s10SessionArray9interaction_id = (Request.Form("s10SessionArray9interaction_id"))
        s10SessionArray9correct_response = (Request.Form("s10SessionArray9correct_response"))
        s10SessionArray9student_response = (Request.Form("s10SessionArray9student_response"))
        s10SessionArray9result = (Request.Form("s10SessionArray9result"))
        s10SessionArray9latency = (Request.Form("s10SessionArray9latency"))

        k13SessionArray0interaction_id = (Request.Form("k13SessionArray0interaction_id"))
        k13SessionArray0correct_response = (Request.Form("k13SessionArray0correct_response"))
        k13SessionArray0student_response = (Request.Form("k13SessionArray0student_response"))
        k13SessionArray0result = (Request.Form("k13SessionArray0result"))
        k13SessionArray0latency = (Request.Form("k13SessionArray0latency"))
        k13SessionArray1interaction_id = (Request.Form("k13SessionArray1interaction_id"))
        k13SessionArray1correct_response = (Request.Form("k13SessionArray1correct_response"))
        k13SessionArray1student_response = (Request.Form("k13SessionArray1student_response"))
        k13SessionArray1result = (Request.Form("k13SessionArray1result"))
        k13SessionArray1latency = (Request.Form("k13SessionArray1latency"))
        k13SessionArray2interaction_id = (Request.Form("k13SessionArray2interaction_id"))
        k13SessionArray2correct_response = (Request.Form("k13SessionArray2correct_response"))
        k13SessionArray2student_response = (Request.Form("k13SessionArray2student_response"))
        k13SessionArray2result = (Request.Form("k13SessionArray2result"))
        k13SessionArray2latency = (Request.Form("k13SessionArray2latency"))
        k13SessionArray3interaction_id = (Request.Form("k13SessionArray3interaction_id"))
        k13SessionArray3correct_response = (Request.Form("k13SessionArray3correct_response"))
        k13SessionArray3student_response = (Request.Form("k13SessionArray3student_response"))
        k13SessionArray3result = (Request.Form("k13SessionArray3result"))
        k13SessionArray3latency = (Request.Form("k13SessionArray3latency"))
        k13SessionArray4interaction_id = (Request.Form("k13SessionArray4interaction_id"))
        k13SessionArray4correct_response = (Request.Form("k13SessionArray4correct_response"))
        k13SessionArray4student_response = (Request.Form("k13SessionArray4student_response"))
        k13SessionArray4result = (Request.Form("k13SessionArray4result"))
        k13SessionArray4latency = (Request.Form("k13SessionArray4latency"))
        k13SessionArray5interaction_id = (Request.Form("k13SessionArray5interaction_id"))
        k13SessionArray5correct_response = (Request.Form("k13SessionArray5correct_response"))
        k13SessionArray5student_response = (Request.Form("k13SessionArray5student_response"))
        k13SessionArray5result = (Request.Form("k13SessionArray5result"))
        k13SessionArray5latency = (Request.Form("k13SessionArray5latency"))
        k13SessionArray6interaction_id = (Request.Form("k13SessionArray6interaction_id"))
        k13SessionArray6correct_response = (Request.Form("k13SessionArray6correct_response"))
        k13SessionArray6student_response = (Request.Form("k13SessionArray6student_response"))
        k13SessionArray6result = (Request.Form("k13SessionArray6result"))
        k13SessionArray6latency = (Request.Form("k13SessionArray6latency"))
        k13SessionArray7interaction_id = (Request.Form("k13SessionArray7interaction_id"))
        k13SessionArray7correct_response = (Request.Form("k13SessionArray7correct_response"))
        k13SessionArray7student_response = (Request.Form("k13SessionArray7student_response"))
        k13SessionArray7result = (Request.Form("k13SessionArray7result"))
        k13SessionArray7latency = (Request.Form("k13SessionArray7latency"))
        k13SessionArray8interaction_id = (Request.Form("k13SessionArray8interaction_id"))
        k13SessionArray8correct_response = (Request.Form("k13SessionArray8correct_response"))
        k13SessionArray8student_response = (Request.Form("k13SessionArray8student_response"))
        k13SessionArray8result = (Request.Form("k13SessionArray8result"))
        k13SessionArray8latency = (Request.Form("k13SessionArray8latency"))
        k13SessionArray9interaction_id = (Request.Form("k13SessionArray9interaction_id"))
        k13SessionArray9correct_response = (Request.Form("k13SessionArray9correct_response"))
        k13SessionArray9student_response = (Request.Form("k13SessionArray9student_response"))
        k13SessionArray9result = (Request.Form("k13SessionArray9result"))
        k13SessionArray9latency = (Request.Form("k13SessionArray9latency"))
        k13SessionArray10interaction_id = (Request.Form("k13SessionArray10interaction_id"))
        k13SessionArray10correct_response = (Request.Form("k13SessionArray10correct_response"))
        k13SessionArray10student_response = (Request.Form("k13SessionArray10student_response"))
        k13SessionArray10result = (Request.Form("k13SessionArray10result"))
        k13SessionArray10latency = (Request.Form("k13SessionArray10latency"))
        k13SessionArray11interaction_id = (Request.Form("k13SessionArray11interaction_id"))
        k13SessionArray11correct_response = (Request.Form("k13SessionArray11correct_response"))
        k13SessionArray11student_response = (Request.Form("k13SessionArray11student_response"))
        k13SessionArray11result = (Request.Form("k13SessionArray11result"))
        k13SessionArray11latency = (Request.Form("k13SessionArray11latency"))
        k13SessionArray12interaction_id = (Request.Form("k13SessionArray12interaction_id"))
        k13SessionArray12correct_response = (Request.Form("k13SessionArray12correct_response"))
        k13SessionArray12student_response = (Request.Form("k13SessionArray12student_response"))
        k13SessionArray12result = (Request.Form("k13SessionArray12result"))
        k13SessionArray12latency = (Request.Form("k13SessionArray12latency"))