Solved

Conversion failed when converting the varchar value

Posted on 2012-03-26
5
397 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access question 3 41
MS Access, hourly employee status 8 40
Null or "" 28 51
VBA Delete sql "Too Few Parameters" 8 19
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

679 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