Insert multiple rows via web using single stored procedure

[uncleroydee]

I need to build a process for inserting multiple rows into a SQL 7 table over the web. Probably 15 +/- rows per page and 10 fields per row. My initial thought was to use a bulk insert statement within a stored procedure.

Does anyone have any experience with this?

Thanks.

 

[tlbroadbent]

The method you use will depend on the source of your data. If you are inserting from a text file then bulk insert would work. If you are inserting from the web page, you can pass the data from the web page via ADO calls. You can pass a batch of procedure executions as follows.

exec my_SP val1, val2, val3, ...
exec my_SP val11, val12, val13, ...
exec my_SP val21, val22, val23, ...
exec my_SP val31, val32, val33, ...
.
.
.


Terry

http://members.home.net/tlbroadbent/index.htm

 

[uncleroydee]

Terry.

I already had a handle of the syntax for the stored procedure, but I didn't phrase my question correctly (and thanks for the suggestion). I need to build my web page table to contain up to 15 rows and 10 fields per row but will need to permit users to enter between 1 and 15 records at a time.

I think I understand what you're saying; execute the SP once for every row in the table with unique names applied to every input field. My next question is, won't this scenario fail whenever a user submits the form (executes the SP) with empty rows in the form? Plus, I can't insert rows populately entirely with nulls into my table.

Thanks again.

 

[tlbroadbent]

You can build the string that is sent to your SP with VBScript or Javascript. Using the script you can avoid sending rows not filled in.

Or you can test in the SP and if the variables received are null, you can Retrun without inserting a record. Terry

http://members.home.net/tlbroadbent/index.htm

 

[uncleroydee]

I think I prefer the javascript approach to reduce traffic and provide immediate validation to the user, although the amount of data sent would be very small.

Here is what I'm thinking; the page where the user enters the data submits to a second page that contains the database connection language and SP commands...
exec my_SP val1, val2, val3, ...
exec my_SP val11, val12, val13, ...
exec my_SP val21, val22, val23, ...
exec my_SP val31, val32, val33, ......

I've never placed the SP syntax in Javascripts before, so can you do something like &quot;if val1 <>'' then exec my_SP val1, val2, val3, ... then go to next SP else endif&quot;

I plan to use validation scripts in the form to require a key value be entered before a user can move to the next row in the form so in the above scenario as soon as a null is encountered in the key value field no further iterations of the SP would be executed.

I hope this is lucid, and I appreciate your help. Any syntax suggestions (Jscript or SQL)are certainly welcome.