Batch Update Statement Help

[bcap]

Hi,

I have written the below update statement and it works fine if there
are multiple records in my recordset, but if there is just one single
record it will not update that one record. Any ideas to what I am
doing wrong or need to do right? =)

dim id
dim xx
if uBound(DID) = 0 then
xx=1
else
xx=0
end if
for id=xx to uBound(DID)
sql_update = "update " & t_maintable & ""
sql_update = sql_update & " SET "',"
sql_update = sql_update & "ConLetDt='" & trim(ConLetDt(id)) &
"',"
sql_update = sql_update & "PID='" & trim(PID(id)) & "',"
sql_update = sql_update & "SID='" & trim(SID(id)) & "',"
sql_update = sql_update & "RespDt='" & trim(RespDt(id)) & "',"
sql_update = sql_update & "TVal='" & trim(TVal(id)) & "',"
sql_update = sql_update & "PSExportDt='" & trim(PSExportDt(id)) &
"',"
sql_update = sql_update & "dShipInd='" & trim(dShipInd(id)) &
"',"
sql_update = sql_update & "ShipRefNo='" & trim(ShipRefNo(id)) &
"'"
sql_update = sql_update & "WHERE DID =" & DID(id)
conn1.Execute sql_update,,129

Thank you in advance!!!!!!!

 

[Adrienne Boswell]

Gazing into my crystal ball I observed bcap <[email protected]> writing
in @o21g2000vbl.googlegroups.com:

Hi,

I have written the below update statement and it works fine if there
are multiple records in my recordset, but if there is just one single
record it will not update that one record. Any ideas to what I am
doing wrong or need to do right? =)

dim id
dim xx
if uBound(DID) = 0 then
xx=1
else
xx=0
end if
for id=xx to uBound(DID)
sql_update = "update " & t_maintable & ""
sql_update = sql_update & " SET "',"
sql_update = sql_update & "ConLetDt='" & trim(ConLetDt(id)) &
"',"
sql_update = sql_update & "PID='" & trim(PID(id)) & "',"
sql_update = sql_update & "SID='" & trim(SID(id)) & "',"
sql_update = sql_update & "RespDt='" & trim(RespDt(id)) & "',"
sql_update = sql_update & "TVal='" & trim(TVal(id)) & "',"
sql_update = sql_update & "PSExportDt='" & trim(PSExportDt(id)) &
"',"
sql_update = sql_update & "dShipInd='" & trim(dShipInd(id)) &
"',"
sql_update = sql_update & "ShipRefNo='" & trim(ShipRefNo(id)) &
"'"
sql_update = sql_update & "WHERE DID =" & DID(id)
conn1.Execute sql_update,,129

Thank you in advance!!!!!!!

Are you getting an error message? Put a response.write sql_update before
the execute and see what it says. Post back here.

 

[bcap]

Hi,

Thank you for your relpy. No, I do not get an error message. It just
seems like it does not want to update the record if it is only one
record.

If I have 50 records it could batch 49 of them or even all 50 at one
time, but if I have only 1 record, it seems like it just ignores the
update.

When the record has multiple updates at a time the

uBound = 1
xx = 0

When it is a single record being updated the

uBound = 0
xx = 1

Could this be the cuase of the problem?

 

[Bob Barrows]

Hi,

Thank you for your relpy. No, I do not get an error message. It just
seems like it does not want to update the record if it is only one
record.

If I have 50 records it could batch 49 of them or even all 50 at one
time, but if I have only 1 record, it seems like it just ignores the
update.

When the record has multiple updates at a time the

uBound = 1
xx = 0

When it is a single record being updated the

uBound = 0
xx = 1

Could this be the cuase of the problem?

We can't tell without seeing the sql statement being executed.

 

[bcap]

Here is the error message:

Microsoft VBScript runtime error '800a0009'
Subscript out of range: '0'





I realize that if works OK if all fields are populated, but this error
produces if at least one of these fields is NULL

 

[Evertjan.]

bcap wrote on 28 aug 2009 in microsoft.public.inetserver.asp.general:

dim xx
if uBound(DID) = 0 then
xx=1
else
xx=0
end if
for id=xx to uBound(DID)

Why not make it simple:

dim id, xx
if uBound(DID) > 0 then
for id = 0 to uBound(DID)
.........
next
end if

 

[Bob Barrows]

Here is the error message:

Microsoft VBScript runtime error '800a0009'
Subscript out of range: '0'





I realize that if works OK if all fields are populated, but this error
produces if at least one of these fields is NULL

I'm lost. I thought you originally said there was no error message. Let's
see ... yes, you did say this: "No, I do not get an error message"
The symptom I was dealing with was the one where a sql statement was
generated that did not produce the intended results. The only way to deal
with that situation is to discover what the failing sql statement looks like
using Response.Write.

Now you tell me that you are getting an error message. What line of code
raises that error?

And what "fields" are you talking about? All we have seen is a partial block
of vbscript that supposedly loops through what appears to be an array and
generates a string that should contain a sql statement to be executed. More
details please.