Skip navigation
Currently Being Moderated

SQL Server not processing @@Identity?

Mar 12, 2012 8:42 AM

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:

 
Replies
  • Currently Being Moderated
    Mar 12, 2012 9:15 AM   in reply to sakonnetweb

    That's a very specific error message.  It tells you exactly what the problem is.

     

    Maybe that column was an autoincrement field in access and is something else in sql server.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 12, 2012 11:12 AM   in reply to sakonnetweb

    sakonnetweb - I noticed your queries are not using cfqueryparam. SQL Server is vulnerable to sql injection in ways that Access is not. Be sure to add cfqueryparam to all of your cfquery's as soon as possible.

     

     

    <cfquery datasource="#Request.BaseDSN#" name="get_bid_ID">
    SELECT @@Identity AS new_bid_ID
    </cfquery>
    </cftransaction>

     

     

    As an aside, now that you are running SQL Server you can take advantage of cfquery's result attribute to grab the new identity value ie #theResultName.IDENTITYCOL#

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points