Intense ColdFusion Insert or Update Database Query.

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>

<cftransaction>

Never heard of this one.

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

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#

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

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.

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.

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...

 

?

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

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....

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.

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

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

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

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?

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

Thanks, did not mean to overlook your input there.

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

:)

glad to be of some help :-)