How to Remove, Delete double records but still have the values intact.

First we get all the double records. In this case I have a table called "test" with 2 columns "tst_id char(26) DEFAULT newid(),tst_name varchar(50)".

Secondly we use the GROUP attribute in the <cfquery> tag to only get SINGLE records. In this query we declare a temporary table (MSSQL - memory only) and we insert the SINGLE records in that table

then we DELETE all the double items and after that insert all the single items back into the original table from the temporary table.

Leaves you with all the double items removed!! BUT BE CAREFULL, this method doesn't take into account that records may have a relation with other tables in the database...

<cfquery>
SELECT  tst_id,tst_name
FROM test
WHERE UPPER(tst_name) IN (
  SELECT UPPER(tst_name) as tst_name
  FROM test
  GROUP BY tst_name
  HAVING (COUNT(UPPER(tst_name)) > 1)
)
ORDER BY tst_name
</cfquery>

<cfsavecontent variable="sqlStmt">
  DECLARE @tmp TABLE (tst_id char(36),tst_name varchar(50))

  <!--- Only insert single items of the double records --->
  <cfoutput query="qDouble" group="tst_name">
    INSERT INTO @tmp VALUES ('#tst_id#','#tst_name#')
  </cfoutput>

  <!--- Delete ALL double records --->
  DELETE
  FROM test
  WHERE UPPER(tst_name) IN (
    SELECT UPPER(tst_name) as tst_name
    FROM test
    GROUP BY tst_name
    HAVING (COUNT(UPPER(tst_name)) > 1)
  )
 
  <!--- Insert the single records back in the database --->
  INSERT INTO test (tst_id,tst_name)
  SELECT tst_id,tst_name FROM @tmp
</cfsavecontent>

<!--- Execute the query --->
<cfquery>
  #PreserveSingleQuotes(sqlStmt)#
</cfquery>

Multiple conditions in JOIN statement

You can put multiple conditions in your LEFT, RIGHT and INNER/OUTER joins if you want.. below a small example that will give you the idea.

SELECT tbl.id, tblo.naam
FROM sometable tbl
  LEFT JOIN someothertable tblo ON tblo.id = tbl.id
   AND tblo.function = tbl.function
ORDER BY ....

Didn't know this one.. could have saved me a lotta query's in the past :-(

Insert with a select

Sometimes you need to update a table from another table and what you can do then is SELECT all data.. loop over it with CF en INSERT the data into the other table....OR.... you can let the database sort it out.

INSERT INTO table1 (column1,column2,column3....)
SELECT column1,
    column2,
    'somevalue' AS column3,
    ....
FROM table2

How do I randomly show a query record?

The easiest way to do this is by the "ORDER BY newid()" statement within your SQL statement. Drawback is that you need SQL server with this.

SELECT TOP 1 *
FROM table
ORDER BY newid()

The query above will give you a randomly choosen record each time.

Another way of doing this is by randomly displaying a record from the queryset you have.. you can do that like this (if you use access or some other database)

<cfquery name="rs" datasource="#dsn#">
  SELECT *
  FROM table
</cfquery>

<cfset displayRow = randRange(1,rs.recordcount)>
<cfoutput query="rs" startrow="#displayRow#" maxrows="1">
  -- show your record
</cfoutput>

How to Add Event Handler on run time

    1.

first declare :

lnk.Click += new System.EventHandler(lnk_Click);
 

lnk--> linkbutton name
 

2. Add Evnet Description 

protected void lnk_Click(object sender, EventArgs e)
    {
        LinkButton lnk = (LinkButton)sender;
       



    }