Stored Procedures With a TimeStamp Column

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
Start Free Trial