Warren
|
| Posted: 09/09/2003, 9:08 PM |
|
I inserted some code to stop INSERTs on duplicate keys. The code runs (thank you very much) but fails to trap those pesky dups. Must be syntax, but after .5 day of trying variations, no luck. Perhaps I need a different approach ...sfi_id is the key to the MySQL database....
$this->SQL = "SELECT nick_name FROM pupils WHERE sfi_id = '$this->ToSQL($this->sfi_id->GetValue())'";
if (mysql_num_rows($results) == 1) {
$this->ds->Errors->Clear();
$this->Errors->AddError("An entry for this SFI ID# is already on file.");
return false; }
Welcome to my nightmare.
Nearly Normal Warren
|
|
|
 |
lneisius
|
| Posted: 09/10/2003, 5:16 AM |
|
In the properties manager set the field to unique and CCS will take care of the code for you!
|
|
|
|
Warren
|
| Posted: 09/10/2003, 6:51 AM |
|
Oh, so close on that "Properties" approach.
When I go to the form in design mode I can click on any input field, see the "Properties" for that field and the setting for "Unique" ...
...EXCEPT...
I can't do this for the one field (sfi_id) that I need to set to "Unique=yes". When I click on that field, "Properties" displays the properties for the entire "Record" not the "Field".
It's playin' with me, right?
Nearly Normal Warren
|
|
|
|
lneisius
|
| Posted: 09/10/2003, 9:12 AM |
|
What type of field is it? I tried every type and it seems to work. Is it a label? and not a text type of field? Is this set to autoincrement in the dbase? If this doesn't work then I'm afraid I probably can't help (Kind of new to this myself).
|
|
|
|
Warren
|
| Posted: 09/10/2003, 9:24 AM |
|
It's a regular box on an entry form. The data type is integer. No, not incremented.
"Properties" seems to understand that this is the datbase key and that seems to be blocking changes to the properties.
Warren
|
|
|
|
VBAjedi
|
| Posted: 09/10/2003, 12:45 PM |
|
I'm not sure if you're concatenating your select statement correctly. What about (the first two statements won't be necessary if you are already connected to your dbase):
//if no connection established yet: make a connection with your details
$connection = mysql_connect("localhost" , "root" , "")
or die ("Cannot make the connection");
//connect to the database with our connection details
$db = mysql_selectdb("yourdbname" , $connection)
or die ("Cannot connect to the database");
$sql_query = "SELECT * FROM pupils WHERE sfi_id = " . $VarContainingProposedID
//execute $sql_query and store in $results
$results = mysql_query($sql_query);
if($results) // returns FALSE if query empty or failed
{
// key exists, do stuff
}
Hope that helps, or at least gives you a different approach to fiddle with!
VBAjedi
|
|
|
|
Nearly Normal Warren
|
| Posted: 09/10/2003, 4:48 PM |
|
VBAjedi YOU 'DA MAN!
It's fixed!
You pointed me in the right direction and I fiddled with it until it worked.
The "or die" feature is a real friend. I'll never leave my home page without it.
Here's the code that worked:
===================================
$connection = mysql_connect("localhost" , "root" , "#####") or die ("Cannot make the connection");
$db = mysql_selectdb("students", $connection) or die ("Cannot connect to the database");
$sql_query = "SELECT * FROM pupils WHERE sfi_id = ' . $this->ToSQL($this->sfi_id->GetValue(), $this->sfi_id->DataType)'";
$results = mysql_query($sql_query);
if($results)
{ $this->ds->Errors->Clear();
$this->Errors->AddError("An entry for this SFI ID# is already on file.");
return false;
}
|
|
|
|
Warren
|
| Posted: 09/10/2003, 7:04 PM |
|
Wait a minute ... I spoke too soon.
The new code DID stop duplicates, HOWEVER it also stopped all non-dups as well.
I discovered that $results always contained "Resounce ID #2"
so if($results) was always true.... so I switched to ...
$results = mysql_query($sql_query,$connection) or die ("Report Problem - Database results error");
$num_of_rows = mysql_num_rows($results);
if ($num_of_rows == 1) {
The problem now is that $num_of_rows ALWAYS comes up as 1.
So I'm still looking for an answer...or a hint.. or manybe even something that smells a little bit like a hint.
Nearly Normal Warren
|
|
|
|
lneisius
|
| Posted: 09/11/2003, 5:30 AM |
|
If you inserted the text box from the html toolbox you will not have any properties management for the text box. It shouldn't even show up in the project explorer tree. Maybe if you delete the text box and insert one from the forms toolbox and then set the properties to unique?
|
|
|
|
VBAjedi
|
| Posted: 09/11/2003, 12:48 PM |
|
Warren,
I enjoyed my "YOU DA MAN!" while it lasted. . . let's see about getting it back!
I have successfully used this approach on mySQL dbases to excecute queries and manipulate the recordset stored in $results (including determining the number of rows just like you did), so I know it can be done. When I execute a Select that has no matches, my mysql_num_rows returns 0.
I wonder if it has to do with your Select statement or the data itself. Try using echo to output the final query, then cut/pasting that query into a DB management tool and executing it manually. Does it give you the result you expect? You have to come up with a query that will return either 0 rows or 1 row (assuming duplicates don't already exist in your dbase).
We'll get it yet!
VBAjedi
|
|
|
|
Nearly Normal Warren
|
| Posted: 09/11/2003, 4:05 PM |
|
OK, this time it IS fixed.
Let me say a sincere thank you for all of you who have had the patience to help an old COBOL programmer get with it. I didn't mention that COBOL think, did I.
I finally had the common sense to drop a handful of code into notepad and run test itterations with notepad/FTP/browser. I quickly got it working and then went back to CodeCharge.
The basic solution was that I was giving the WHERE clause (Argument, DataType) when (Arguement) alone worked just fine. A few other changes that may not have been necessary and the final code (and this is the "final" version of the "final" code) looks thus:
==========Final Code===================
$connection = mysql_connect("localhost" , "root" , "#####") or die ("Report DB Down - Cannot make the connection");
$db = mysql_select_db("students", $connection) or die ("Report Problem - Cannot connect to the database");
$Seak_me_id = $this->sfi_id->GetValue();
$sql_query = "SELECT * FROM pupils WHERE sfi_id = ($Seak_me_id )" or die ("Bad Query Statement");
$results = mysql_query($sql_query,$connection) or die ("Report Problem - Database results error");
$num_of_rows = mysql_num_rows($results);
if ($num_of_rows == 1)
{
$this->ds->Errors->Clear();
$this->Errors->AddError("An entry for this SFI ID# is already on file.");
return false;
}
|
|
|
|
