Solved

Stored Procedures With a TimeStamp Column

Posted on 2004-09-01
12
2,122 Views
Last Modified: 2013-12-24
Having Problems Inserting Details into a SQL Server 2000 database

Coldfusion Code to do this is :-
<cfstoredproc procedure="AddCand" datasource="IntranetBeta">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="Title" value="Mr" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="FirstName" value="Jonathan" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="MiddleName" value="David" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="LastName" value="smith" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="JobTitle" value="IT Manager" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="Address" value="12 High Street" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="TownCity" value="Belfast" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="Country" value="Scotland" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="PostCode" value="FK2 0JW" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="HomeNo" value="565654656" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="MobileNo" value="04458385867" null="No">          
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="WorkNo" value="0349 4958675" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="EmergencyNo" value="0384 63948576" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_TINYINT" dbvarname="NoticePeriod" value="4" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="MaritalStatus" value="Single" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="Email" value="Jonathan@jarvisking.com" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_DATETIME" dbvarname="DOB" value="10/11/1977" null="NO">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="Gender" value="Male" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="Status" value="Available" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="SalaryFrom" value="20000" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="SalaryTo" value="30000" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_DateTime" dbvarname="RegDate" value="" null="Yes">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="LastUpdatedUser" value="#getauthuser()#" null="No">
<cfprocparam type="In" cfsqltype="cf_sql_timestamp" dbvarname="TimeStamp" value="" null="Yes">
</cfstoredproc>

Error Received

[Macromedia][SQLServer JDBC Driver][SQLServer]Implicit conversion from data type datetime to timestamp is not allowed. Use the CONVERT function to run this query

Stored Procedure in SQL:  -

Use IntranetBeta

GO
ALTER PROC AddCand
      @Title                  char(10) = NULL,
      @FirstName            varchar(100),
      @MiddleName            varchar(100) = NULL,
      @LastName            varchar(100),
      @JobTitle                            varchar(100) = NULL,
      @Address                            varchar(100),
      @TownCity            varchar(100),
      @Country                            varchar(100) = NULL,
      @PostCode            char(10) = NULL,
      @HomeNo                  char(15) = NULL,
      @MobileNo            char(15) = NULL,
      @WorkNo                  char(15) = NULL,
      @EmergencyNo            char(15) = NULL,
      @NoticePeriod            tinyint,
      @MaritalStatus            varchar(20) = NULL,
      @Email                  varchar(100) = NULL,
      @DOB                  smalldatetime = NULL,
      @Gender                  char(6),
      @Status                  char(11),
      @SalaryFrom            int = NULL,
      @SalaryTo                            int = NULL,
      @RegDate                            datetime = NULL,
      @LastUpdatedUser                      varchar(50),
      @TimeStamp            timeStamp
AS
      /* Set RegDate To DateTime as no parameter being passed in from CF */
      If @Regdate Is Null
                Set @RegDate = getdate()

      /* Create the new Record */
      INSERT INTO Candidates
      VALUES
         (
            @Title,
            @FirstName,
            @MiddleName,
            @LastName,
            @JobTitle,
            @Address,
            @TownCity,
            @Country,
            @PostCode,      
            @HomeNo,
            @MobileNo,
            @WorkNo,
            @EmergencyNo,
            @NoticePeriod,
            @MaritalStatus,
            @Email,
            @DOB,
            @Gender,
            @Status,
            @SalaryFrom,
            @SalaryTo,
            @RegDate,
            @LastUpdatedUser,
            @TimeStamp
          )

Also is there any smart way of skipping fields for input into the database or do you have to specify every column in order when create a stored procedure and give it null etc.. ??,

From the above code you can see that I want SQL to do the timestamping for regdate by using GETDATE() but how do you do it with a TIMESTAMP data type without passing anything from the Coldfusion side ??

Thanks for your Help

500 Points for Grabs

Jonny
0
Comment
Question by:jturkington
  • 6
  • 6
12 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 11954687
DO not use the cf_sql_timestamp type as Macromedia has admitted it is buggy.

Instead send in the timestamp as either a date or as a varchar such as :

<cfprocparam type="In" cfsqltype="cf_sql_varchar" dbvarname="TimeStamp" value="#DateFormat(Now(), 'MM/DD/YYYY')# #Timeformat(Now(), 'h:mm tt')#" null="Yes">

Or don't send it in at all and modify your proc:


GO
ALTER PROC AddCand
     @Title               char(10) = NULL,
     @FirstName          varchar(100),
     @MiddleName          varchar(100) = NULL,
     @LastName          varchar(100),
     @JobTitle                          varchar(100) = NULL,
     @Address                          varchar(100),
     @TownCity          varchar(100),
     @Country                          varchar(100) = NULL,
     @PostCode          char(10) = NULL,
     @HomeNo               char(15) = NULL,
     @MobileNo          char(15) = NULL,
     @WorkNo               char(15) = NULL,
     @EmergencyNo          char(15) = NULL,
     @NoticePeriod          tinyint,
     @MaritalStatus          varchar(20) = NULL,
     @Email               varchar(100) = NULL,
     @DOB               smalldatetime = NULL,
     @Gender               char(6),
     @Status               char(11),
     @SalaryFrom          int = NULL,
     @SalaryTo                          int = NULL,
     @RegDate                          datetime = NULL,
     @LastUpdatedUser                     varchar(50),
AS
     /* Set RegDate To DateTime as no parameter being passed in from CF */
     If @Regdate Is Null
              Set @RegDate = getdate()

     /* Create the new Record */
     INSERT INTO Candidates
     VALUES
        (
          @Title,
          @FirstName,
          @MiddleName,
          @LastName,
          @JobTitle,
          @Address,
          @TownCity,
          @Country,
          @PostCode,    
          @HomeNo,
          @MobileNo,
          @WorkNo,
          @EmergencyNo,
          @NoticePeriod,
          @MaritalStatus,
          @Email,
          @DOB,
          @Gender,
          @Status,
          @SalaryFrom,
          @SalaryTo,
          @RegDate,
          @LastUpdatedUser,
          GETDATE()
         )
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11954693
whoops I forgot to take out one extra comma here :

@LastUpdatedUser                     varchar(50),  <!-- this comma should be removed
0
 

Author Comment

by:jturkington
ID: 11954755
Thanks for you quick response

I thought the TimeStamp data type sets itself when a record is inserted or updated and you cant change its value. Basically I am using TIMESTAMP to log when a record was last modified exactly

Thanks

Jonathan
0
 

Author Comment

by:jturkington
ID: 11954814
Tried your recommendation with altering the SPROC but received the following error when altering the SPROC: -

Disallowed implicit conversion from data type datetime to data type timestamp, table 'IntranetBeta.dbo.Candidates', column 'LastUpdated'. Use the CONVERT function to run this query.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11954831
It does, but I was not sure about your column types since you were assigning it in the original code (even though you were trying to assign null)

If it is a true timestamp you should not even need to include the column in the insert at all - and therefore not as a paramenter to the stored proc either.
0
 

Author Comment

by:jturkington
ID: 11954892
Checked my Candidates Table and the TimeStamp Column is setup as the following :-

Column Name                    Data Type             Length      Allow Nulls
LastUpdated                      TimeStamp               8                

Am i missing something here ??

Thanks again

jonny
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 35

Expert Comment

by:mrichmon
ID: 11954893
Try it without specifying the timestamp at all (note in this case you may need to sepcify the column names in the insert such as

INSERT INTO Candidates (Title, FirstName, MidleName, LastName, ....)
     VALUES
        (
          @Title,
          @FirstName,
          @MiddleName,
          @LastName,
          ......
          )
0
 

Author Comment

by:jturkington
ID: 11954980
Removed TimeStamp entries but still receive the following error :-

Server: Msg 213, Level 16, State 4, Procedure AddCand, Line 31
Insert Error: Column name or number of supplied values does not match table definition.


This indicates to me that the stored procedure wants to know about all the columns in the table. In saying that I have a primary key call CANDID (INT,incremantal of 1) which is not specified in the SPROC and is working fine !!


AHHGHH

Something so simple, causing so much hassle !!

Thanks

Jonny
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11955096
Yes if you remove the timestamp entry you will have to specify all of the columns you are assigning to as I mentioned in my previous post.
0
 

Author Comment

by:jturkington
ID: 11955145
????????

I have removed all entries for TIMESTAMP from the CF code and SPROC, i have even moved the TIMESTAMP entry last in my table design made sure all the other Columns are in order according to the table in my SPROC and CF Code , still receiving the above error

Jonny
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 500 total points
ID: 11955231
Not just in order the columns need to be secified in the stored proc when you do not insert all of them (note the primary key entry doesn't count as you never specify this field on an insert)

So try this :

 /* Create the new Record */
     INSERT INTO Candidates
     (
          Title,
          FirstName,
          MiddleName,
          LastName,
          JobTitle,
          Address,
          TownCity,
          Country,
          PostCode,    
          HomeNo,
          MobileNo,
          WorkNo,
          EmergencyNo,
          NoticePeriod,
          MaritalStatus,
          Email,
          DOB,
          Gender,
          Status,
          SalaryFrom,
          SalaryTo,
          RegDate,
          LastUpdatedUse
          )
     VALUES
        (
          @Title,
          @FirstName,
          @MiddleName,
          @LastName,
          @JobTitle,
          @Address,
          @TownCity,
          @Country,
          @PostCode,    
          @HomeNo,
          @MobileNo,
          @WorkNo,
          @EmergencyNo,
          @NoticePeriod,
          @MaritalStatus,
          @Email,
          @DOB,
          @Gender,
          @Status,
          @SalaryFrom,
          @SalaryTo,
          @RegDate,
          @LastUpdatedUser
         )
0
 

Author Comment

by:jturkington
ID: 11957046
Oh I feel like a right tit !!

I see what what you mean by specifying the columns before entering the Values

Thanks for all your help mrichmon

The 500 Points are yours !!

Jonny
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now