DB Structure
dbo.ElectricalContractorInfo
memberID [PK] (int)
company name, etc, etc. (varchar(MAX))
dbo.contractorZip
MemberID [FK_ElectricalContractorInfo.memberID] (int)
contractorZipID [PK] (int)
zipID [FK_dbo.ZipCodes.zipID(numeric(18, 0))
dbo.zipCodes
zipID [PK](int)
zipCode (nvarchar(MAX))
dbo.ZipCodeDatabase (read only, downloaded from web. Contains
42,000 zips on seperate rows.))
zipCode [PK](int)
Latitude (float)
Longitude (float)
Zip_Class (nvarchar(255))
City (nvarchar(255))
Sate_Number (float)
County_Number (float)
OK the consumer goes to my index.cfm page and enters thier
zip code to find a member in thier area. I got most of that worked
out. The problem I'm running in to is the member enrollment stage.
The INSERT part.
Let me start by saying that Member- AZADI is mostly
responsible for the progress that I have made to date but I have
fallen out of communication with him somehow.
My main problem is that I can't seem to make the primary and
foreign keys work nor do I have any idea what I'm doing here. On my
page that inserts the data, I don't know if I have to make a
statement to insert into the PK or FK or where.
The parts on this page that I'm about to show you that make
sense are mostly due to Azadi and the parts that don't- are my
mess.
The contractor goes to
www.electriciansnet.com/city.cfm and
fills out a few lines including their city. When submitted to
contractor.cfm, form.city querys ZipCodeDatabase and returns all of
the matching zips to form.zipCodes (which is not quite good enough
because most contractors have more than 1 city in thier service
area but we'll worry about that later). Anyway, they fill out the
rest of the form and submit to enroll.cfm. enroll.cfm inserts all
of the basic data into electricalContractorInfo. It also loops
through the form.zipCodes and indexes them to dbo.zipCodes. That is
where the problem starts because it did insert the zips where I
told it to before I started redesigning the tables but it inserted
them in a comma delimited format. I thought "GREAT!" But I was
wrong. Back on index.cfm, the only results that would be returned
is the rows that had only 1 zip in them. It did not read the
results between the commas. So, I tore it down and tried changing
the tables to reflec a zipID and contractorZipID. I think you gurus
call that relational database normalization. The problem is, like I
said, I have no idea how to reference these tables in my
enroll.cfm. So as stupid as it may look, I'm going to show it to
you guys so that maybe you can help. Note: whatever changes I need
to make to the DB -I do directly in SQL Studio 2005.
enroll.cfm
<!--- get inserted Member's userID --->
<cfquery name="getMemberID" datasource="#enet#">
SELECT (MAX(memberID)+1) AS memberID FROM
dbo.ElectricalContractorInfo
</cfquery>
<cfquery name="getZipID" datasource="#enet#">
SELECT (MAX(zipID)+1) AS zipID FROM dbo.zipCodes
</cfquery>
<!--- Insert all stats into electricalContractorInfo
--->
<cfquery name="insertData" datasource="#enet#">
INSERT INTO dbo.ElectricalContractorInfo (memberID,
companyName, StreetNumber, City, State, Contact_First,
Contact_Last, Contact_Title, Contact_Phone, Contact_Email,
Contact_URL, CellNumber, NetworkID, Slogan)
VALUES (#getMemberID.memberID#, '#Form.company#',
'#form.address#', '#form.city#', '#form.states#',
'#Form.first_name#', '#form.last_name#', '#form.title#',
'#form.PhoneO#', '#form.email#', '#form.url#',
'#form.cell_phone#', '#form.carrier#', '#slogan#')
</cfquery>
<!--- Tbl_contractorZip--->
<cfquery name="insertZipData" datasource="#enet#">
INSERT INTO dbo.contractor_Zip (MemberID, zipID)
VALUES ('#getMemberID.memberID#', '#getZipID.ZipID#')
</cfquery>
<!--- Tbl_zipCodes--->
<!--- loop through submitted zip codes and insert them
--->
<cfloop index = "ListElement" list =
"#form.zipCodes#"delimiters="#chr(10)##chr(13)#">
<cfquery name="insertZipCodes" datasource="#enet#">
INSERT INTO dbo.zipCodes (contractorZipID)
SELECT CompanyID FROM tblCompany WHERE CompanyID NOT
IN(SELECT CompanyID FROM tblNotes)
INSERT INTO dbo.zipCodes (zipID, zipCode, contractorZipID)
VALUES ('#getZipID.zipID#', '#ListElement#',
'#CreateUUID()#') (just reaching here)
</cfquery>
</cfloop>
Should the memberID, zipID, and contractorZipID all just be
the same number in ascending order i.e. every insert 1,1,1 and
2,2,2 etc,etc,etc.?
Why contractorzipID?
1
Reply
AdChoices