Vista - Trouble with Array

Hi,

I am trying to learn how to use an array for an update statement and
seem to be getting spaces in my update.

Here is what the update looks like:

UPDATE Location SET LocDesc='Test1', Active='1' WHERE LocationID='13'
UPDATE Location SET LocDesc=' Test2', Active=' 1' WHERE LocationID='
12'
UPDATE Location SET LocDesc=' Test3', Active=' 1' WHERE LocationID='
5'
UPDATE Location SET LocDesc=' Test4', Active=' 1' WHERE LocationID='
9'
UPDATE Location SET LocDesc=' Test5', Active=' 1' WHERE LocationID='
3'

(Notice, the first one worked as expected, but the following ones have
spaces before the parameter starts)

Here is my query:

'LocDesc
Dim MyLocDesc,LocDescArray
MyLocDesc = request.form("LocDesc")
LocDescArray=Split(MyLocDesc,",")

'Active
Dim MyActive,ActiveArray
MyActive = request.form("Active")
ActiveArray=Split(MyActive,",")

'LocID
Dim MyLocID,LocIDArray
MyLocID = request.form("LocationID")
LocIDArray=Split(MyLocID,",")

For i = 0 to uBound(LocDescArray)

set conn=server.createobject("adodb.connection")
conn.open "data Source=xxx;User ID=xxx;Password=xxx;"

sqlstmt = "UPDATE Location"
sqlstmt = sqlstmt & " SET "'"


sqlstmt = sqlstmt & "LocDesc='"& LocDescArray(i) & "', "
sqlstmt = sqlstmt & "Active='" & ActiveArray(i) & "'"
sqlstmt = sqlstmt & " WHERE LocationID='" & LocIDArray(i) &
"'"

response.write sqlstmt
response.write "<BR>"
Set RS = conn.execute(SQLstmt)
Next

Can you please help? Is there a better way to do this?

Thanks so much!

bcap schrieb:Assuming that the leading spaces are caused by MyLocDesc containing
text like

"FrsLoc, SecLoc, ThrLoc"

you could change
to

sqlstmt = sqlstmt & "LocDesc='"& Trim( LocDescArray(i) ) & "', "
sqlstmt = sqlstmt & "Active='" & Trim( ActiveArray(i) ) & "'"

This would deal with leading blanks caused by other agents too.

In general, you should consider using stored precedures/commands
instead of dynamically concatenated SQL to do the updates. As a first
step in that direction consider:

sSQLT = "UPDATE Location SET LocDesc='@L@', Active='1' WHERE LocationID='@I@'"
....
For i = 0 to uBound(LocDescArray)
....
sSQL = Replace( Replace( sSQLT, "@L@", Trim( LocDescArray(i) ) ), _
"@I@", Trim( ActiveArray(i) ) )
...

"bcap" <rayh@xxxxxx> wrote in message
news:13c442ae-ace2-4eb0-8ab9-c3150a2e4c34@xxxxxxThe spaces must be in the comma delimited strings you are converting into
arrays. Use the Trim function to remove any leading or trailing spaces. For
example:

sqlstmt = sqlstmt & "LocDesc='"& Trim(LocDescArray(i)) & "', "
sqlstmt = sqlstmt & "Active='" & Trim(ActiveArray(i)) & "'"
sqlstmt = sqlstmt & " WHERE LocationID='" & Trim(LocIDArray(i)) & "'"

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

Thanks for the help guys! Very much appreciated!!!!