Page 1 of 1

Having trouble with SQL string...

Posted: Thu May 11, 2006 10:09 am
by snowrhythm
i'm trying to add data to an Access table called Locations. when i put this string into the command:

Code: Select all

<?php

$sqlCommand = "INSERT INTO Locations (CompanyID, Country, Address, City, State, Zip, Phone, Fax) VALUES ('$companyID', '$country', '$address', '$city', '$state', '$zip', '$phone', '$fax')";

?>
...i get an error message saying that the LocationID field in the table is not allowed to be null while there is other info being added. so i
looked at the database and saw that someone hadn't set the LocationID field to autonumber....it was just set to number. i guess i
have to create a function that makes non-duplicates for an ID (primary key) field. does anyone already have a function like this that they'd
be willing to pass along? or does anyone know a quick way to do this? any help is appreciated.

phil

Posted: Thu May 11, 2006 10:24 am
by RobertGonzalez
You have to autoincrement the value yourself. The easiest way that I know of is the select the most recent id, ++ it, then use that as your next id. Shouldn't be more than a few lines of code to do it.

Posted: Thu May 11, 2006 11:32 am
by Burrito
if it is set to autoincrement, then you dont' need to do anything with it. Just don't list it in your fields to insert. LocationID sounds to me like it should NOT be autoincremented though (based on the field name). If that's the case, then just change it back to a number.

Re: Having trouble with SQL string...

Posted: Thu May 11, 2006 12:00 pm
by RobertGonzalez
snowrhythm wrote:so i looked at the database and saw that someone hadn't set the LocationID field to autonumber....it was just set to number.
His access table id field is not set to autonumber. And access will not allow you to change it afterward. So to get the same results as autoincrement, he would have to create the process himself.

I would guess that location_id is the primary key, id field of the table 'Locations'.

Posted: Thu May 11, 2006 12:48 pm
by snowrhythm
that's correct, LocationID is the primary key of the Locations table and it is NOT set to autoNumber. i'll try and explain the situation a little more...(get ready, it's pretty nasty):

i'm making a temporary CRM (customer relations management) program. because the one that my company is using isn't working right.
the previous CRM (the one that doesn't work right) was made in VB.NET/ASP.NET. this program allows you to create new locations for companies
that we deal with should they open a new plant or move their company to a new location. and i think that the guy who made it
used something from the windows registry to encrypt the LocationID (in the locations form - on the user end) into something that it could use later.
For instance....the user would add a location, and then this windows registry "function" would take the date and time and convert it into a number that could be used
later (like to see when the entry was made) and then made that the LocationID. pretty smart, really, but a P.I.T.A. for future developers.

so that said...

i like the idea about taking the lastest LocationID and then incrementing off of that to make the new LocationIDs...but couldn't that cause problems
if someone starts using the old CRM again and for some one-in-a-million-chance it tries to duplicate an ID? i might be taking it too far...but i'm new at
coding and i want to be careful about that stuff. lemme know what you think....

Posted: Thu May 11, 2006 1:18 pm
by RobertGonzalez
What I would do is create your increment functionality. Since you are a bright coder and not someone who leaves potential crap for the next guy, you think ahead, asking yourself "what if someone goes backward? What then?.". Simple, you develop your increment functionality to account for the potential for locationId collisions, and, checking to see if the value that is about to be added to the DB is already in there, find it is, develop a new and improved locationId that is not currently in the table. Boss pats you on the back, friends take you out for a brew, life is peachy.

Or you could do what I would do... redevelop the whole freakin thing so it works right from the go. 8)

Posted: Thu May 11, 2006 2:29 pm
by snowrhythm
lol, yeah...developing the "whole freakin' thing" is what i might end up doing. right now they just want some temporary relief
so they can work on the database while the real problem is being fixed. i like your idea though, i'm going to use it...and a beer
sounds fantastic.


ps - these boards are great!