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
Solved

Conversion failed when converting the varchar value

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
corrupt Databases 9 65
On Key Down Access 2010 6 41
MS Access VBA Code to Include Library References 4 49
database opened as read only 10 31
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
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.

840 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