Solved

Intense ColdFusion Insert or Update Database Query.

Posted on 2003-11-11
18
1,279 Views
Last Modified: 2013-12-24
Let me start by writing the database I have that consist of 5 tables.  

tblDept
DeptID            AutoNumber           Primary Key
Dept               Text                      
--------------------------------------------------------
tblLicense

LicenseID        AutoNumber           PrimaryKey
LicenseType    Text
---------------------------------------------------------
tblSoft
SoftID              AutoNumber           PrimaryKey
Title                 Text
---------------------------------------------------------
tblCust
CustID             AutoNumber          Primarykey
Fname             Text
Lname              Text
Phone              Number
Email               Text
----------------------------------------------------------
tblJunction
SoftID
DeptID
LicenseID
CustID
<!---not sure if I should have a primarykey for this one------>
----------------------------------------------------------

What happens is, my department will have a web page that will query the tblDept table and it will post all of the departments that we support.

Now from that page with all the departments in it, they will be able to click on a dept name and it will take them to a dept page and show all of the information from each table.  Such as Customer Contact Info, list of software that deparment has licensed, what type of license it is and how many licenses they have purchased.


From here, I added a link called "ADD SOFTWARE TO LIST".  When they click on this it takes them to a FORM with the DEPT LISTED ABOVE that they are adding software for. And there is a form they will have to Add the Title, LicenseType and Number of licenses owned.


I GOT THIS MUCH DONE (ABOVE)

What I need help with?

As you can see above, I have this info I need to collect and put it into three different tables and  once it's added, I need to take those PRIMARYKEY ID's and add them to the table called tblJunction correctly.

That's a lot of SQL for me and I need some help.  There are a number of ways I can do this but I feel a little overwhelmed right now.  

I tried to normalize the database as much as possible, but trying to keep this as simple as possible.  

Can anyone help?
0
Comment
Question by:fixx17
  • 8
  • 4
  • 4
  • +1
18 Comments
 
LVL 2

Expert Comment

by:jonnygo55
ID: 9725374
shouldnt be too bad...

first of all wrap all of the insert queries in a cftransaction tag...
next use 'Set nocount on' before each insert statement and 'Select @@identity as thisTableNameID Set nocount off'
which you can then pass to the insert of tblJunction...

<cftransaction>
   <cfquery name="insertDept"...>
           Set nocount on
           insert into tblDept ....
           Select @@identity as tblDeptID Set nocount off
   </cfquery>
    ...do the same for the other two...
    <cfquery name="insertDept"...>
          insert into tblJunction
          (DeptID,LicenseID,CustID)
          values
          (#tblDeptID#,#tblLisenceID#,tblCustID#)
      </cfquery>
</cftransaction>
0
 
LVL 1

Author Comment

by:fixx17
ID: 9725401
<cftransaction>

Never heard of this one.

Could you explain what this does, I am lost when it comes to nocount or Select@@identitiy.

0
 
LVL 2

Expert Comment

by:jonnygo55
ID: 9725484
cftransaction will insure that all of the queries wrapped within are properly executed before the changes are committed...if one fails than changes to all are rolled back even if it was executed before the one that fails...

the other code(select @@ identity...) returns the auto incremented number your insert puts in the table to allow you to reference it in your code...

the portion after as you can reference as a cold fusion variable...
thus Select @@identity as tblDeptID
can be referenced in the rest of your cold fusion page after the insert as #tblDeptID#

0
 
LVL 10

Expert Comment

by:Mause
ID: 9725720
Hi there

I don't think you need a primarykey in tblJunction because there are only key's in it what makes it unique!

Fixx what DB are you using??
Jonnygo I never heard of Set nocount on/off what does it do and on what DB does it work?
same for @@identity In mysql to get the last inserted id you call the function LAST_INSERT_ID() (select LAST_INSERT_ID() as lastid)

Fixx an other option is to make your own ID width createUUID() (cfset newid = createUUID()>) then you already have the ID and don't have
to get it from the database. This will have to chage your database structure a little bit
An uuid creates a Universally Unique Identifier and is a 35-character string representation of a unique 128-bit integer.
so your ID must be a char(38) (format: xxxxxxxx-xxxx-xxxx-xxxxxxxxxxxxxxxx (8-4-4-16))
But this is only an other option.

Mause
0
 
LVL 1

Author Comment

by:fixx17
ID: 9725918
But the tblDept, as you can see above, will not be updated but  only referenced. I just need to make sure that it grabs the right DeptID out from the page, and inserts that DeptID number into tblJunction.

I will have to read up more on CFTRANACTION and get back to you.

0
 
LVL 1

Author Comment

by:fixx17
ID: 9725951
OH,

BTW, I am using an MS ACCESS database.

I know most will cringe, but our department is small and we only support 69 departments at this time.  We are just trying to keep departments legit on software licensing issues.

We had a software audit performed a month ago and we got slammed hard. $50,000.00 worth for just one department.  We have an SMS controller that can pull the info from all our network computers.  Then we are going to use this website to figure out how many computers have a software installed, compared to what is actually licensed.
0
 
LVL 2

Expert Comment

by:jonnygo55
ID: 9726287
ah ok I was falsely assuming sql server database...
in that case I would take mause's suggestion of using uuids...

and I would think of switching to MySQL, which is free and much more scalable...

 
0
 
LVL 1

Author Comment

by:fixx17
ID: 9726530
?

It's free?  Where can I download it.  Is it easier than access?
0
 
LVL 2

Expert Comment

by:jonnygo55
ID: 9726608
According to the website... 'The MySQL database server is available for free under the GNU General Public License (GPL). Commercial licenses are available for users who prefer not to be restricted by the terms of the GPL.'

http://www.mysql.com/downloads/index.html

I've never used it...Mause is it easier to use than Access?  
I am sure it is at least as easy....
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:fixx17
ID: 9726886
I think for purposes for work, I have to stick with access for now.  But I would like to learn SQL Server on my own.

I have a test server at home, so maybe I could try it there.

But I will try to work on this more tomorrow at work.

Mause, I will look into what you said.  Plus I think I can still use CFREGISTER from what I am reading.

0
 
LVL 11

Expert Comment

by:hart
ID: 9728596
just a query

how do u show software and licenses when there are no relationship between tblDept, tblLicense and tblSoft..

don't u think there should be a relationship between Department and software
and a relation ship between software and license.

i am not clear how u show softwares corresponding to a particular department

tblDept
DeptID            AutoNumber           Primary Key
Dept               Text                      
--------------------------------------------------------
tblLicense

LicenseID        AutoNumber           PrimaryKey
LicenseType    Text
---------------------------------------------------------
tblSoft
SoftID              AutoNumber           PrimaryKey
Title                 Text
---------------------------------------------------------
tblCust
CustID             AutoNumber          Primarykey
Fname             Text
Lname              Text
Phone              Number
Email               Text
----------------------------------------------------------
tblJunction
SoftID
DeptID
LicenseID
CustID


Regards
Hart
0
 
LVL 11

Expert Comment

by:hart
ID: 9728629
if u get a relationship then all u have to save is the software id and the custmer id.

in the junction table

Regards
Hart
0
 
LVL 10

Expert Comment

by:Mause
ID: 9729032
mysql is a server not a tool like access but there are many tools that can handle mysql.

I gues you have the DeptID passed to the form (like: <a href="form.cfm?DeptID=#DeptID#">ADD SOFTWARE TO LIST</A>)
then use a hidden field to submit the DeptID and in the action page insert the software and license get the id
and insert all in table tblJunction

I do not know if there is a variable or function in access to get the last inserted ID then you must do
something like select max(id) from tblSoft or something

hope this makes it all clear
0
 
LVL 1

Author Comment

by:fixx17
ID: 9763997
OK, I've changed the way I am going to do this with MS ACCESS.

This is what I've done

Here is part of my database.

tblSoft
SoftID                 AutoNumber         PrimaryKey
Title

tblLicense
LicenseID              Autonumber          Primary Key
LicenseType

tlbJunction
SoftID
LicenseID
CustID
DeptID

I've decided on my form, is that I want to query my tables and post what I have in my drop down list in CFSELECT.  This will help normalize my database.

Here is what I have.

<!----------This querys my software license type------------------->
<cfquery name="querysofttype" datasource="softtrack">
SELECT * FROM tblLicense ORDER BY LicenseID
</cfquery>
<!---------This query's my software titles------------------------------->
<cfquery name="querysofttitle" datasource="softtrack">
SELECT * FROM tblSoft ORDER BY SoftID
</cfquery>

Here is my form

<cfform name="Addsoft" action="updatedept.cfm" method="post">
    <table width="100%"  border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td width="7%">&nbsp;</td>
        <td width="11%">Software Title: </td>
        <td width="21%"><cfselect name="SoftTitle" required="yes" message="Please Enter A Software Title." query="querysofttitle" value="Title"></cfselect></td>
        <td width="11%">License Type: </td>
        <td width="15%"><cfselect name="LicenseType" required="yes"message="Please Select A License Type" query="querysofttype" value="LicenseType">
                       </cfselect></td>
        <td width="17%">Number of Licenses</td>
        <td width="18%"><cfinput name="Count" type="text" size="3" required="yes" message="Please Enter How Many Licenses" id="Count"></td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
       <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td colspan="2">&nbsp;</td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td><input type="submit" name="Submit" value="Update"></td>
        <td><input type="reset" name="Submit2" value="Reset"></td>
        <td>&nbsp;</td>
        <td colspan="2">&nbsp;</td>
      </tr>
    </table></cfform>


Sorry for the table mess, but you can see that it pulls the list out of my database and puts it in the drop down list out of my database.  This I do like, then that way all I need to do is when this is submitted, it takes the SoftID and LicenseID and takes that number and adds it to tblJunction columns that I have.

Would anyone know how I could do this last part when people click submit?
0
 
LVL 1

Author Comment

by:fixx17
ID: 9765121
Ok, I needed to add a primary key to tblJunction because I could not get cfinsert to put valule in the database with out it.

This is what I did .

tlbJunction
SoftID             Number
LicenseID        Number
CustID             Number
DeptID            Number
JunctionID        Autonumber      PrimaryKey


Here is the form


<cfform name="Addsoft" action="updatedept.cfm" method="post">
    <table width="100%"  border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td width="7%">&nbsp;</td>
        <td width="11%">Software Title: </td>
        <td width="21%"><cfselect name="Title" required="yes" message="Please Enter A Software Title." query="querysofttitle" value="Title"></cfselect></td>
        <td width="11%">License Type: </td>
        <td width="15%"><cfselect name="LicenseType" required="yes"message="Please Select A License Type" query="querysofttype" value="LicenseType">
                       </cfselect></td>
        <td width="17%">Number of Licenses</td>
        <td width="18%"><cfinput name="HowMany" type="text" size="3" required="yes" message="Please Enter How Many Licenses" id="Count"></td>
      </tr>
      <tr>

<!----------This query carries over the DeptID into a hidden field--------------------------------------------------->

<cfquery name="getid" datasource="softtrack">
            SELECT DeptID
            FROM tblDept
            WHERE DeptID=<cfqueryparam cfsqltype="CF_SQL_INTEGER"
       value="#url.DeptID#">
            </cfquery>
        <td><cfoutput query="getid"><input name="DeptID" type="hidden" value="#DeptID#"></cfoutput></td>
        <td>&nbsp;</td>
       <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td colspan="2">&nbsp;</td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td><input type="submit" name="Submit" value="Update"></td>
        <td><input type="reset" name="Submit2" value="Reset"></td>
        <td>&nbsp;</td>
        <td colspan="2">&nbsp;</td>
      </tr>
    </table></cfform>



What it does right, it adds Number of Licenses to the HowMany column in the tblJunction, and it add the DeptID in the tblJunction in the DeptID column.

Now as you can see above, I am using CFSELECT for two fields.

LicenseType and Title

This querys the database and adds the licensetype and title to the drop down list.

When they choose from the list that is queried from the database, I need to to also grab the SoftID and LicenseID and add it to tblJunction somehow.

Any ideas?

I can get it to query
0
 
LVL 11

Accepted Solution

by:
hart earned 500 total points
ID: 9768938
thats simple all u have to do is this
<cfform name="Addsoft" action="updatedept.cfm" method="post">
    <table width="100%"  border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td width="7%">&nbsp;</td>
        <td width="11%">Software Title: </td>
        <td width="21%">
          <cfselect name="Title" required="yes" message="Please Enter A Software Title.">
                <cfoutput query="querysofttitle">
                <option value="#SoftID#">#Title#</option>
                 </cfoutput>
          </cfselect></td>
        <td width="11%">License Type: </td>
        <td width="15%">
            <cfselect name="LicenseType" required="yes"message="Please Select A License Type">     <cfoutput query="querysofttype">
                <option value="#LicenseID#">#LicenseType#</option>
                 </cfoutput>
            </cfselect>


Regards
Hart
        </td>
        <td width="17%">Number of Licenses</td>
        <td width="18%"><cfinput name="HowMany" type="text" size="3" required="yes" message="Please Enter How Many Licenses" id="Count"></td>
      </tr>
      <tr>
0
 
LVL 1

Author Comment

by:fixx17
ID: 9774512
Thanks, did not mean to overlook your input there.

I appreciate the help.  I feel like such a novice.

:)
0
 
LVL 11

Expert Comment

by:hart
ID: 9776393
glad to be of some help :-)
0

Featured Post

Superior storage. Superior surveillance.

WD Purple drives are built for 24/7, always-on, high-definition security systems. With support for up to 8 hard drives and 32 cameras, WD Purple drives are optimized for surveillance.

Join & Write a Comment

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now