Visual Basic - updating multiple records with VBScript and SQL

Author

updating multiple records with VBScript and SQL

Amanda Hallock

2005-05-25, 8:55 pm

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 ***

Andrew D. Newbould

2005-05-25, 8:55 pm

In message <eWBMljVYFHA.2508@TK2MSFTNGP15.phx.gbl>, Amanda Hallock
<amanda2@southwind.org> writes
>
>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

Amanda Hallock

2005-05-25, 8:55 pm

>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 ***

DrBarkley

2005-05-25, 8:55 pm

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

"Amanda Hallock" <amanda2@southwind.org> wrote in message
news:ONrqczVYFHA.3840@tk2msftngp13.phx.gbl...
>
> 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 ***

Amanda Hallock

2005-05-26, 3:56 pm

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

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

Sponsored Links