2. PHPRunner General questions
  3. Insert into new table question
This topic is locked

Insert into new table question

 		7/19/2007 5:07:17 PM
PHPRunner General questions
R
 rcurtin author

Ok I have asked sort of this same question before but using a different function and you all helped me GREATLY!! Now I am having an interesting issue trying to replicate but change the process. I am trying to send data into the main table but also send one field into a second table using beforeadd event.
Default code I am using

[codebox]function BeforeAdd(&$values)

{
// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Save new data in another table ****

global $conn,$strTableName;
$strSQLSave = "INSERT INTO AnotherTable (Field1, Field2) values (";
$strSQLSave .= $values["Field1"].",";

$strSQLSave .= $values["Field2"];
$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case
}[/codebox]
Now my issue is when i use the default code for the "Save new data in another table" function everything works peachy if I am only using 1 field from each table.
ie.
[codebox]function BeforeAdd(&$values)

{
// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Save new data in another table ****

global $conn,$strTableName;
$strSQLSave = "INSERT INTO LDtotals (AISLensDoctor) values (";
$strSQLSave .= $values["LensDoctor"];
$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case
}[/codebox]
So obviously the first thing you notice is I took out one of the fields from the source and one from the destination (to only move 1 field AND THIS WORKS FINE)
now when I try to use the same exact code but add the second field (just like it is defaulted to) I get errors.....
Technical information

Error type 2

Error description odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement., SQL state 37000 in SQLExecDirect

URL 208.49.238.91/blargh/output/AISTotals_add.php?

Error file C:\www\webroot\BLARGH\output\include\dbconnection.php

Error line 56

SQL query insert into [AISTotals]

Solution This is a general error. It occurs when there is an error in event code or in SQL.
Send your SQL or event code along with full error message tosupport@xlinesoft.com.
using this code

[codebox]function BeforeAdd(&$values)

{
// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Save new data in another table ****

global $conn,$strTableName;
$strSQLSave = "INSERT INTO LDtotals (Date, AISLensDoctor) values (";
$strSQLSave .= $values["Date"].",";

$strSQLSave .= $values["Lensdoctor"];
$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case
}
[/codebox]
See how this is strange? maybe I am missing something simple but just wierd to me how it will work when I modify the code but using the exact default format (just changing table info) it messes up.
J
 		Jane 7/20/2007

Hi,
you need to use single quetes arount text and date fields in the SQL query.

Here is a sample:

insert into TableName (ID, NumberField, TextField, DateField) values (1,2,'text','2005-10-10');

Try to add single quotes around Date field in your event:

$strSQLSave = "INSERT INTO LDtotals (Date, AISLensDoctor) values ('";

$strSQLSave .= $values["Date"]."',";

$strSQLSave .= $values["Lensdoctor"];

$strSQLSave .= ")";


Also you can debug your code using echo statement:

$strSQLSave = "INSERT INTO LDtotals (Date, AISLensDoctor) values ('";

$strSQLSave .= $values["Date"]."',";

$strSQLSave .= $values["Lensdoctor"];

$strSQLSave .= ")";

echo $strSQLSave;
R
 		rcurtin author 7/20/2007

Thank you for the reply I threw in the echo and the ' single quotes I still get the error and with the echo code now this is the result. All field/table names are 100% correct spelling, names, and capitilization but still no go. Any thoughts?
-------this is what echos (the date and the 536 is the data I want to copy)---------------------------------------------
INSERT INTO LDtotals (Date, AISLensDoctor) values ('2007-07-21',536)
----------------------------------------------------------------------------------------------------------------------------------

PHP error happened
Technical information

Error type 2

Error description odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement., SQL state 37000 in SQLExecDirect

URL 208.49.238.91/blargh/output/AISTotals_add.php?

Error file C:\www\webroot\BLARGH\output\include\dbconnection.php

Error line 56

SQL query insert into [AISTotals]

Solution This is a general error. It occurs when there is an error in event code or in SQL.
Send your SQL or event code along with full error message tosupport@xlinesoft.com.
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Again this is exact code I am using I pulled it straight from the events page in PHPRunner 4.0
function BeforeAdd(&$values)

{
// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Save new data in another table ****

global $conn,$strTableName;
$strSQLSave = "INSERT INTO LDtotals (Date, AISLensDoctor) values ('";

$strSQLSave .= $values["Date"]."',";

$strSQLSave .= $values["LensDoctor"];

$strSQLSave .= ")";

echo $strSQLSave;

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case
}
R
 		rcurtin author 7/20/2007

Ok so I figured it out.... after adding what you told me to add... I played with it a little bit on my own.... all I had to do was add [] brackets to the field names in the INSERT INTO line
here is code that worked
function BeforeAdd($values)

{
// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Save new data in another table ****

global $conn,$strTableName;
$strSQLSave = "INSERT INTO LDtotals ) values ('";

$strSQLSave .= $values["Date"]."','";

$strSQLSave .= $values["LensDoctor"];

$strSQLSave .= "')";

echo $strSQLSave;

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case
}
J
 		Jane 7/23/2007

I'm glad you were able to resolve it!