Error Executing Database Query

[Bulkis]

I have a website, which is visited by 18.000 unique users a day, who view
almost 900.000 pages As you can see, one user views a lot of pages. These
pages are viewed very quickly so there is a problem with the queries to SQL
database. I get a few hundred such errors a day: Error Executing Database
Query.[Macromedia][SQLServer JDBC Driver][SQLServer]A column has been specified
more than once in the order by list. Columns in the order by list must be
unique. The specific sequence of files included or processed is:
Z:\web\draugas.lt\narys.cfm How to avoid these errors and what to do that all
user requests would be completed. The source code is quite simple, but the
problem is that there are a lot of queries and the data in one page is being
updated, while the other user tries to use that data at the same time.

[blewis]

Can you post the code for the query which is gererating the error?

[Bulkis]

Examples

(1)

<cfquery name="ngrupe" datasource="draugas"
cachedwithin="#createtimespan(0,0,5,0)#">
select narys, vardas, pavarde, amzius, miestas, nuotrauka, nuotraukag,
nuotrauka2, nuotrauka2g, nuotrauka3, nuotrauka3g, telefonas, lytis, iesko, sms,
laiskai, plaikas
from dbo.tblduomenys
where rodyti=1
order by <cfif #rusiuoti# is 1>registruotas2 desc<cfelseif #rusiuoti# is
2>registruotas desc<cfelseif #rusiuoti# is 3>(laiskai+sms) desc, registruotas2
desc</cfif>
</cfquery>

(2)

<cfquery name="laiskai4" datasource="draugas">
select gavejas, viso
from dbo.tbl_laiskai4
</cfquery>

<cfoutput query="laiskai4">

<cfquery name="update" datasource="draugas">
update dbo.tblduomenys
set laiskai=#viso#
where narys=<cfqueryparam value="#gavejas#" cfsqltype="cf_sql_integer">
</cfquery>

</cfoutput>

[Bulkis]

I try this and error fall much at rare intervals. Why cfquery name change that?
<cfquery name='laiskai4' datasource='draugas'> select gavejas, viso from
dbo.tbl_laiskai4 </cfquery> <cfoutput query='laiskai4'> <cfquery
name='update#randrange(100000,999999)#' datasource='draugas'> update
dbo.tblduomenys set laiskai=#viso# where narys=<cfqueryparam value='#gavejas#'
cfsqltype='cf_sql_integer'> </cfquery> </cfoutput>

[toby007]

Hi there I am really stumped on this one so am seeking some assistance. I am
reading from a database and then updating the record. I am coming unstuck when
updating the database because of date fields. I have two date fields a
created_date and a modified date. This is the error message that I am getting:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Syntax error (missing operator) in query expression ''{d
'2005-01-01'}''. And this is the code <CFIF form.contentID> <cfquery
name='updateCMS' datasource='CartDB'> UPDATE CMS SET Title =
'#Trim(FORM.Title)#', Body = '#Trim(FORM.Body)#', Active =
'#FORM.Active#', Created_by = '#FORM.Created_by#',
Created_date = '#CreateODBCDate(FORM.Created_date)#', Modified_by =
'#FORM.Modified_by#', Modified_date =
'#CreateODBCDate(FORM.Modified_date)#' WHERE
ContentID=#val(FORM.ContentID)# </cfquery> </cfif> </cfif> I can email or
post the whole template if someone can have a look.

[toniu27]

Created_date = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#FORM.Created_date#">

Try to use cfqueryparam, it provide both security and performance

[toby007]

Thanks for the reply - it has now resulted in the following error message:
Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC
Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
expression. The error occurred in
C:\Inetpub\wwwroot\beecroftflowers\Admin\Guarantee CMS.cfm: line 25 23 :
Modified_by = '#FORM.Modified_by#', 24 : Modified_date =
<cfqueryparam cfsqltype='cf_sql_timestamp' value='#FORM.Modified_date#'> 25 :
WHERE ContentID=#val(FORM.ContentID)# 26 : </cfquery> 27 : </cfif>
UPDATE CMS SET Title = 'Guarantee Title', Body = 'Beecroft Flower Place and
Gifts guarantee to use only fresh, quality flowers. On our web site we have
provided a page outlining care procedures for your flowers so that you will
obtain maximum enjoyment from your purchase.', Active = 'Yes', Created_by =
'Peter Tanswell', Created_date = (param 1) , Modified_by = 'Peter Tanswell',
Modified_date = (param 2) WHERE ContentID=1 So in essence I am still
struggling on getting this update working. Any other ideas please. Regards

[Tulsa]

Take out the quotes around "#FORM.Modified_date#"

[toniu27]

I guess you should use CF_SQL_DATE instead of CF_SQL_TIMESTAMP.

Check the following available cfsqltype parameter when using cfqueryparam :

SQL type to which the parameter (any type) is bound. ColdFusion supports the
following values, where the last element of the name corresponds to the SQL
data type. Different database systems might support different subsets of this
list. See your DBMS documentation for information on supported parameter types.

CF_SQL_BIGINT
CF_SQL_BIT
CF_SQL_BLOB
CF_SQL_CHAR
CF_SQL_CLOB
CF_SQL_DATE
CF_SQL_DECIMAL
CF_SQL_DOUBLE
CF_SQL_FLOAT
CF_SQL_IDSTAMP
CF_SQL_INTEGER
CF_SQL_LONGVARCHAR
CF_SQL_MONEY
CF_SQL_MONEY4
CF_SQL_NUMERIC
CF_SQL_REAL
CF_SQL_REFCURSOR
CF_SQL_SMALLINT
CF_SQL_TIME
CF_SQL_TIMESTAMP
CF_SQL_TINYINT
CF_SQL_VARCHAR

[heath_edw]

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Syntaxfehler (fehlender Operator) in Abfrageausdruck
'StoryDetails where date>now()'.

how can i fix this?

[Scooby Doobie Doo]

What causes this and how can I fix it? I get this A LOT on my server. The
Error: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]Error
establishing socket. Connection refused: connect This is irritating because
I'm not sure what's causing it, anyone else experience this nightmare and if
so, what did you do about it?

[Tom Jordahl]

Looks like a syntax error in your SQL. What does your query look like?

--
Tom Jordahl
Macromedia Server Development

[heath_edw]

this is my query

<CFQUERY NAME="StoryTable" DATASOURCE="thun">
SELECT Preacher, StoryDate, StoryDetails
FROM agenda_services
</CFQUERY>

what i'm trying to do is show 3 databased information stories at a time,
starting from now()

[Tom Jordahl]

> this is my query

>
> <CFQUERY NAME="StoryTable" DATASOURCE="thun">
> SELECT Preacher, StoryDate, StoryDetails
> FROM agenda_services
> </CFQUERY>

I am confused as the string "StoryDetails where date>now()" does not appear
in this query.

--
Tom Jordahl
Macromedia Server Development

[heath_edw]

this is my current query:

<CFQUERY NAME="StoryTable" DATASOURCE="thun">
SELECT Preacher, StoryDate, StoryDetails
FROM agenda_services
WHERE StoryDate>=now()
</CFQUERY>

'date' wouldn't work, and had to be rewritten 'StoryDate'. now, it works fine,
though i'm having difficulty trying to enforce a limit of only three stories.

[pukkel]

'date' is a reserved word in SQL country. You cannot use it as a column name.
If you insist on using it, you could enclose it in brackets like [date] (but
I'm not sure in Access). What's wrong with your SQL statement is that you are
forgetting to enclose now() in poundsigns. #now()# should work better for you.

[Evan_at_RM]

You can use CF's "MaxRows" parameter, when you work with the data returned
using the <CFOUTPUT> tag.
For instance,
<cfoutput query="StoryTable" maxrows="3">

If you're using Microsoft SQL Server, you can try "TOP" syntax like this to
limit your selection to 3 records. That might be better if you want the
limiting to occur on the SQL Server, if there are millions of rows.
SELECT TOP 3 * Preacher, StoryDate, StoryDetails
FROM agenda_services

Usually, you would also put in an ORDER BY clause so that the 3 records aren't
selected randomly:
SELECT TOP 3 * Preacher, StoryDate, StoryDetails
FROM agenda_services
ORDER BY StoryDate

-- Evan

[heath_edw]

thanks for your help...

[IMCNetworks Webmaste]

I've got a problem when I try to run a query against an access database on
Win2K.

The query if very simple. SELECT * FROM Products WHERE PartNumber LIKE '%5-1%'

It breaks and sends me this error: Error Executing Database Query. The
Microsoft Jet database engine stopped the process because you and another user
are attempting to change the same data at the same time. <br>The error occurred
on line 5.

Here's the real kicker... if I add more to the PartNumber it works just fine
and will return all matching rows. Something LIKE '%5-10101%' works every
single time.

I've tried using the Access Driver with UNICode in CF, Restarting CF, Shuting
Down, Renaming the Database, ODBC, and CF Datasource... but no luck.

What am I missing? Any help will be very greatly appreciated!

[gwgiswebmaster]

if you have high traffic volume on your site, I can see this error coming up
frequently - in which case you may want to consider converting to a SQL DB.
Otherwise, it could be the .ldb file needs to be deleted.