Solved

Conversion failed when converting the varchar value

Posted on 2012-03-26
5
399 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Brendt Hess
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

719 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