[Resolved] You cannot add or change a record because

[Velausanakha]

After some simple code

I'm using ODBC

I have several tables, more than what I'll list here

Person
Address
State
Country

State is a field in Address which is a FK to Abbreviation, the PK in the table State

The issue I have is, you enter data in a form, it searches the tables to see if State or Country exists, if it doesn't it updates the table

When I perform the operation of adding a new person which gets the person's details, address details, etc, it adds to the Person table fine, it never updates the address, state or country table, it gives me the error

You cannot add or change a record because a related record is required in table 'State'.

And points to a line in the Address function, the one where it executes the SQL statement

The trouble I have with that is, the Address function isn't called until after the State and Country functions

Enter_New_State($Abbrev, $State);
Enter_New_Country($Country);
Enter_New_Address($AddressLn1 ,$AddressLn2 ,$Suburb, $State, $Postcode, $Country, $PrefPhone, $AltPhone);

I tried to put some data manually in the table that I knew the address would use, as I'm filling the form out with some dummy data

I filled out the form again, gave me the same error

Yet if I enter all the information into the address, state and country table manually it works. Person all ready have being uploaded by the form

Here's the code to my state function

function Enter_New_State($Abbrev, $State) { 

/* 
First, we create a connection to our ODBC source. This is done by creating 
a connection. Once this is done, we are returned an ODBC connection number. 
We use this number to use the ODBC functions within PHP. 
*/ 

$cnx = odbc_connect( 'project_draft' , 'root', '' ); 
if (!$cnx) { 
Error_handler( "Error in odbc_connect" , $cnx ); 
} 


$cur0= odbc_exec( $cnx, "select * from State where State.abbreviation = '$_POST[abbreviation]'" ); 
$nbrows=0;
while( odbc_fetch_row( $cur0 ) ) { 
$nbrows++; 
$Abbreviation = odbc_result( $cur0, 1 ); } //Get's abbreviation field

if (!isset($cur0)){

// statement to execute

$SQL_Exec_String = "Insert Into State ( Abbreviation, Name) Values ( '$_POST[Abbreviation]', '$_POST[Name]')"; 

// in this connection execute this string

$cur= odbc_exec( $cnx, $SQL_Exec_String ); 
if (!$cur) { 
Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx ); 
}}else{/*do nothing, is that possible maybe exit;*/} 

odbc_close( $cnx); 
} 

Here's the code to my address function

function Enter_New_Address($AddressLn1 ,$AddressLn2 ,$Suburb, $State, $Postcode, $Country, $PrefPhone, $AltPhone)
 {


/*First, we create a connection to our ODBC source. This is done by creating 
a connection. Once this is done, we are returned an ODBC connection number. 
We use this number to use the ODBC functions within PHP. */


$cnx = odbc_connect( 'project_draft' , 'root', '' ); 
if (!$cnx) { 
Error_handler( "Error in odbc_connect" , $cnx ); 
} 

// increments PID for UID

$cur0= odbc_exec( $cnx, "select * from Person" ); 
$nbrows=0;
while( odbc_fetch_row( $cur0 ) ) { 
$nbrows++; 
$PersonID= odbc_result( $cur0, 1 ); }

$cur=odbc_exec( $cnx, "select * from Address");
$rows=0;
while(odbc_fetch_row($cur)){
$rows++;
$AddressID=odbc_result ($cur, 2);}
$AddressID++;

// statement to execute

$SQL_Exec_String = "Insert Into Address ( PersonID, AddressID, AddressLn1, AddressLn2, Suburb, State, Postcode, Country, PrefPhone, AltPhone) Values ( $PersonID, $AddressID, '$_POST[AddressLn1]', '$_POST[AddressLn2]', '$_POST[Suburb]', '$_POST[Abbreviation]', '$_POST[Postcode]','$_POST[Country]', '$_POST[PrefPhone]', '$_POST[AltPhone]')"; 

// in this connection execute this string

$cur= odbc_exec( $cnx, $SQL_Exec_String ); //This is the line it points to
if (!$cur) { 
Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx ); 
} 

odbc_close( $cnx); 
} 

Now I'm not sure if I got PersonID right here as it is meant to get the PersonID out of the PersonID table and put it in the Address Table as the PK is made up of PersonID and AddressID but that's not the issue I'm having at the moment. Every function is similar in it's makeup - originally ripped from a tute.

[Velausanakha]

k, maybe that was information overload

abbreviation primary key in state
state foreign key in address

if I ignore the state and country entries in address, it updates how its supposed to

add state or country - a related record is required in state or country table - it says that even when the record has been manually placed there

I comment everything out except enter state and enter country functions and calling them. It still doesn't work. Can anyone improve upon this code?

function Enter_New_State($Abbrev, $State) {  

/* 
First, we create a connection to our ODBC source. This is done by creating 
a connection. Once this is done, we are returned an ODBC connection number. 
We use this number to use the ODBC functions within PHP. 
*/  

$cnx = odbc_connect( 'project_draft' , 'root', '' );  

if (!$cnx) {  

Error_handler( "Error in odbc_connect" , $cnx );  

}  

//check if state exists

$cur0= odbc_exec( $cnx, "select * from State where State.abbreviation = '$_POST[abbreviation]'" );  

$nbrows=0; 
while( odbc_fetch_row( $cur0 ) ) {  

$nbrows++;  

$Abbreviation = odbc_result( $cur0, 1 ); } //Get's abbreviation field 
$Name = odbc_result($cur0, 2);

//if state doesn't exist, add it

if (!isset($cur0)){ 

// statement to execute 

$SQL_Exec_String = "Insert Into State ( Abbreviation, State) Values ( '$_POST[Abbreviation]', '$_POST[State]')";  

// in this connection execute this string 

$cur= odbc_exec( $cnx, $SQL_Exec_String );  

if (!$cur) {  

Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );  

}}//end while  

odbc_close( $cnx);  

}

[Velausanakha]

function Enter_New_State($Abbrev, $State) { 
/* 
First, we create a connection to our ODBC source. This is done by creating 
a connection. Once this is done, we are returned an ODBC connection number. 
We use this number to use the ODBC functions within PHP. 
*/ 

$cnx = odbc_connect( 'project_draft' , 'root', '' ); 
if (!$cnx) { 
Error_handler( "Error in odbc_connect" , $cnx ); 
} 



$cur0= odbc_exec( $cnx, "select * from State" ); 
$nbrows=0;
while( odbc_fetch_row( $cur0 ) ) { 
$nbrows++; 
$Abbreviation = odbc_result( $cur0, 1 ); 
$Name = odbc_result($cur0, 2); }

// statement to execute

$SQL_Exec_String = "Insert Into State ( Abbreviation, Name) Values ( '$_POST[abbreviation]', '$_POST[State]')"; 

// in this connection execute this string

$cur0= odbc_exec( $cnx, $SQL_Exec_String ); 
if (!$cur0) { 
Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx ); 
} 

odbc_close( $cnx); 
} 

THIS FUNCTION ADDS THE STATE BUT STILL GENERATES THIS ERROR MESSAGE

Warning: SQL error: [Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'State'., SQL state 23000 in SQLExecDirect in c:\apache\apache\htdocs\project\dataaccess.php on line 172
Error in odbc_exec( no cursor returned )

IT REFERS TO THE ADDRESS FUNCTION AFTER THE STATE FUNCTION HAS BEEN CALLED

AND WILL GIVE YOU THE DUPLICATE INDEX ERROR MESG IF U ADD SAME STATE TWICE

---

function Enter_New_State($Abbrev, $State) { 
/* 
First, we create a connection to our ODBC source. This is done by creating 
a connection. Once this is done, we are returned an ODBC connection number. 
We use this number to use the ODBC functions within PHP. 
*/ 

$cnx = odbc_connect( 'project_draft' , 'root', '' ); 
if (!$cnx) { 
Error_handler( "Error in odbc_connect" , $cnx ); 
} 



$cur0= odbc_exec( $cnx, "select State.Abbreviation from State where State.Abbreviation='$_POST[abbreviation]'" ); 
$nbrows=0;
while( odbc_fetch_row( $cur0 ) ) { 
$nbrows++; 
$Abbreviation = odbc_result( $cur0, 1 ); 
$Name = odbc_result($cur0, 2); }

// statement to execute

$SQL_Exec_String = "Insert Into State ( Abbreviation, Name) Values ( '$_POST[abbreviation]', '$_POST[State]')"; 

// in this connection execute this string

$cur0= odbc_exec( $cnx, $SQL_Exec_String ); 
if (!$cur0) { 
Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx ); 
} 

odbc_close( $cnx); 
} 

SAME DEAL AS ABOVE

---

function Enter_New_State($Abbrev, $State) { 
/* 
First, we create a connection to our ODBC source. This is done by creating 
a connection. Once this is done, we are returned an ODBC connection number. 
We use this number to use the ODBC functions within PHP. 
*/ 

$cnx = odbc_connect( 'project_draft' , 'root', '' ); 
if (!$cnx) { 
Error_handler( "Error in odbc_connect" , $cnx ); 
} 



$cur0= odbc_exec( $cnx, "select State.Abbreviation from State where State.Abbreviation='$_POST[abbreviation]'" );  

$nbrows=0;
while( odbc_fetch_row( $cur0 ) ) { 
$nbrows++; 
$Abbreviation = odbc_result( $cur0, 1 ); 
$Name = odbc_result($cur0, 2); }

// statement to execute

$SQL_Exec_String = "Insert Into State ( Abbreviation, Name) Values ( '$_POST[abbreviation]', '$_POST[State]')"; 

// in this connection execute this string


if (!$cur0) { 
$cur0= odbc_exec( $cnx, $SQL_Exec_String ); 
Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx ); 
} 

odbc_close( $cnx); 
} 

DOES ABSOLUTELY NOTHING BUT GENERATES THE ALL READY REPORTED ERROR MESSAGE - I WOULD HAVE THOUGHT THIS VERSION WOULD HAVE WORKED EXACTLY HOW IT WAS MEANT TO

---

THE BIG THING I DON'T GET IS I'VE NOW MANAGED TO GENERATE THE NECESSARY RELATED RECORD IN STATE BEFORE CALLING THE ADDRESS FUNCTION YET IT STILL TELLS ME THE RELATED RECORD IS REQUIRED. WHY?

[Velausanakha]

sort of resolved

diff prob now

inserts address and stuff

but if the values for state and country are the same and the actual address is different, it doesn't add it to the address table

[Velausanakha]

re-writing some sql statements fixed that no prob