Peter K
|
| Posted: 07/16/2003, 2:48 PM |
|
I have spent hours trying to debug a problem associated with the beforebuildinsert function. I can set the textbox value in beforebuildinsert but when it creates the insert statement, it codes NULL for the value. I added the NOTES - comments in this forum so you know where the data is set correctly. What am I doing wrong?
Here is the code:
function nfirs_1abcde_record_ds_BeforeBuildInsert()
{
$nfirs_1abcde_record_ds_BeforeBuildInsert = true;
//End nfirs_1abcde_record_ds_BeforeBuildInsert
//Custom Code @161-EE98CC07
// -------------------------
global $nfirs_1abcde_record;
global $DBFECSConnection;
echo ("In beforebuildinsert routine");
$newincidentno = CCDLookUp("INCIDENT_NO + 1", "incident_master", "", $DBFECSConnection);
$SQL = "UPDATE incident_master SET INCIDENT_NO = ". $newincidentno;
$db = new clsDBFECSConnection;
$db->query($SQL);
unset ($db);
$yearcode = date("Y");
while (strlen($newincidentno) < 7 ){
$newincidentno = "0" . $newincidentno;
}
$formattedincidentno = substr($yearcode,3,1). "-" . $newincidentno;
echo ($formattedincidentno); NOTE-DATA DISPLAYS HERE
$nfirs_1abcde_record->INCIDENT_NO->SetValue($formattedincidentno);
echo ($nfirs_1abcde_record->INCIDENT_NO->GetValue()); NOTE-DATA DISPLAYS HERE
// -------------------------
//End Custom Code
function Insert()
{
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeBuildInsert");
echo ("getdbvalue=");
echo ($this->INCIDENT_NO->GetDBValue());
echo ("datatype=");
echo ($this->INCIDENT_NO->DataType);
echo ("getvalue before insert=");
echo ($this->INCIDENT_NO->GetValue());
$this->SQL = "INSERT INTO nfirs_1abcde ("
. "INCIDENT_NO, "
. "INCIDENT_DATE, "
. "FDID, "
. "STATION, "
. "INCIDENT_STATE, "
. "DATE_OF_ALARM, "
. "DATE_OF_ARRIVAL, "
. "DATE_CONTROLLED, "
. "DATE_LAST_UNIT_RETURN, "
. "TIME_OF_ALARM, "
. "TIME_OF_ARRIVAL, "
. "TIME_CONTROLLED, "
. "TIME_LAST_UNIT_RETURN, "
. "INCIDENT_TYPE, "
. "LOCATION_TYPE, "
. "STREET_NUMBER, "
. "STREET_PREFIX, "
. "STREET_NAME, "
. "STREET_TYPE, "
. "STREET_SUFFIX, "
. "APT_SUITE_ROOM, "
. "CITY, "
. "STATE, "
. "ZIP_CODE, "
. "CROSS_STREET, "
. "MUTUAL_AID_STATUS, "
. "MUTUAL_AID_FDID, "
. "MUTUAL_AID_STATE, "
. "MUTUAL_AID_INCIDENT_NO, "
. "SHIFT, "
. "ALARMS, "
. "DISTRICT, "
. "OFFICER_IN_CHARGE, "
. "MEMBER_MAKING_REPORT"
. ") VALUES ("
. $this->ToSQL($this->INCIDENT_NO->GetDBValue(), $this->INCIDENT_NO->DataType) . ", "
. $this->ToSQL($this->INCIDENT_DATE->GetDBValue(), $this->INCIDENT_DATE->DataType) . ", "
. $this->ToSQL($this->FDID->GetDBValue(), $this->FDID->DataType) . ", "
. $this->ToSQL($this->STATION->GetDBValue(), $this->STATION->DataType) . ", "
. $this->ToSQL($this->INCIDENT_STATE->GetDBValue(), $this->INCIDENT_STATE->DataType) . ", "
. $this->ToSQL($this->DATE_OF_ALARM->GetDBValue(), $this->DATE_OF_ALARM->DataType) . ", "
. $this->ToSQL($this->DATE_OF_ARRIVAL->GetDBValue(), $this->DATE_OF_ARRIVAL->DataType) . ", "
. $this->ToSQL($this->DATE_CONTROLLED->GetDBValue(), $this->DATE_CONTROLLED->DataType) . ", "
. $this->ToSQL($this->DATE_LAST_UNIT_RETURN->GetDBValue(), $this->DATE_LAST_UNIT_RETURN->DataType) . ", "
. $this->ToSQL($this->TIME_OF_ALARM->GetDBValue(), $this->TIME_OF_ALARM->DataType) . ", "
. $this->ToSQL($this->TIME_OF_ARRIVAL->GetDBValue(), $this->TIME_OF_ARRIVAL->DataType) . ", "
. $this->ToSQL($this->TIME_CONTROLLED->GetDBValue(), $this->TIME_CONTROLLED->DataType) . ", "
. $this->ToSQL($this->TIME_LAST_UNIT_RETURN->GetDBValue(), $this->TIME_LAST_UNIT_RETURN->DataType) . ", "
. $this->ToSQL($this->INCIDENT_TYPE->GetDBValue(), $this->INCIDENT_TYPE->DataType) . ", "
. $this->ToSQL($this->LOCATION_TYPE->GetDBValue(), $this->LOCATION_TYPE->DataType) . ", "
. $this->ToSQL($this->STREET_NUMBER->GetDBValue(), $this->STREET_NUMBER->DataType) . ", "
. $this->ToSQL($this->STREET_PREFIX->GetDBValue(), $this->STREET_PREFIX->DataType) . ", "
. $this->ToSQL($this->STREET_NAME->GetDBValue(), $this->STREET_NAME->DataType) . ", "
. $this->ToSQL($this->STREET_TYPE->GetDBValue(), $this->STREET_TYPE->DataType) . ", "
. $this->ToSQL($this->STREET_SUFFIX->GetDBValue(), $this->STREET_SUFFIX->DataType) . ", "
. $this->ToSQL($this->APT_SUITE_ROOM->GetDBValue(), $this->APT_SUITE_ROOM->DataType) . ", "
. $this->ToSQL($this->CITY->GetDBValue(), $this->CITY->DataType) . ", "
. $this->ToSQL($this->STATE->GetDBValue(), $this->STATE->DataType) . ", "
. $this->ToSQL($this->ZIP_CODE->GetDBValue(), $this->ZIP_CODE->DataType) . ", "
. $this->ToSQL($this->CROSS_STREET->GetDBValue(), $this->CROSS_STREET->DataType) . ", "
. $this->ToSQL($this->MUTUAL_AID_STATUS->GetDBValue(), $this->MUTUAL_AID_STATUS->DataType) . ", "
. $this->ToSQL($this->MUTUAL_AID_FDID->GetDBValue(), $this->MUTUAL_AID_FDID->DataType) . ", "
. $this->ToSQL($this->MUTUAL_AID_STATE->GetDBValue(), $this->MUTUAL_AID_STATE->DataType) . ", "
. $this->ToSQL($this->MUTUAL_AID_INCIDENT_NO->GetDBValue(), $this->MUTUAL_AID_INCIDENT_NO->DataType) . ", "
. $this->ToSQL($this->SHIFT->GetDBValue(), $this->SHIFT->DataType) . ", "
. $this->ToSQL($this->ALARMS->GetDBValue(), $this->ALARMS->DataType) . ", "
. $this->ToSQL($this->DISTRICT->GetDBValue(), $this->DISTRICT->DataType) . ", "
. $this->ToSQL($this->OFFICER_IN_CHARGE->GetDBValue(), $this->OFFICER_IN_CHARGE->DataType) . ", "
. $this->ToSQL($this->MEMBER_MAKING_REPORT->GetDBValue(), $this->MEMBER_MAKING_REPORT->DataType)
. ")";
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeExecuteInsert");
$this->query($this->SQL);
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "AfterExecuteInsert");
if($this->Errors->Count() > 0)
$this->Errors->AddError($this->Errors->ToString());
$this->close();
}
//End Insert Method
Results in:
In beforebuildinsert routine3-00000143-0000014getdbvalue=datatype=3getvalue before insert=Database error: Invalid SQL: INSERT INTO nfirs_1abcde (INCIDENT_NO, INCIDENT_DATE, FDID, STATION, INCIDENT_STATE, DATE_OF_ALARM, DATE_OF_ARRIVAL, DATE_CONTROLLED, DATE_LAST_UNIT_RETURN, TIME_OF_ALARM, TIME_OF_ARRIVAL, TIME_CONTROLLED, TIME_LAST_UNIT_RETURN, INCIDENT_TYPE, LOCATION_TYPE, STREET_NUMBER, STREET_PREFIX, STREET_NAME, STREET_TYPE, STREET_SUFFIX, APT_SUITE_ROOM, CITY, STATE, ZIP_CODE, CROSS_STREET, MUTUAL_AID_STATUS, MUTUAL_AID_FDID, MUTUAL_AID_STATE, MUTUAL_AID_INCIDENT_NO, SHIFT, ALARMS, DISTRICT, OFFICER_IN_CHARGE, MEMBER_MAKING_REPORT) VALUES (NULL, '2003-07-16', '06032', '008', 'MA', '2003-07-16', '2003-07-16', '2003-07-16', '2003-07-16', '10:42', '10:51', '11:37', '12:04', '118', '1', '4556', NULL, 'Nowhere', 'ST', NULL, NULL, 'Sometown', 'MA', '01111-1111', 'Nothing St', 'N', NULL, NULL, NULL, '1', '1', '8', '62', '62')
MySQL Error: 1048 (Column 'INCIDENT_NO' cannot be null)
Session halted.
|
|
|
 |
RonB
|
| Posted: 07/16/2003, 10:59 PM |
|
"$newincidentno = CCDLookUp("INCIDENT_NO + 1", "incident_master", "", $DBFECSConnection);"
The problem here is you do not define a where clause. ccdlookup is designed to get 1 return value. your query might return 100 depending on the number of records in that table. Also you choose to do the math in the query so I take it you want the highest number and then ad 1 to it ->(max(INCIDENT_NO)+1). I f that's what you want to do you do not need to add a where clause because using max means you will be getting only 1 value returned.
"echo ($formattedincidentno); NOTE-DATA DISPLAYS HERE"
I'm not sure what it is you want to do here but you can't use echo in CCS because of the template driven nature of CCS you would have to use the $Tpl class for that.
In general the code looks a bit weird and I also wonder why you need to get the incedent nr. and then change it if it is just an autoincrement feature why would you do it like this instead of setting incedent nr to ....autoincrement in mysql.
hmmm...
Ron
|
|
|
|
Peter K
|
| Posted: 07/17/2003, 5:57 PM |
|
RonB you missed the entire point of my question. It has nothing to do with the MySQL code. It has to do with the fact that I set a value in a textbox prior to formatting the INSERT statement. When the INSERT statement is created, INCIDENT_NO has a NULL value. Why? I put the NOTE - comments in so you could see where INCIDENT_NO had a value associated with it. My SQL coding is fine but CCS is not handing textbox values correctly. All the echo statement does is show me the value for debugging purposes, not the final product.
|
|
|
|
RonB
|
| Posted: 07/18/2003, 6:35 AM |
|
"$newincidentno = CCDLookUp("INCIDENT_NO + 1", "incident_master", "", $DBFECSConnection);"
this line will ALWAYS come up with a null value because you do not set a where clause or use max() in the select statement. If you have 100 incident nrs in your table....wich one is cdlookup supposed to get? Also cdlookup function is designed to query the db and return 1 value. your statement would come up with an array of all the incident nrs in your table. How is CCS supposed to know wich nr to use.
It should look something like:
$newincidentno = CCDLookUp("max(INCIDENT_NO) + 1", "incident_master", "", $DBFECSConnection);
"echo ($nfirs_1abcde_record->INCIDENT_NO->GetValue()); NOTE-DATA DISPLAYS HERE"
Again...CCS works with template system ECHO will not write the value to the page. So your data will not display there.
Ron
|
|
|
|
PETERK
|
| Posted: 08/16/2003, 8:34 AM |
|
Well, as I suspected your idea did not work. I now have a specific row being retrieved and updated but still get NULL after I SetValue on a text field:
Which again goes back to my original question, why doesn't the following code work:
global $nfirs_1abcde_record;
global $DBFECSConnection;
echo ("In beforebuildinsert routine");
$newincidentno = CCDLookUp("FIELD_VALUE + 1", "incident_master", "FIELD_NAME = 'INCIDENTNO'", $DBFECSConnection);
$SQL = "UPDATE incident_master SET FIELD_VALUE = ". $newincidentno . " WHERE FIEld_NAME = 'INCIDENTNO'";
$db = new clsDBFECSConnection;
$db->query($SQL);
unset ($db);
$yearcode = date("Y");
while (strlen($newincidentno) < 7 ){
$newincidentno = "0" . $newincidentno;
}
$formattedincidentno = substr($yearcode,3,2). "-" . $newincidentno;
echo ($formattedincidentno);
$nfirs_1abcde_record->INCIDENT_NO->SetValue($formattedincidentno);
echo ($nfirs_1abcde_record->INCIDENT_NO->GetValue());
And by the way, the echo command does work when the error message is displayed. I am only using this for debugging purposes.
|
|
|
|
glerma
|
| Posted: 08/20/2003, 3:41 PM |
|
Peter. Have you tried to move your BeforeBuildInsert code to BeforeInsert or BeforeExecuteInsert?
Maybe the outcome will be different due to the differing stages. Perhaps the code that is supposed to setting your default value is not being called yet prior to the BeforeBuildInsert Event.
Just giving a suggestion.
|
|
|
|
PeterK
|
| Posted: 08/22/2003, 10:13 AM |
|
I don't think that would work since the values are already established and the SQL statement has been generated. That's why I placed the code prior to building the statement.
thanks for the comments.
|
|
|
|
|
