Upsizing Access 97 to SQL server 6.5 Part III

After I recently upsized an Access database to SQL server which powers our quote and order log users were experiencing certain difficulties when inserting and updating quotes and orders. This was down to the syntax I was using in my insert and update statements and the fact that SQl server is a lot stricter at enforcing field formats. I use to use the format below to insert data into my tables.

Code:

<cfquery datasource="MySource" Name="MyQuery">
Insert into Table
MyField = '#Form.MyField#'
</cfquery>

As it turns out using the ‘#MyField#’ format for inserting or updating data means that you are inserting a string of a VarChar field type and when you try to update Integer fields SQL server rejects the update. So you need to remove the single quotation marks to update Integer fields, like shown below:

Code:

<cfquery datasource="MySource" Name="MyQuery">
Insert into Table
MyField = #Form.MyField#
</cfquery>