UPDATE not updating

[disk42]

Greetings all,

I have 2 ASP files that work on an Access DB - Update.asp
and ThankYou.asp. Update.asp inserts rows into the DB,
and ThankYou.asp updates a Yes/No field, setting it to
true. This setup used to work, but I've updated
Update.asp and the DB. Now, the UPDATE statement in
ThankYou.asp doesn't change the field to True, and returns
no error. If I go back to the old Update.asp, it works
fine.

The updates I've made are:
In the DB, I added a text column.
In Update.asp, I changed it from being able to append the
DB upon submission of a form to also functioning as
an "autosave" for the form (it's called from a Java
applet).

ThankYou.asp has not changed, and it now no longer works. I have checked to see if an error has occurred by using "response.write err.number", but it always returns a 0. Any ideas?

Thanks for your help.

Phil Rau

 

[Quehay]

Phil,

If you submit the code for review--it will be easier to help you out...


Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com

 

[disk42]

Here's the update code:

strSQL = "UPDATE OrderDetails SET Uploaded=True, ETADate=#" & cdate(strDateComplete) & "# WHERE OrderNumber=" & intJobOrderNumber
objcon.Execute strSQL

It's not in an If..Then statement, so it always executes.

I'm not sure if you wanted the whole files, but one is 10K and the other is 28K.

Thanks,
Phil

 

[nicsin]

Hi disk42,

it may sound simple but are you sure the where criterion is met? Apart from that is strDateComplete in date format?

Also, if I were you, I would write 2 update queries, one for each field.

Hope it helps!

nicsin

 

[disk42]

I know the criterion is met. I put code in to search for the records before the UPDATE line to test this, and I've gotten a result. strDateComplete is in date format; as I've said, the code used to work. I also tried using 2 update queries, and that did not solve my problem either.

 

[Quehay]

A shot in the dark...

Get rid of the Cdate function. The SQL statement needs to be text entirely--then the SQL engine will parse the delimited date string and do what it needs to with it. VB is probably going to do implicit conversions as needed regardless but the logic of Cdate is saying make this into a number even though I'm still delimiting it with # as a date literal...

Also, it's a huge pain in ASP but you really should check the VB Err object and the Errors collection of the Connection object before moving along. Or at least check the return from Cnn.Execute--the "RecordsAffected" output parameter.

[tt]

Sub ExecuteCommand(strSQL)
On Error Resume Next

Dim lngRecordsAffected

CheckConnectionState

CNN_COMMON.BeginTrans

CNN_COMMON.Execute strSQL,lngRecordsAffected,adCmdText

'Abort if any connection errors
If CNN_COMMON.Errors.Count <> 0 or Err <> 0 Then

CNN_COMMON.RollbackTrans

ElseIf lngRecordsAffected <> 1 Then

CNN_COMMON.RollbackTrans
RaiseDataError &quot;ExecuteCommand&quot;, strSQL

'No errors and only one record affected the all clear COMMIT
Elseif lngRecordsAffected = 1 Then

CNN_COMMON.CommitTrans

End If

End Sub 'ExecuteCommand [/tt]



Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com

 

[disk42]

I tried taking the CDate function out, I separated it back into 2 update statements, and output &quot;err.number&quot; for each one. It still doesn't work, and err.number is 0 for both statements. There are also no errors in the connection's Errors collection.

Could it have something to do with an INSERT statement executed in another file? Update.asp has 3 INSERT statements that are executed depending on what kind of file is being uploaded, and whether the insert is an &quot;autosave&quot; or form submission. They have the following structure:
&quot;INSERT INTO OrderDetails (columns being updated) VALUES(values being updated)&quot; They also return no errors.