How to insert multiple records in a table with one SQL Statement

[pborregg]

Hello Experts,

I'm trying to insert 6 rows and ONLY 6 rows of data into a table. Right now, I had to create 6 separate "Forms" using ASP Classic. I then called the forms in a javascript like this:

Function PostData()
{

document.form1. submit();
document.form2. submit();
document.form3. submit();
document.form4. submit();
document.form5. submit();
document.form6. submit();


}

This works dandy, on my home machine. But on the server, I get this error:

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/webproject/list-stoplight.asp, line 144

It inserts the first to forms records and hiccups. I've called the hosting company and they can't figure our what's wrong. Is there another solution that will insert the data in one fell swoop?

Consider this: I have an invoice with 6 rows of products and they have to go into a table like they appear on the invoice.

Ex:

ID SECTION COLOR DESCRIPTION
1 TestSection Green Test
2 TestSection2 Red Test
3 TestSection3 Blue Test
4 TestSection4 Orange Test
5 TestSection5 Purple Test
6 TestSection6 Maroon Test

That's the invoice and that's the table. Simple right? I'd like to keep it that way.

I can do this in VB if you can provide a solution. I build my sites in Dreamweaver CS3.

Hope someone can help me.

Peter

[jeffstl]

So these 6 forms you are submitted. Does each one have it's own seperate .asp page it is submitted to for saving?

Or are they all being submitted to the same .asp page for processing?

Also I assume that if each form is different you will have different processing rules for each one as well.

There is alot of possible problems that could exist here. I would need to know alot more about what your doing on the processing side and how\where each form is submitted (what each form action is = to).

Also I would need to see the code near or around the lines where the error is occurring.

Also a silly question maybe, but if you are submitting 6 forms on a single page with one button click, why not just make them all one form?

One more thing....your subject seems to be asking how to insert multiple records with 1 sql. The easiest way to do that is setting your SQL in a loop

'for example this would insert 6 records with the same values. but Im not sure this is what your talking about. If you need to change the MyTextValue each time then just do a SELECT CASE or something with this to determine which sql to use.

sSQL = "Insert into MyTable (column1) VALUES ('" & MyTextValue & "')"

for x = 0 to 5
     DbConnection.Execute sSQL
next

[pborregg]

No, there's only one ASP page with 6 forms ecapsulating 6 tables (mimicking rows of a single table).

No, each form processes the same way. There's nothing unique about the forms except their names. I do have 6 recordsets (built in dreamweaver) with each unique recordset going to each unique form.

I can't make them all one form unless you know of another way to do that?

Perhaps I can... let me think about it. I could use the recordsets, tie them to the rows of the table (6 rows), uniquely, and then possibly do it that way. Hmmmm, perhaps that may work. I'll try it and let you know.

Thanks for the tips...

Peter