INSERT query error: Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

Question

I am trying to run an insert query, but I am getting the error below. How can I pass the values into my insert statement? The value is coming from an array and the [cNotes] column type is varchar NULL.

Thanks in advance.

Error:

        Error Executing Database Query.  
        [Macromedia][SQLServer JDBC Driver][SQLServer]Implicit conversion from data type varchar    to varbinary is not allowed. Use the CONVERT function to run this query.  

         The error occurred in C:\Inetpub\wwwroot\Components\Assessment.cfc: line 510
       Called from C:\Inetpub\wwwroot\Components\Assessment.cfc: line 440

          508 :                      ,NULL
          509 :                  </cfif>
          510 :                   ,'#arguments.notes#') 
           511 :        </cfquery>
          512 :     </cffunction> 



           --------------------------------------------------------------------------------

            SQL    INSERT INTO AssessmentToolDetail(iAssessmentToolMaster_ID ,iServiceList_ID   ,cRowStartUser_ID ,Service_text ,cNotes) Values(251069 ,3592 ,NULL ,'y ' ,'')   
           DATASOURCE   TIPS4 
             VENDORERRORCODE   257 
            SQLSTATE   HY000 '

CFC function:

<cffunction name="AddService" access="public" returntype="void" output="false">
    <cfargument name="ServiceList" type="Components.ServiceList"  required="true">
    <cfargument name="notes" type="string" required="false" default="">
    <cfargument name="serviceText" type="string" required="false" default="">       

    <cfquery name="AddServiceListQuery" datasource="#variables.dsn#">
        INSERT INTO AssessmentToolDetail (
                iAssessmentToolMaster_ID
                , iServiceList_ID
                , cRowStartUser_ID
                , Service_text
                , cNotes )
        VALUES (
               #variables.id#
                ,#ServiceList.GetId()#              
                <cfif variables.rowStartUserId neq "">
                    , '#variables.rowStartUserId#'
                <cfelse>
                    , NULL
                </cfif>
                <cfif ListFirst( Arguments.serviceText,'_' ) EQ ServiceList.GetId() >
                    , '#Left(ListLast( Arguments.serviceText,'_'),1)# '
                <cfelse>
                    , NULL
                </cfif>
                ,  '#arguments.notes#'
              ) 
    </cfquery>
</cffunction>

Solution

The error is saying that you're trying to insert a string into a column with a binary storage format, and it can't make the conversion implicitly.

Check that the cNotes column in the AssessmentToolDetail table has a datatype of varchar(n) or nvarchar(n) and not varbinary(n).

Comments

[Adam Cameron]

The error message spits out the SQL it was trying to run... it looks fine to me, but what do you get if you run that SQL in SQL Server Management Studio?

[TGR]

Adam - same error : Msg 257, Level 16, State 3, Line 1 Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

[Cᴏʀʏ]

@TGR: That's exactly the error SQL server throws if you try to insert an empty string into a varbinary column. Are you absolutely positive that the datatype on that column is varchar(n) and not varbinary(n)` (i.e. SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AssessmentToolDetail' AND COLUMN_NAME = 'cNotes' should return varchar)?

[TGR]

@Cory i fixed the issue.. Thanks,

[Cᴏʀʏ]

@TGR: What did you determine to be the issue?

[Adam Cameron]

@TGR: yeah, I'm keen to know too. You should add an actual "answer" and mark it as such.