Solved

Conversion failed when converting the varchar value

Posted on 2012-03-26
5
391 Views
Last Modified: 2012-04-02
Hello
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
    .Execute
    .Parameters.Delete ("pUSI")
    .Parameters.Delete ("pReviewType")
    .Parameters.Delete ("pStatus")
End With
   
cnn.Close
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

AS

--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
begin
      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))
end
0
Comment
Question by:chtullu135
  • 4
5 Comments
 

Author Comment

by:chtullu135
ID: 37769030
Hello,

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.
0
 
LVL 32

Expert Comment

by:bhess1
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:
SELECT 
    Ascii(SUBSTRING(@USI, 3, 1)) as embedded,
    ASCII(RIGHT(@USI, 1)) as appended

Open in new window

0
 

Author Comment

by:chtullu135
ID: 37771734
Hello bhess1,

Below is the result

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

Accepted Solution

by:
chtullu135 earned 0 total points
ID: 37772288
Hello,

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
0
 

Author Closing Comment

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

14 Experts available now in Live!

Get 1:1 Help Now