Multi Update with If Else Statement ... error

[bcap]

Hi,

I am trying to create an update statement that works well until I try
to make a IF THEN ELSE decision to determine the status update. Below
is my code. I would sincerely appreciate any thoughts and/or
suggestions ...



DID = Split(Request.Form("DID"),",")
CongratsLetDt = Split(Request.Form("CongratsLetDt"),",")
ShipRefNo = Split(Request.Form("ShipRefNo"),",")
dshipmentdate = Split(Request.Form("dshipmentdate"),",")


StatusA = Split("0",",")
StatusB = Split("1",",")
StatusC = Split("2",",")

Dim id
For id=0 to uBound(DID)

sql_update = "UPDATE DetailRecs"
sql_update = sql_update & " SET "',"
sql_update = sql_update & "CongratsLetDt='" & trim(CongratsLetDt(id))
& "',"

If trim(dshipmentdate(id))="" then
sql_update = sql_update & "dshipmentdate='00000000',"
Else
sql_update = sql_update & "dshipmentdate='" & trim(dshipmentdate(id))
& "',"
End If

If trim(ShipRefNo(id))="" then
Else
sql_update = sql_update & "dshipmentrefno='700900800000645" & trim
(ShipRefNo(id)) & "',"
End if

sql_update = sql_update & "ShipRefNo='" & trim(ShipRefNo(id)) & "',"

' Status Types
' 0 - New
' 1 - Working
' 2 - Pending Finance
' 3 - Approved
' 4 - Fulfilled

If ((trim(CongratsLetDt(id))="" AND (trim(dshipmentdate(id))
="00000000" OR trim(dshipmentdate(id))="") AND trim(ShipRefNo(id))
="")) then
sql_update = sql_update & "Status='" & StatusA(id) & "'"
Else
End If

If (trim(CongratsLetDt(id))="" OR trim(ShipRefNo(id))="" OR (trim
(dshipmentdate(id))="00000000" OR trim(dshipmentdate(id))="")) then
sql_update = sql_update & "Status='" & trim(StatusB(id)) & "'"
Else
End If

If (trim(CongratsLetDt(id))<>"" AND trim(ShipRefNo(id))<>"" AND (trim
(dshipmentdate(id))<>"" OR trim(dshipmentdate(id))<>"00000000")) then
sql_update = sql_update & "Status='" & StatusC(id) & "'"
Else
End If

sql_update = sql_update & "WHERE DID =" & DID(id)

Set rs = conn.Execute(sql_update)

 

[Bob Barrows]

Hi,

I am trying to create an update statement that works well until I try
to make a IF THEN ELSE decision to determine the status update. Below
is my code. I would sincerely appreciate any thoughts and/or
suggestions ...

It would help to know what the failure is. Error message? What is
telling you what you have isn't "working well"?

Snip>
sql_update = "UPDATE DetailRecs"

You cannot troubleshoot a sql statement without knowing what it is.

Response.Write sql_update
Response.End

comment out those two lines when finished debugging.
Run the page and look at the statement. does the error stand out? If
not, copy and paste it into the query execution environment for whatever
database you are using and try to run it. You maight get a more
informative error message.
Still stuck? Show us the statement.

Set rs = conn.Execute(sql_update)

Why in the world are you opening a recordset on a query that does not
return records?!? Simply do this:

conn.Execute sql_update,,129

The 129 tells ADO that the sql statement won't return records which
makes it skip the step of creating an implicit recordset to receive the
resultset.

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers (tokens):
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

SQL Server:
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en

 

[bcap]

Thank you Bob!

Here is what my query looks like when doing the Response.Write. It
does run when I copy and paste it into Access to do the query. What
appears to be the problem is it is not looping past the first record
for some reason ...

UPDATE DetailRecs SET
CongratsLetDt='07/04/09',dshipmentdate='08/04/09',ShipRefNo='',Status='1'
WHERE DID =37162887

Here is the error message I get:

Microsoft VBScript runtime error '800a0009'

Subscript out of range: '1'

index.asp, line 148

Line 148 in my earlier query is: sql_update = sql_update & "Status='"
& StatusA(id) & "'"


Sincere Thanks!

 

[Bob Barrows]

Thank you Bob!

Here is what my query looks like when doing the Response.Write. It
does run when I copy and paste it into Access to do the query. What
appears to be the problem is it is not looping past the first record
for some reason ...

Now see? Simply showing us the error message would have kept me from
focussing on irrelevencies (you should still address the issues I
raised)

Here is the error message I get:

Microsoft VBScript runtime error '800a0009'

Subscript out of range: '1'

index.asp, line 148

Line 148 in my earlier query is: sql_update = sql_update & "Status='"
& StatusA(id) & "'"

Well, if that is really line 148, then it seems that the StatusA array
(I'm not really sure it is an array, btw) only contains a single
element. You're expecting it to contain as many elements as contained in
the DID array?
So let's look at the code where you defined the array:

StatusA = Split("0",",")

Hmmm, what do you think the purpose of the split function is? Are you
trying to create an array with 3 elements, the first containing 0, and
the others containing empty strings? If so, you should be using the
Array() function, not the Split() function. Split() is used to break up
a string containing multiple characters, putting the resulting
substrings into an array.
s = "break this up"
ar = split(a, " ")
ar now contains 3 elements, each containing one of the words.

Your statement above, since the string to be split only contains a
single character ("0") causes a single-element array to be created. I
think you wanted to say:
StatusA=Array("0",",")

But this is fraught with danger as well. How are you guaranteeing that
the DID array will only ever contain 3 elements?