Conversion failed when converting the varchar value

Posted on 2012-03-26
Last Modified: 2012-04-02
I wrote the following code to update a table on an sql server 2005 backend.  I am getting an error on the execute line that says error when converting the varchar value '57, 112, 128, 140.....  I've checked the sql server code and it runs fine when I execute it from SSMS and enter the values.  I've also listed the code for that stored procedure.  It looks like the data type are correct, Now strUSI can be very long so I am wondering if that is the problem.  The front end is an access 2007 front-end

Private Function ExecuteRadarStoredProcedure(strUSI As String, strReviewType As String, intStatus As Integer, strProcName As String)
Dim cmd As ADODB.Command
Dim prm  As ADODB.Parameter
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
'Set cnn.ConnectionString = cStrConnectionString
cnn.Open (cStrConnectionString)

Debug.Print strUSI

Set cmd = New ADODB.Command

cmd.CommandTimeout = 0

Set cmd.ActiveConnection = cnn

With cmd
    Set prm = .CreateParameter("pUSI", adVarWChar, adParamInput, 8000, strUSI)
    .Parameters.Append prm
    Set prm = .CreateParameter("pReviewType", adVarChar, adParamInput, 8000, strReviewType)
    .Parameters.Append prm
    Set prm = .CreateParameter("pStatus", adInteger, adParamInput, , intStatus)
    .Parameters.Append prm
    .CommandType = adCmdStoredProc
    .CommandText = strProcName
    .Parameters.Delete ("pUSI")
    .Parameters.Delete ("pReviewType")
    .Parameters.Delete ("pStatus")
End With
Set cnn = Nothing
Set cmd = Nothing

End Function

-----Server Stored Procedure
ALTER Proc [dbo].[spUpdateRadarStatus]
@USI As varchar(Max),
@ReviewType as varchar(max),
@Status as int


--loop for the delimited values:
set nocount on
declare @t varchar(max)
declare @pos int
set @t = @USI
set @t = @t + ','
while len(@t)>0
      Declare @MyUSI varchar(20)
      set @pos = charindex(',', @t)
      --select left(@t,@pos-1)
      select @MyUSI = left(@t,@pos-1)

      UPDATE R
      SET  R.StatusId = @Status,
            R.StatusDate = GETDATE()
      FROM dbo.RadarTest R
      INNER JOIN dbo.RadarReviewType RRT
      ON R.ReviewType = RRT.RadarReviewTypeID
      INNER JOIN [References].dbo.Status RDS
      ON R.StatusID = RDS.StatusID
      WHERE R.USI = @USI AND RRT.RadarReviewTypeName = @ReviewType

      set @t = substring(@t, @pos+1, len(@t))
Question by:chtullu135
  • 4

Author Comment

ID: 37769030

In the stored procedure I am getting the following when I attempt to pass 57,112.  
Msg 245, Level 16, State 1, Procedure spUpdateRadarStatus, Line 22
Conversion failed when converting the varchar value '57,112' to data type int.
LVL 32

Expert Comment

ID: 37769236
Looking at the error message, I believe the values being passed in as commas are not real commas.  Your error indicates that the trailing comma added by your code was located and stripped off, but the middle 'comma' was not located.

To confirm, run this code in your query (using the same parameters you entered in your example above) and report the values:
    Ascii(SUBSTRING(@USI, 3, 1)) as embedded,
    ASCII(RIGHT(@USI, 1)) as appended

Open in new window


Author Comment

ID: 37771734
Hello bhess1,

Below is the result

embedded    appended
----------- -----------
50                51

Accepted Solution

chtullu135 earned 0 total points
ID: 37772288

I found the problem. It was in the where clause
WHERE R.USI = @USI AND RRT.RadarReviewTypeName = @ReviewType
should be
WHERE R.USI = @MyUSI AND RRT.RadarReviewTypeName = @ReviewType

Author Closing Comment

ID: 37795096
I had made a mistake by using the wrong variable in the where clause.  Once I fixed that, everything worked fine

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now