3 Replies Latest reply: Mar 12, 2012 11:12 AM by -==cfSearching==- RSS

    SQL Server not processing @@Identity?

    sakonnetweb

      Greetings  I just imported a fairly large Access DB to SQL Server. 

       

      When a user creates a new bid in the system, I need to return the last Identity to continue onto the next action page using the code.

       

      
      <!--- Insert bid record and pull new_bid_id. Wrap in transaction tag to 
      
      
      preserve integrity --->
      
      
      <cftransaction>
      
      
      
      <cfquery datasource="#Request.BaseDSN#">
      
      
      
      
      INSERT 
      
      
      
      
      INTO 
      Bid  
      
      
      
      
      
      
      (
      
      
      
      
      
      
      
      admin_uuid, ActiveDate, 
      
      
      InActiveDate, extend_date, PreBidDate, prebid_location, 
      
      
      
      
      
      
      
      ActiveTime, InActiveTime, 
      
      
      PreBidTime, prebid_status, ReferenceNumber, location_ID, Title, 
      
      
      
      
      
      
      
      Description, requestor_ID, 
      
      
      Status, FileContents
      
      
      
      
      
      
      ) 
      
      
      
      
      
      
      
      
      
      VALUES  (
      
      
      
      
      
      
      
      '#request.admin_uuid#', 
      
      
      #CreateODBCDate(Now())#, '#Form.InActiveDate#', '#Form.extend_date#',
      
      
      
      
      
      
      
      '#Form.PreBidDate#', 
      
      
      '#Form.prebid_location#', #CreateODBCTime(Now())#, '#InActiveTime#',
      
      
      
      
      
      
      
      '#Form.PreBidTime#', 
      
      
      #Form.prebid_status#, '#Form.ReferenceNumber#', #Form.location_ID#,'#Form.Title#', 
      
      
      
      
      
      
      
      '#Form.Description#', 
      
      
      #Form.requestor_ID#, 1, '#cffile.serverfile#'
      
      
      
      
      
      
      )
      
      
      
      </cfquery>
      
      
      
      <cfquery datasource="#Request.BaseDSN#" name="get_bid_ID">
      
      
      
      
      SELECT
      @@Identity AS new_bid_ID
      
      
      
      </cfquery>
      
      
      </cftransaction>
      
      
      <!--- Set session.new_bid_ID --->
      
      
      <cflock scope="SESSION" type="EXCLUSIVE" timeout="3">
      
      
      
      <cfset session.new_bid_ID = get_bid_ID.new_bid_ID>
      
      
      </cflock>
      
      

       

      I am getting:  "ERROR: Bid was not created!  » Database » Error Executing Database Query. » [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot insert the value NULL into column 'new_bid_ID', table 'Vendor.dbo.Bid'; column does not allow nulls. INSERT fails.   Please click back and retry bid submission. "

       

      My code is: