A question about "error converting data type varchar to int".

When I run below code (in the try...catch... block), I kept getting error message:"error converting data type varchar to int". I checked the database table and the variables in the program, there is no where defined as int type, all are varchar type. So why does this message show up? It's related with the output parameter in stored proc? Thanks a lot for any suggestions!

The simplified code block:

        objCmd = New OleDb.OleDbCommand
        objCmd.CommandText = "dbo.usp_CheckStatus"
        objCmd.Parameters.Add("@status_message", SqlDbType.VarChar)
        objCmd.Parameters("@status_message").Direction = ParameterDirection.Output

        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.Connection = objConn

        Try
            objCmd.ExecuteNonQuery()
        Catch ex As OleDbException '
            MessageBox.Show(ex.Message)
        End Try

The simplified stored proc:

create proc usp_CheckStatus
@status_message varchar(20) out
as
set @status_message = 'abcde'      
return
heyday2004Asked:
Who is Participating?
 
Nandakumar SakthivelTechnology LeadCommented:
Hi,
   Just replace "SqlDbType.VarChar" with "OleDb.OleDbType.VarChar" as specified below

            objCmd.Parameters.Add("@status_message", OleDb.OleDbType.VarChar, 200)
            objCmd.Parameters("@status_message").Direction = ParameterDirection.Output

Thanks,
Nanda
0
 
TimCotteeHead of Software ServicesCommented:
Hi heyday2004,

Can we see the rest of the sp? Does it still happen even with your simplified sp?

Tim Cottee
0
 
aprestoCommented:
Hi heyday2004,

If you run the stored proc command in query analyser do you get the sam error? If so the prob is with proc not code in which case we can move on from there, try it out

Apresto
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
aprestoCommented:
DECLARE @MyVar varchar(20)
exec usp_CheckStatus @MyVar
Print @MyVar

run that in query analyser
0
 
heyday2004Author Commented:
Thanks for the reply. That's the whole stored proc, I've simplified to that extent but still couldn't find out the problem. It's still reporting :"error converting data type varchar to int". I guess it's related with the output parameter? BTW, it runs good in query analyzer. What does your above query mean? I ran it and returns nothing although succeeded, i think that's the right result. Anyway, any further suggestion is really appreciated. Thanks.
0
 
aprestoCommented:
if it printed nothing it means @status_message  isnt being set or returned, the query above, executes the stored proc and prints the output paramater
0
 
heyday2004Author Commented:
Yeah, anywhere is wrong in the program in using the output parameter?  Thanks.
0
 
heyday2004Author Commented:
Thanks a lot. Great help for me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.