[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1310
  • Last Modified:

Intense ColdFusion Insert or Update Database Query.

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
fixx17
Asked:
fixx17
  • 8
  • 4
  • 4
  • +1
1 Solution
 
jonnygo55Commented:
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
 
fixx17Author Commented:
<cftransaction>

Never heard of this one.

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

0
 
jonnygo55Commented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
MauseCommented:
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
 
fixx17Author Commented:
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
 
fixx17Author Commented:
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
 
jonnygo55Commented:
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
 
fixx17Author Commented:
?

It's free?  Where can I download it.  Is it easier than access?
0
 
jonnygo55Commented:
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
 
fixx17Author Commented:
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
 
hartCommented:
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
 
hartCommented:
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
 
MauseCommented:
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
 
fixx17Author Commented:
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
 
fixx17Author Commented:
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
 
hartCommented:
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
 
fixx17Author Commented:
Thanks, did not mean to overlook your input there.

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

:)
0
 
hartCommented:
glad to be of some help :-)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 8
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now