ColinH
Posts: 15
|
| Posted: 12/04/2006, 11:07 PM |
|
Hi
I'm new to CCS and not a trained programmer. We have a set of forms connecting to Oracle tables using ASP with templates. All of this is working pretty well. What I'm strugling with now is the following. I have an INCIDENTS table and a form for capturing incidents. These can be assigned as work orders. We have setup a button in the incidents grid for assignng a WO to the incident. When clicked this button opens a new WORK ORDER form and displays the INC_ID (noneditable/hidden) and records it in the WO Table as this is the link column between INCIDENTS and WO - That all works nicely.
However, once the Work order details are completed on the WO form and the record inserted, I want to write a value of "Yes" back into the incidents table into the WO_ASSIGNED column. In this way when the incidents grid is displayed initially I can show which incidents have already had at least one WO assigned to them.
I've tried to use the After_executeUpdate event with some custom code - shown below. In my desperation, as you'll see, I've been testing with a "hardcoded " INC_ID of 23.
The project is using a DB connection called Connection1
Function OSM_WO_DataSource_AfterExecuteUpdate(Sender) 'OSM_WO_DataSource_AfterExecuteUpdate @2-C173A4F6
'Custom Code @73-73254650
' -------------------------
' Write your own code here.
' -------------------------
Dim SQL
Dim Connection
Dim ErrorMessage
Set Connection = New clsDBConnection1
Connection.Open
SQL ="UPDATE OSM_INCIDENT SET WO_ASSIGNED='YES' where INC_ID=23;"
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
On Error Goto 0
'End Custom Code
|
 |
 |
Edd
Posts: 547
|
| Posted: 12/05/2006, 1:16 PM |
|
Colin,
For a non programmer you have done quite well.
Just a couple of things
1. UPDATE OSM_INCIDENT SET WO_ASSIGNED='YES' where INC_ID=23;"
Is WO_ASSIGNED a varchar or char field or is it a bit.
2. Try moving the event to AfterUpdate and before you run the script test if the update did not encounter any errors, ie
Put in a line
if OSM_WO.Errors.Count > 0 Then Exit Function
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
ColinH
Posts: 15
|
| Posted: 12/05/2006, 2:30 PM |
|
Thanks Edd,
1. The column WO_ASSIGNED is VARCHAR2(3)
2. Moved it to AfterUpdate
3. Added error check as shown below
When I click add on the form it apparently completes and continues. Any edited values on the form are updated to the OSM_WO table successfully but the custom code is not updating the WO_ASSIGNED column in the OSM_INCIDENTS table. Is there something I need to put in the custom code to get a value for the specific record I'm trying to update (this was why I put in the where INC_ID=23 part. Ultimately I want this to update depending on which inc_id (record) has bee selected.
Where/what would I see if there was an error message thrown?
Dim SQL
Dim Connection
Dim ErrorMessage
Set Connection = New clsDBConnection1
Connection.Open
SQL ="UPDATE OSM_INCIDENT SET WO_ASSIGNED='YES' where INC_ID=23;"
if OSM_WO.Errors.Count > 0 Then Exit Function
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
On Error Goto 0
General:
Code Charge has helped a lot but now I've hit the wall in a few places, with trying to execute a SQL statement. I guess some understanding of the programming constructs and what to put where would be of great help. The docs give sample code but it doesn;t help me that much - I need a few comments like in the follwoing exampe code. What do each of the parts of the connection.tosql statements below mean and do. Maybe this is just VB training...
Set Connection = New clsDBConnection1
Connection.Open
SQL = "INSERT INTO report (report_task_id,report_creator) "&_
"VALUES ("&
Connection.ToSQL(CCGetFromGet("task_id",0),ccsInteger) &","&
Connection.ToSQL(CCGetUserID(),ccsInteger) &")"
Connection.Execute(SQL)
Once again thank you for your time.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 12/05/2006, 2:43 PM |
|
Colin,
Is your code executed but database not updated? Or is your code not being executed at all?
This can be usually checked using Response.Write & Response.End.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
ColinH
Posts: 15
|
| Posted: 12/05/2006, 2:56 PM |
|
Peter,
Changes on the Record form are updated to the record table (OSM_WO).
The custom code is to make a change on a different table (OSM_INCIDENT).
I'm not sure if the custom code is being executed.
When the add button is clicked I return to a different list/page (actually the incidents page).
I just tried your suggestion with this code (below) and don't get any errors or feedback - where would I see/find that anyways? The INC_ID column in the OSM_INCIDENTS table is still not being updated by the event/custom code.
Dim SQL
Dim Connection
Dim ErrorMessage
Set Connection = New clsDBConnection1
Connection.Open
SQL ="UPDATE OSM_INCIDENT SET WO_ASSIGNED='YES' where INC_ID=23;"
if OSM_WO.Errors.Count > 0 Then Exit Function
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
Response.Write
Response.end
On Error Goto 0
Thank you
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/05/2006, 3:12 PM |
|
Colin
Change code to
Dim SQL
Dim Connection
Dim ErrorMessage
if OSM_WO.Errors.Count > 0 Then Exit Function
Set Connection = New clsDBConnection1
Connection.Open
SQL ="UPDATE OSM_INCIDENT SET WO_ASSIGNED='YES' where INC_ID=23;"
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
Response.Write SQL & " " & ErrorMessage
Response.end
On Error Goto 0
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
ColinH
Posts: 15
|
| Posted: 12/05/2006, 3:27 PM |
|
Edd,
Still does exactly the same thing. I suppose the fact that it is not throwing and error means it is not crashing. The custom code is however not updating the OSM_WO table.
Any other ideas would be most welcome.
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/05/2006, 9:36 PM |
|
Colin,
Quote :The custom code is however not updating the OSM_WO table.
The OSM_WO Or OSM_INCIDENT tables.
Now that the code has been spat out the SQL statement what happens when you run the code manually through a query analyser using the exercise of cut and paste.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
ColinH
Posts: 15
|
| Posted: 12/05/2006, 9:52 PM |
|
Sorry, my sentence was misleading. I'm still not getting an error message nor a message showing the SQL string being passed to the DB. The form doesn't show any error at all.
The OSM_WO table is being updated fine from the form.
The OSM_INCIDENTS table (being referenced by the custom code) is NOT being updated.
In sqlplus the statement
UPDATE OSM_INCIDENT SET WO_ASSIGNED='YES' where INC_ID=23;
works just fine.
It would be good if I could see the string CCS is submitting to the database.
Many thanks for the help.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 12/05/2006, 10:07 PM |
|
Colin,
It seems that you're on a wrong track and unnecessarily testing the SQL.
Edd's code displays the SQL statement on the scrren but since you don't see anything this means that your event code never gets executed. The SQL itself should work fine once it gets executed. You should focus on testing the event itself, not the SQL. Just displaying "This is After Update Event" should be sufficient to check if the event works. You can display different messages in each event to see which events get executed.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
ColinH
Posts: 15
|
| Posted: 12/05/2006, 10:28 PM |
|
Absolutely - first is the event getting fired then what is it sending etc
I tried your suggestion with an AfterInsert event and presto I got Response.Write to give somthing back !!!!  This is progress
It returned this message:
UPDATE OSM_INCIDENT SET WO_ASSIGNED='YES' where INC_ID=23; ORA-00911: invalid character
This is wierd as the SQL works - cutting and pasting it from the error string into sqlplus works. sqlplus I've done quite a bit of.
So then the question remains - what am I doing wrong?
As always your feedback and suggestions are very much appreciated.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 12/05/2006, 10:51 PM |
|
OK, that's much better. Now that we know the error message, I'd try commenting the line of code with your SQL and retype it again on the next line, or copy into Notepad and then paste back into CCS. That's just to make sure that there are no invalid invisible characters encoded into that line, as this happened to me once.
Also please try removing the semicolon (;) from your SQL statement.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
ColinH
Posts: 15
|
| Posted: 12/05/2006, 11:08 PM |
|
YES, yes, yes
It was the ;
now the hardcoded update works in an AfterInsert event!! Thank you.
In the "hardcoded update" I used an INC_ID of 23 just to reduce the variables.
The final part of the challenge I have is to use the actual value for the INC_ID in tquestion in the current form (OSM_WO) in the WHERE clause. How do I do that.
Some background and details:
We have managed to capture the incident in question by setting a link on the incidents form. The user clicks this link to Assign a work order to that incident and carry a value over into the new WO form. When they click on the link the WO form opens and has the incident number pre-filled
To do this we added a before show event to the WO form:- code below...
Function OSM_WO_WO_INC_ID_BeforeShow(Sender) 'OSM_WO_WO_INC_ID_BeforeShow @8-D05DC28C
'Retrieve Value for Control @34-6B9455DB
OSM_WO.WO_INC_ID.Value = Request.QueryString("INC_ID")
'End Retrieve Value for Control
End Function 'Close OSM_WO_WO_INC_ID_BeforeShow @8-54C34B28
So the challenge is, in the code below how do I get the current value for INC_ID inserted instead of the hardcoded value of 23 ? Sorry I have no programming training.
Dim SQL
Dim Connection
Dim ErrorMessage
if OSM_WO.Errors.Count > 0 Then Exit Function
Set Connection = New clsDBConnection1
Connection.Open
SQL ="UPDATE OSM_INCIDENT SET WO_ASSIGNED='YES' where INC_ID=23"
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
Response.Write SQL & " " & ErrorMessage
Response.end
On Error Goto 0
|
|
|
|
ColinH
Posts: 15
|
| Posted: 12/05/2006, 11:38 PM |
|
Problem solved !!!
As I already had the value for INC_ID in the form I just put that into the SQL string by doing the following..
SQL ="UPDATE OSM_INCIDENT SET WO_ASSIGNED='YES' where INC_ID=" & OSM_WO.HIDDEN_INC_ID.Value
Peter and Edd - many thanks for your patience and guidance.
Who knows what I'll break now
|
|
|
|
peterr
Posts: 5971
|
| Posted: 12/07/2006, 1:24 AM |
|
Great - I'm glad this works now.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
