insert an array of user data into database

Question

Post reply

insert an array of user data into database

Betty Sun

SSCrazy

Points: 2442
More actions
January 25, 2006 at 11:54 am

#96454

Hi all,
I am trying to insert serveral rows of data into a table using store procedure, but don't know how to pass an array of data from asp.net to sql database, The while loop is trying to insert different dependent data into table.
CREATE PROCEDURE add_dependent
@numdep int
@txntime datetime=NULL,
@FName varchar(50),
@MI char(1),
@lname varchar(50),
@ssn varchar(11),
@sex char(1)=NULL,
@orderDetailsID int=NULL,
@dpssn varchar(11)
AS
Declare i int=0
-- Execute the INSERT statement.
while i< numdep
Begin
INSERT INTO Dependents
(txndatetime, fname, MI, lname,ssn, sex,OrderDetailsID, dependentssn) values
(@txntime,@fname,@mi,@lname,@ssn,@sex, @orderDetailsID,@dpssn)
-- Test the error value.
IF @@ERROR <> 0
BEGIN
    -- Return 99 to the calling program to indicate failure.
    PRINT 'An error occurred inserting the new dependt information'
    Goto error:
END
ELSE
BEGIN
    -- Return 0 to the calling program to indicate success.
    PRINT 'The new dependent information has been loaded'
    set i=i+1
    Continue
END
End
Error: Rollback transaction

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jordanac Hall of Fame Points: 3653 More actions · Answer 1

Do you want to pass an array? like exec 'ACJG,12547896,2006/01/01' comma or something seperated.

Then your stored proc must be like this

CREATE PROCEDURE add_dependent

@incomming_array varchar(255)

as........

Working with arrays IMHO should be avoided in SQL as it "OVERCOMPLICATES" your stored proc. (I do it when the front end must)

Do do you want to pass parameters as in your stored proc ? like exec 'ACJG','12547896','2006/01/01'

Andy.

vadba SSChampion Points: 11133 More actions · Answer 2

In your asp.net code, loop through your array and call the stored procedure for each row.

bledu SSChampion Points: 12335 More actions · Answer 3

you could make it intresting by using xml file.post an xml file with the data using a stored proc.

Everything you can imagine is real.

Betty Sun SSCrazy Points: 2442 More actions · Answer 4

Hi all,

Thanks all for your postings, that's a great help to me.

Yesterday, I googled for a while, then an article gave me an idea,just like jordanac mentioned that I can pass a string with some format like "fname1, lastname1,ssn1,sex1; fname2, lastname2, ssn,2sex2;..."

then parse each person's information through ";" and each column's information through ",", a little bit complicated. Probably in asp.net code, Maybe loop through array and call store procedure is a better way. Don't know much about xml.

Betty

bledu SSChampion Points: 12335 More actions · Answer 5

there is no better time to know about xml than now.

Everything you can imagine is real.

Betty Sun SSCrazy Points: 2442 More actions · Answer 6

Bledu,

Can you provide more information, link or article about xml.

Thanks

Betty

bledu SSChampion Points: 12335 More actions · Answer 7

http://www.perfectxml.com/

hope it helps

Everything you can imagine is real.

Marbry Hardin Old Hand Points: 331 More actions · Answer 8

If you get to the point you feel you need to write some sort of looping structure you're probably going to be better off keeping that part of it in your ASP.Net code.

If it is a large number of entries, you may be better served by inserting them into an XML file and simply submitting that to the proc as someone suggested above.