ColdFusion Muse

Why You Should Join AA (Access Anonymous)

Mark Kruger September 22, 2006 1:43 PM Coldfusion & Databases, Coldfusion Troubleshooting Comments (1)

I'm an Access hater too - so the idea of giving tips on it galls me just a bit. Recently, however, I had the opportunity to help a fellow with his Access driven Coldfusion site. This poor soul had moved his Coldfusion site from to to save a few bucks. Saving money on hosting is almost never worth it in my opinion - but I won't go there for this post. After much heartache he got the Access DSN installed using the godaddy control panel. But, uh oh... the new Access driver is the one with Unicode support (at least that's what I surmise without talking to the perky but useless godaddy support). That means that lots of things are now broken.

Read More
  • Share:

More CF and DTS Troubleshooting

Mark Kruger August 25, 2006 3:55 PM MS SQL Server, Coldfusion & Databases Comments (2)

For those of you that have made use of my blog post on DTS and Coldfusion and the subsequent blog on Troubleshooting DTS and Coldfusion, I have a 2 more useful tips. One has to do with a way to trap errors on the server and get information about what's going on on the client - the second tip has to do with protocol selection and conflict.

Read More
  • Share:

Top and the UNION Query

Mark Kruger August 25, 2006 9:10 AM MS SQL Server, Coldfusion & Databases Comments (7)

A "UNION" query can be very useful. Take sorting for example. If I want to select 2 types of data from the same table using different conditions and order by my conditions union is a great tool. See my previous post on Using UNION in your queries. If you also like using TOP you may run into an unexpected behavior when using UNION. Let's say you had 2 tables, portalUsers and intranetUsers and you wanted to get the most recent entries in each that were unapproved. If you try something like this:

<cfquery name="myquery" datasource="mydsn">
      SELECT    TOP 1 fname, lname,
            'Portal' AS sourceTable,
      FROM    portalUsers
      WHERE   unApproved = 1
      SELECT TOP 1 fname, lname,
            'Intranet' AS sourceTable,
      FROM   intranetUsers
      WHERE   Approved = 0
   ORDER BY dateAdded      
You might expect to get 2 records equal to the most recently added portal user and the most recently added intranet user - right? You could take the first record and you would be good to go. Actually, unless the "portalUsers" table has a clustered index ordered by dateAdded, this is not the case.

Read More
  • Share:

Inserting and Updating in a Single Query Statement

Mark Kruger June 23, 2006 10:20 AM MS SQL Server, Coldfusion & Databases Comments (8)

This is a tip about something I do regularly. No, I'm not talking about forgetting to put down the seat. I'm talking about coding. Let's say you have an application that collects user data in a wizard-like format using a few steps. You allow folks to go back and forth between steps and edit what they've done. How do you handle the insert on that first step without creating duplicates when users return to that step? There are several approaches to this problem:

Read More
  • Share:

Great Tip - Capturing "PRINT" output from T-SQL

When I write stored procedures I often throw in a few print statements to help me understand what is going on. For example, if I have a routine that loops through table A and updates table B based on some conditions, I might include a print statement that said something like PRINT 'Condition Met' or PRINT 'Condition Not Met'. I could also output the values of a column or 2 to indicate why a condition was or was not met. That's a neat trick in Query analyzer and it makes debugging stored procedures easier. When you move the procedure to CF the PRINT output is lost or ignored. That is usually exactly what you want. But what if you did want to see the PRINT output? Is there a Coldfusion way to do id? Thanks to this great tip from Shlomy Gantz you can!

Read More
  • Share:

Multiple Columns in a Sub Select??

Mark Kruger May 15, 2006 11:23 AM Coldfusion & Databases Comments (24)

Selecting more than 1 column in a sub-select is possible. I learned this tip from Jochem van Dieten (who knows more about SQL than anyone I know) on a recent CF-Talk thread. According to Jochem you can use the following Syntax:

<Cfquery ... >
WHERE (x, y) IN
   (SELECT a, b FROM otherTable)
...Now ya gotta admit that's pretty neat!

Note: This syntax will not work on every db server. It will not work on MS SQL server for example. See the notes below for more detail

  • Share:

Getting the Auto Number Field in MS Access

As a follow up to this Previous Post on using the @@Identify Field and it's related function "scope_identity()", it appears that it is possible to do this same thing in Access. Charlie Arehart has posted a great article with some examples titled "Select @@Identity Works in Access. Worth Exploring. Check it out.

  • Share:

You Should Use an Alias for Those Sub Query Tables!

Mark Kruger March 15, 2006 4:04 PM SQL tips, Coldfusion & Databases Comments (4)

Here's a rule of thumb for you. When you are writing a query that includes a sub query always use an alias for the table and qualify the columns - or use the full column name if you are so inclined. If you don't you may very well end up with unexpected results. Consider this query:

Read More
  • Share: