Skip to main content
A
Anonymous
July 18, 2008
Question

CF_SQL_TIMESTAMP

  • July 18, 2008
  • 1 reply
  • 6285 views
I have just upgraded to Coldfusion 8 on Windows Server 2008 (using MS SQL Server 2005) but CF_SQL_TIMESTAMP is causing problems for me.

when I try to add or update an auction in the database I get the following error:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver]Invalid precision value

I have it narrowed down to this piece of code.

Auction_Date=<CFQUERYPARAM VALUE="#DateFormat(Auction_Date, "mm/dd/yyyy")#" CFSQLTYPE="CF_SQL_TIMESTAMP">,

I was using coldfusion mx7 with MS SQL Server 2005 on Windows 2003 server
with the exact same code and setup and everything was OK.
    This topic has been closed for replies.

    1 reply

    D
    Dan_Bracuk
    Inspiring
    July 18, 2008
    quote:
    Originally posted by: harpso
    I have just upgraded to Coldfusion 8 on Windows Server 2008 (using MS SQL Server 2005) but CF_SQL_TIMESTAMP is causing problems for me.

    when I try to add or update an auction in the database I get the following error:

    Error Executing Database Query.
    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver]Invalid precision value

    I have it narrowed down to this piece of code.

    Auction_Date=<CFQUERYPARAM VALUE="#DateFormat(Auction_Date, "mm/dd/yyyy")#" CFSQLTYPE="CF_SQL_TIMESTAMP">,

    I was using coldfusion mx7 with MS SQL Server 2005 on Windows 2003 server
    with the exact same code and setup and everything was OK.

    Dateformat returns a string. If auction_date is a valid date object, use it without the formatting.

    If it makes you feel any better, Cold Fusion has a history of getting less tolerent of "incorrect" code as the version numbers get higher.
    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    July 18, 2008
    1. What is the value of Auction_Date?
    2. Can you post the entire query?
    A
    Anonymous
    July 18, 2008
    the value of Auction_Date is 07/17/2008

    I removed dateformat as shown below but still the same error.
    Auction_Date=<CFQUERYPARAM VALUE="#Auction_Date#" CFSQLTYPE="CF_SQL_TIMESTAMP">,

    I then took out cfqueryparam for the date/time fields and used the query below which of course worked. the value inserted into the database was 17/07/2008 00:00:00 (Auction_Date is a datetime field in SQl Server)

    <CFSET Auction_Date1=Form.Month & "/" & Form.DayNo & "/" & Form.Year>

    <CFSET Auction_Date=DateFormat(Auction_Date1,"mm/dd/yyyy")>

    <!--- Update the Auction --->
    <CFQUERY DATASOURCE="#Application.Datasource#" NAME="UpdateItem">
    UPDATE Auctions
    SET
    ModeID=<CFQUERYPARAM VALUE="#Form.ModeID#" CFSQLTYPE="CF_SQL_INTEGER">,
    ResellerID=<CFQUERYPARAM VALUE="#Session.ResellerID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">,
    BuyerID=<CFQUERYPARAM VALUE="#Form.BuyerID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">,
    Event_Title=<CFQUERYPARAM VALUE="#Form.Event_Title#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">,
    IndustryID=<CFQUERYPARAM VALUE="#Form.IndustryID#" CFSQLTYPE="CF_SQL_INTEGER">,
    TypeID=<CFQUERYPARAM VALUE="#Form.TypeID#" CFSQLTYPE="CF_SQL_INTEGER">,
    Description=<CFQUERYPARAM VALUE="#Form.Description#" CFSQLTYPE="CF_SQL_LONGVARCHAR">,
    Auction_Date='#DateFormat(Auction_Date, "mm/dd/yyyy")#',
    DurationID=<CFQUERYPARAM VALUE="#Form.DurationID#" CFSQLTYPE="CF_SQL_INTEGER">,
    Open_Price=<CFQUERYPARAM VALUE="#Form_Open_Price#" CFSQLTYPE="CF_SQL_NUMERIC">,
    Bid_Decrement=<CFQUERYPARAM VALUE="#Form_Bid_Decrement#" CFSQLTYPE="CF_SQL_NUMERIC">,
    CurrencyID=<CFQUERYPARAM VALUE="#Form.CurrencyID#" CFSQLTYPE="CF_SQL_INTEGER">,
    Suppliers=<CFQUERYPARAM VALUE="#Form.Selected_Suppliers#" CFSQLTYPE="CF_SQL_LONGVARCHAR">
    WHERE Auctions.AuctionID=<CFQUERYPARAM VALUE="#Form.AuctionID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">;
    </CFQUERY>

    the query that doesnt work is:

    <CFSET Auction_Date1=Form.Month & "/" & Form.DayNo & "/" & Form.Year>

    <CFSET Auction_Date=DateFormat(Auction_Date1,"mm/dd/yyyy")>

    <CFQUERY DATASOURCE="#Application.Datasource#" NAME="UpdateItem">
    UPDATE Auctions
    SET
    ModeID=<CFQUERYPARAM VALUE="#Form.ModeID#" CFSQLTYPE="CF_SQL_INTEGER">,
    ResellerID=<CFQUERYPARAM VALUE="#Session.ResellerID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">,
    BuyerID=<CFQUERYPARAM VALUE="#Form.BuyerID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">,
    Event_Title=<CFQUERYPARAM VALUE="#Form.Event_Title#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">,
    IndustryID=<CFQUERYPARAM VALUE="#Form.IndustryID#" CFSQLTYPE="CF_SQL_INTEGER">,
    TypeID=<CFQUERYPARAM VALUE="#Form.TypeID#" CFSQLTYPE="CF_SQL_INTEGER">,
    Description=<CFQUERYPARAM VALUE="#Form.Description#" CFSQLTYPE="CF_SQL_LONGVARCHAR">,
    Auction_Date=<CFQUERYPARAM VALUE="#DateFormat(Auction_Date, "mm/dd/yyyy")#" CFSQLTYPE="CF_SQL_TIMESTAMP">,
    DurationID=<CFQUERYPARAM VALUE="#Form.DurationID#" CFSQLTYPE="CF_SQL_INTEGER">,
    Open_Price=<CFQUERYPARAM VALUE="#Form_Open_Price#" CFSQLTYPE="CF_SQL_NUMERIC">,
    Bid_Decrement=<CFQUERYPARAM VALUE="#Form_Bid_Decrement#" CFSQLTYPE="CF_SQL_NUMERIC">,
    CurrencyID=<CFQUERYPARAM VALUE="#Form.CurrencyID#" CFSQLTYPE="CF_SQL_INTEGER">,
    Suppliers=<CFQUERYPARAM VALUE="#Form.Selected_Suppliers#" CFSQLTYPE="CF_SQL_LONGVARCHAR">
    WHERE Auctions.AuctionID=<CFQUERYPARAM VALUE="#Form.AuctionID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">;
    </CFQUERY>
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices