updating multiple records with VBScript and SQL

25 May 2005 6:38 PM

Amanda Hallock

I have an ASP page that needs to update multiple records.

I have 4 variables that are a string of values, for instance:

id = "1,2,3,4"
name = "Charlie, Lucy, Linus, Snoopy"

so I need to create 4 SQL statements like:
Update table Set name = 'Charlie' where id = '1';
Update table Set name = 'Lucy' where id = '2';

and so on...

I've used the Split command before to separate a list into an array, but
not sure how to deal with multiple arrays like this.

Suggestions?

Thanks, Amanda

*** Sent via Developersdex http://www.developersdex.com ***

25 May 2005 6:49 PM

Andrew D. Newbould

In message <eWBMljVYFHA.2***@TK2MSFTNGP15.phx.gbl>, Amanda Hallock
<aman***@southwind.org> writes
Show quoteHide quote

>
>I have an ASP page that needs to update multiple records.
>
>I have 4 variables that are a string of values, for instance:
>
>id = "1,2,3,4"
>name = "Charlie, Lucy, Linus, Snoopy"
>
>so I need to create 4 SQL statements like:
>Update table Set name = 'Charlie' where id = '1';
>Update table Set name = 'Lucy' where id = '2';
>
>and so on...
>
>I've used the Split command before to separate a list into an array, but
>not sure how to deal with multiple arrays like this.

Why not split each string into a separate array and then use both
array's to build your update statement. After all, you can run Split
more than once on a page :-)

Just remember to check the bounds of each array so that you don't try
updating 5 records when there is only data for 4.

--
Andrew D. Newbould E-Mail: newsgroups@NOSPAMzadsoft.com

ZAD Software Systems Web : www.zadsoft.com

25 May 2005 7:06 PM

Amanda Hallock

>Why not split each string into a separate array and then use both

array's to build your update statement. After all, you can run Split
more than once on a page :-)

Sounds great, but I haven't used arrays very often before, and I'm not
sure how I would use both to build the statement.

Let's say I do:

id=Split(id, ",")
name=Split(name, ",")
For x = LBound(id) to UBound(id)
statement = statement & "UPDATE table SET name = '" & name & "' WHERE
id = '" & id & "';"
Next

How does it know to iterate through the name array along with the id
array?

- Amanda

*** Sent via Developersdex http://www.developersdex.com ***

25 May 2005 7:21 PM

DrBarkley

Amanda,

Try:

idArray=Split(id, ",")
nameArray=Split(name, ",")
For x = LBound(idArray) to UBound(idArray)
statement = statement & "UPDATE table SET name = '" & nameArray(x) & "'
WHERE id = '" & idArray(x) & "';"
Next

Dave

Show quoteHide quote

"Amanda Hallock" <aman***@southwind.org> wrote in message
news:ONrqczVYFHA.3840@tk2msftngp13.phx.gbl...
>
> >Why not split each string into a separate array and then use both
> array's to build your update statement. After all, you can run Split
> more than once on a page :-)
>
> Sounds great, but I haven't used arrays very often before, and I'm not
> sure how I would use both to build the statement.
>
> Let's say I do:
>
> id=Split(id, ",")
> name=Split(name, ",")
> For x = LBound(id) to UBound(id)
> statement = statement & "UPDATE table SET name = '" & name & "' WHERE
> id = '" & id & "';"
> Next
>
> How does it know to iterate through the name array along with the id
> array?
>
> - Amanda
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

26 May 2005 3:00 PM

Amanda Hallock

Thanks a bunch Dave, worked like a charm. -Amanda

*** Sent via Developersdex http://www.developersdex.com ***