CFQUERY Question

One variation of this issue that I have to deal with around here has to do with MS SQL Views.

We have several databases around here (of course), and most of the important result-sets that we routinely have to deal with are defined as SQL "views."  The trick is that sometimes one database contains a view that refers to what's supposed to be the same view in another database.

If you change the definition of the view in the second database, you have to "refresh the view" in the first.  Otherwise, the columns do not correspond, and the wrong values (sometimes "nonsense" values!) show up in the wrong columns.  Refreshing the views (that refer to the other views) solves the problem.

For whatever reason, the views aren't paying attention to the column names.  The behavior certainly is incorrect, but our DBAs assure me that it's "a known issue." 

So... if you know that you are referring to the correct columns, and you know that you are supplying the correct values in the correct positions, but the values are not winding up in the right places (especially if they seem to be "offset"), then I'd wager that you're dealing with a similar problem.

Corrected message.  Sorry for the duplication.

Questions:

1. What database are you using?

2. Does your database server have a profile or trace feature that allows you to examine the commands that the database is executing?

3. Are you using CFQUERY or CFINSERT?

4. Can you post your CF code?

Got to agree with Adam here, this looks far more like you've labelled your form fields incorrectly or similar. If a database engine was as screwed as this would imply I'm pretty sure it wouldn't be running

First things first: CF doesn't insert anything into your DB.  All it does is pass an string (containing an SQL statement) and any parameters to the DB driver, which passes it on to the DB for the DB to do the work.  If you are passing that exact string (the insert query you cite) to the DB, then it's the DB buggering it up.  CF doesn't touch it.

I suspect you are not telling us the full story here.  I do not believe that you simply have that SQL statement within a <cfquery> tag and the insert is going wrong in the DB.  Can you post the code that you're running which is causing this problem...

--

Adam

INSERT INTO TableA ( first_name, last_name, email ) VALUES ( 'john', 'doe', 'jdoe@test.com' )

How are those the wrong column/value pairs? Is the first_name not 'Joe', etcetera?

If you run the same query outside coldfusion, does the same thing happen?

I've never heard of this in insert statements.  But I have heard of behavior like this in select statements when the "SELECT *" syntax is uses.

What happens there is the when SELECT * is use the JDBC database driver will cache the columns.  Then if the database schema is modified, the columns will no longer match the cached order and strange results can occur.

It is one of the prime reasons that experienced developers will almost always advise against using the SELECT * syntax.