sc_lookup on Ajax on change event

Scriptcase 9 General
#1

Hi, I have the following code on a Ajax on change event: The city and state fields appear on my form as they should but only the City is being saved to the DB. Any ideas anyone?
Thanks in advance
Michael

//Lookup City and State using info from Zip field
sc_lookup(my_data, “SELECT city, state FROM tblzipcodes WHERE Zip={Zip}”);

{
$city = {my_data[0][0]};
$state = {my_data[0][1]};
}

// Standard Scriptcase Insertion
$insert_table = ‘tblsuppliers’; // Table name
$insert_fields = array( // Field list, add as many as needed
‘City’ => “’” . {City} . “’”,
‘State’ => “’” . {State} . “’”);

#2

Can you show us the sql INSERT or UPDATE statement that you use to save the data to the DB?

#3

Thanks for the reply robydago, I’m using Insert from the toolbar of a single record form. The above code is all I have in the On Change event

#4

View this:
//Lookup City and State using info from Zip field
sc_lookup(my_data, “SELECT city, state FROM tblzipcodes WHERE Zip={Zip}”);
if ({my_data}===false){
sc_error_message(“Error:”.{my_data_erro});
} else {
if (count({my_data})<>0) {
$city = {my_data[0][0]};
$state = {my_data[0][1]};
// Standard Scriptcase Insertion
$insert_table = ‘tblsuppliers’; // Table name
$insert_fields = array( // Field list, add as many as needed
‘City’ => “’” . {City} . “’”,
‘State’ => “’” . {State} . “’”);
} else {
sc_error_message(“Record not found”);
}

#5

Thanks oscar, this code didn’t work at all, gave me a http 500 error

#6

you should change the php settings to show the errors
change code with this:
View this:
//Lookup City and State using info from Zip field
sc_lookup(my_data, “SELECT city, state FROM tblzipcodes WHERE Zip={Zip}”);
if ({my_data}===false){
sc_error_message(“Error:”.{my_data_erro});
} else {
if (count({my_data})<>0) {
$city = {my_data[0][0]};
$state = {my_data[0][1]};
// Standard Scriptcase Insertion
$insert_table = ‘tblsuppliers’; // Table name
$insert_fields = array( // Field list, add as many as needed
‘City’ => “’” . {City} . “’”,
‘State’ => “’” . {State} . “’”);
} else {
sc_error_message(“Record not found”);
}
}

#7

I don’t get how your code is supposed to add or change anything in any table: where is the insert or update SQL statement?

You just declare two variables ($insert_table and $insert_fields) but you do nothing with them.

This is the code snippet generated by SC for “[SIZE=12px]Insert a record on another table”[/SIZE].
As you can see, once the variables are declared, they are used to create the $insert_sql statement variable that it is then executed via the sc_exec_sql() macro.

/**
 * Insert a record on another table
 */

// SQL statement parameters
$insert_table  = 'my_table';      // Table name
$insert_fields = array(   // Field list, add as many as needed
     'field_1' => "'new_value_field_1'",
     'field_2' => "'new_value_field_2'",
 );

// Insert record
$insert_sql = 'INSERT INTO ' . $insert_table
    . ' ('   . implode(', ', array_keys($insert_fields))   . ')'
    . ' VALUES ('    . implode(', ', array_values($insert_fields)) . ')';

sc_exec_sql($insert_sql);

But since you are using the code in the onChange event maybe you need the snippet for “[SIZE=12px]Update another table”[/SIZE]?
I’m not sure I understand what you are trying to do (and how)…

/**
 * Update a record on another table
 */

// SQL statement parameters
$update_table  = 'my_table';      // Table name
$update_where  = "field_3 = 'condition'"; // Where clause
$update_fields = array(   // Field list, add as many as needed
     "field_1 = 'new_value_field_1'",
     "field_2 = 'new_value_field_2'",
 );

// Update record
$update_sql = 'UPDATE ' . $update_table
    . ' SET '   . implode(', ', $update_fields)
    . ' WHERE ' . $update_where;
sc_exec_sql($update_sql);
#8

robydago, I see your point. Let me clarify what I have: My single record form has the fields:
SupplierID, Name, Address, City, State, Zip, Telephone
I’m using the Toolbar button Insert to do the insert to db
which, on its own, works fine, a new record is added to the db as it should be.

The problem begins when I try to lookup the City and State fields from another table by using the Ajax on change event (zip). I don’t understand how the City is added but the State isn’t
I see what you say about not having an insert or update statement, but doesn’t the Toolbar button Insert do this for me? It seems to - except for the State field.
​I appreciate you help and patience

#9

OK maybe I’m getting somewhere…I just recreated the form in its simplest state, without looking up the City and State and it works. Now I’ll start to add the extras and see if I can find where it fails. I’ll report back my progress. Thanks robydago and oscar

#10

Solved!! I had made the State field a “Disabled Field” in update/edit mode thinking I was preventing the end user from screwing up, but guess what? I screwed up
Thanks to oscar and robydago