SQL Server not processing @@Identity?

[Guest]

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:

[Dan_Bracuk]

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.

[Guest]

Thanks Dan

For future reference, Think I just solved the issue. @@Identity AS new_bid_ID had nothing to do with it.


When one exports Access tables to SQL Server, one needs to:

1) Re-Declare the key column, even though they were keys in Access, and

2) Modify each Key column - Column Properties/Table Designer/Intentity Specification/ set (Is Identity) to yes.

Thanks again

Norman

[-__cfSearching__-]

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#