Solved

Stored Procedures With a TimeStamp Column

Posted on 2004-09-01
12
2,145 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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
 
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

752 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