Solved

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

Posted on 2006-07-18
8
259 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:heyday2004
8 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 17129135
Hi heyday2004,

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

Tim Cottee
0
 
LVL 23

Expert Comment

by:apresto
ID: 17129144
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
 
LVL 23

Expert Comment

by:apresto
ID: 17129148
DECLARE @MyVar varchar(20)
exec usp_CheckStatus @MyVar
Print @MyVar

run that in query analyser
0
 

Author Comment

by:heyday2004
ID: 17129315
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 23

Assisted Solution

by:apresto
apresto earned 50 total points
ID: 17129335
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
 

Author Comment

by:heyday2004
ID: 17129438
Yeah, anywhere is wrong in the program in using the output parameter?  Thanks.
0
 
LVL 6

Accepted Solution

by:
Nandakumar Sakthivel earned 450 total points
ID: 17129478
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
 

Author Comment

by:heyday2004
ID: 17129737
Thanks a lot. Great help for me.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

932 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

20 Experts available now in Live!

Get 1:1 Help Now