jerrym
Posts: 52
|
| Posted: 11/28/2006, 6:22 PM |
|
hi
would like some help in troubleshooting this issue. attempting to update a column (FTRFlag, tblEquipment, Boolean, 1 or 0) from a record form (tblFTR, Completed, Boolean 1 or 0) using the After Execute Update custom code. There are no errors given when submit button is pressed. Table FTR updates ok BUT table Equipment's column FTRFlag IS NOT updating with values from Table FTR's column Completed. i'm using CCS 3.1, Win2003, IIS. Below is my code hopefully someone can help me out.
<%
'BindEvents Method @1-276919F6
Sub BindEvents()
Set FTR.DataSource.CCSEvents("AfterExecuteUpdate") = GetRef("FTR_DataSource_AfterExecuteUpdate")
End Sub
'End BindEvents Method
Function FTR_DataSource_AfterExecuteUpdate(Sender) 'FTR_DataSource_AfterExecuteUpdate @2-EC9F41D3
'Custom Code @56-73254650
' -------------------------
Dim SQL
Dim Connection
Set Connection = New clsDBCASE_SQL
Connection.Open
SQL="UPDATE Equipment SET FTRFlag=" & FTR.DataSource.Completed.Value & " WHERE EquipID=FTR.DataSource.EquipmentID.Value"
Connection.Execute (SQL)
Set Connection=nothing
' -------------------------
'End Custom Code
End Function 'Close FTR_DataSource_AfterExecuteUpdate @2-54C34B28
%>
|
 |
 |
Edd
Posts: 547
|
| Posted: 11/28/2006, 7:28 PM |
|
Jerry,
SQL="UPDATE Equipment SET FTRFlag=" & FTR.DataSource.Completed.Value & " WHERE EquipID=FTR.DataSource.EquipmentID.Value"
Should be
SQL="UPDATE Equipment SET FTRFlag=" & FTR.DataSource.Completed.Value & " WHERE EquipID=" & FTR.DataSource.EquipmentID.Value
Before you execute the code you should also test for errors:
If FTR.Errors.Count > 0 Then Exit Function
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
jerrym
Posts: 52
|
| Posted: 11/28/2006, 8:05 PM |
|
hi Edd
many thanks for replying. mate i tried your code but it still does not work. would you mind having another look at where i've gone wrong. thanks
|
|
|
|
Edd
Posts: 547
|
| Posted: 11/28/2006, 8:50 PM |
|
Jerry,
Let us make it simple so we can see what is going on:
After the line
Set Connection=nothing
Put in (temporarily)
Response.Write SQL
Response.End
And see if the SQL is properly formed.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
jerrym
Posts: 52
|
| Posted: 11/28/2006, 9:04 PM |
|
Hi Edd
this is the result following the addition of the 2 statements you suggested.
UPDATE Equipment SET FTRFlag=True WHERE EquipID=2
btw FTRFlag in sql server has numeric as the datatype, out of curiousity i also tested another column called FTFlag with a bit datatype, no changes in both these columns.
i'm at a lost, could you assist further. thanks mate.
|
|
|
|
Edd
Posts: 547
|
| Posted: 11/28/2006, 9:24 PM |
|
Jerry,
The following has occured
CCS has kindly translated a Bit (0/1) to a True - you will need to verify your connection settings in CCS so it sends back a 1 or 0.
If you copy and paste the displayed SQL into a query analyser if will fail.
You can fix this by taking the value to a variable first and test it for True or False and populating the SQL for the update.
Dim SQL
Dim Connection
Dim StrCompleted
Dim StrEquipmentID
If FTR,Errors.Count > 0 Then Exit Function
StrCompleted = FTR.DataSource.Completed.Value
StrEquipmentID = FTR.DataSource.EquipmentID.Value
if StrCompleted = "True" Then
StrCompleted = 1
Else
StrCompleted =0
End If
Set Connection = New clsDBCASE_SQL
Connection.Open
SQL="UPDATE Equipment SET FTRFlag=" & StrCompleted & " WHERE EquipID=FTR.DataSource.EquipmentID.Value"
Connection.Execute (SQL)
Set Connection=nothing
I think you get the picture.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
jerrym
Posts: 52
|
| Posted: 11/28/2006, 9:35 PM |
|
hi Ed
many thanks for your help, i'll try your new code in a moment, what i dont get is that the checkbox 'Completed' in my FTR form is set to Boolean, checked =1 unchecked=0
so why is CCS interpreting it differently?
regards
jerry
|
|
|
|
jerrym
Posts: 52
|
| Posted: 11/28/2006, 9:51 PM |
|
hey Ed
your code works perfectly when converting true/false to 1/0
appreciate your time mate. if you could advise why ccs is reading true/false it would be great.
regards
jerry
|
|
|
|
Edd
Posts: 547
|
| Posted: 11/28/2006, 10:37 PM |
|
To tell you the true not sure if it's CCS or ASP that converts it. Did your database connection settings say True / False for binary OR 1 / 0.
Regards,
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
jerrym
Posts: 52
|
| Posted: 11/29/2006, 6:45 PM |
|
Hi edd
in sql server the bit datatype is 1/0. many thanks for your help.
|
|
|
|
