Error Converting Data Type Nvarchar To Int

[adebuigny]

I am working on a project to build a asp.net support/help desk wizard. I am using stored procedures to handle just about all of my database calls. In one particular place, I am getting a data type conversion error that makes no sense to me. It's telling me that it is having trouble converting an nvarchar data type to int.

 

The Stored Procedure works if I run it manually, but when I run it from code, it gets the error.

 

Here is the relevant code.

 

Stored Procedure

ALTER PROCEDURE dbo.UpdateProbRec
(
 @ShortDesc VarChar(100),
 @LongDesc ntext,
 @ProbIndex int,
 @ImageLink int
)
As

Update tbl_Problem Set str_ProblemDescShort = '@ShortDesc', 
mem_ProblemDescLong = '@LongDesc', int_ImageLink = @ImageLink
Where int_ProblemIndex = @ProbIndex

 

Object Code

 

Public Function Update() As Boolean
       Dim cn As Data.OleDb.OleDbConnection
       Dim cd As Data.OleDb.OleDbCommand


       Dim sConnString2 As String = _
        "Provider=SQLOLEDB.1;" & _
        "User ID=xxxyyyz;" & _
        "Password=pwd;" & _
        "Initial Catalog=SupportWizardSQL;" & _
        "Data Source=MYSERVER;"

       Try
           cn = New Data.OleDb.OleDbConnection(sConnString2)
           cd = New Data.OleDb.OleDbCommand("UpdateProbRec", cn)

           cd.CommandType = CommandType.StoredProcedure

           cd.Parameters.Add("@ProbIndex", int_ProblemIndex)
           cd.Parameters.Add("@ShortDesc", str_ProblemShort)
           cd.Parameters.Add("@LongDesc", str_ProblemLong)
           cd.Parameters.Add("@ImageLink", int_ImageLink)

           cn.Open()
           cd.ExecuteNonQuery()

           cn.Close()
           cd = Nothing
           cn = Nothing

           Update = True

       Catch eExcep As Exception
           MsgBox(eExcep.Message)
           Update = False

       End Try

   End Function

 

When the cd.ExecuteNonQuery command is run, the following error message is generated

 

ERROR CONVERTING DATA TYPE NVARCHAR TO INT

 

Here are the relevant SQL data types (SQL 2000)

 

int_ProblemIndex Int

str_ProblemDescShort VarChar(100)

mem_ProblemDescLong ntext

int_ImageLing Int

 

Oh, and the var names in the parameter add statements are correct, the data fields above are what they are in the SQL table.

 

I have been pounding my head on this one for quite a while. I would appreciate anyone pointing out what I missed.

 

Thanks

 

Art DeBuigny

[Robby]

firstly you should be using SqlClient instead of OleDb.

 

The second argument in your Parameters.Add method should be the SQL data type ie. SqlDbType.Text or SqlDbType.Int etc...

[Robby]

Also, place Option Strict On and Option Explicit On at the top of wach code page.

[adebuigny]

Robby;

 

That did the trick.

 

Thanks for the help.

 

Art