data convertion with dynamic sql

Hi, All,
I;m new to SQl server and having a problem with dynamic sql within an
procedure.
I got error(the error msg and procedure are below) when passing an smallint
value (@.batch_ID)to the dynamic sql, do I need to conver it to didferent
datatype, how to do it?
THanks a lot.
Here is the error message I got:
**********
0
ST_ccst_code_value
Msg 245, Level 16, State 1, Procedure CR_Update_Stage_tables, Line 23
Conversion failed when converting the nvarchar value 'Update
ST_ccst_code_value Set DTS_BATCH_ID =' to data type smallint.
Here is the procedure:
****
ALTER PROCEDURE [dbo].[CR_Update_Stage_tables]
@.BatchName nvarchar(30)
AS
Declare @.batch_ID smallint
select @.batch_ID = 0
Declare @.SQL VarChar(1000)
Declare @.ST_table nvarchar(30)
exec SP_CR_Get_Batch_ID @.BatchName, @.batch_ID output
print @.batch_ID
select @.ST_table = 'ST_'+ @.BatchName
print @.ST_table
SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ @.batch_ID
Exec (@.SQL)
GOWhen you try to add (concatenate) the value for @.batch_ID to the query
string, SQL tries to implicitly convert the string to an integer, because in
t
has a higher data type precedence than char data types.
Try:
SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ CAST(@.batch_ID
as nvarchar(10))
Exec (@.SQL)
"Jessie" wrote:

> Hi, All,
> I;m new to SQl server and having a problem with dynamic sql within an
> procedure.
> I got error(the error msg and procedure are below) when passing an smallin
t
> value (@.batch_ID)to the dynamic sql, do I need to conver it to didferent
> datatype, how to do it?
> THanks a lot.
> Here is the error message I got:
> **********
> 0
> ST_ccst_code_value
> Msg 245, Level 16, State 1, Procedure CR_Update_Stage_tables, Line 23
> Conversion failed when converting the nvarchar value 'Update
> ST_ccst_code_value Set DTS_BATCH_ID =' to data type smallint.
> Here is the procedure:
> ****
> ALTER PROCEDURE [dbo].[CR_Update_Stage_tables]
> @.BatchName nvarchar(30)
> AS
> Declare @.batch_ID smallint
> select @.batch_ID = 0
> Declare @.SQL VarChar(1000)
> Declare @.ST_table nvarchar(30)
>
> exec SP_CR_Get_Batch_ID @.BatchName, @.batch_ID output
> print @.batch_ID
> select @.ST_table = 'ST_'+ @.BatchName
> print @.ST_table
> SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ @.batch_ID
> Exec (@.SQL)
> GO

Data Convertion error help

Hi,

I have an error:"*erro while update quantity. Error converting data type nvarchar to int "while i try update data through form page. Does anybody have any idea how can i correct the error??

I didnt try two methods but both given same error and failed update: -

1) Dim sqlcomm As New SqlCommand(sSaveQuote, rConnect)
....

sqlcomm.Parameters.AddWithValue("@.employeeID", sUserID)
sqlcomm.Parameters.AddWithValue("@.quantity", txtquantity.Text)

2)Error converting data type nvarchar to int ??
Dim rConnect As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("CRNS_CustomerConnectionString").ConnectionString)
Dim command As SqlCommand = New SqlCommand("UpdateOrder", rConnect)
command.CommandType = Data.CommandType.StoredProcedure

If OrderInfo.Verified.ToUpper = "True" Or OrderInfo.Verified = 1 Then

command.Parameters.Add("@.Verified", Data.SqlDbType.Bit)
command.Parameters("@.Verified").Value = 1
Else
command.Parameters.Add("@.Verified", Data.SqlDbType.Bit)
command.Parameters("@.Verified").Value = 0
End If

command.Parameters.Add("@.Comment", Data.SqlDbType.VarChar, 100)
command.Parameters("@.Comment").Value = OrderInfo.Comment

command.Parameters.Add("@.ProductID", Data.SqlDbType.Int)
command.Parameters("@.ProductID").Value = OrderInfo.ProductID

command.Parameters.Add("@.OrderDate", Data.SqlDbType.SmallDateTime)
command.Parameters("@.OrderDate").Value = OrderInfo.OrderDate

Cheers:)

Christe

It seems that your column is int type and what you try to send in connot convert to int.|||

Hi,

Thks for kind replied. Do you have any idea or hint, how can i correct the error?? or how i can convert the nvarchar to int before update in dbs??

cheers:)

|||

Hi

You could debug your program to see if the value you pass to parameter is right ,especiallyOrderInfo.ProductID.

Data Convertion Error

I am using SQL server 2005, Visual Web Developer 2005 express (for right now). Can get the stored procedure to run fine if I do not return the CityID.

Stored Procedure

ALTER Procedure [dbo].[WDR_CityAdd1]

(

@.CountryID int,

@.CityName nvarchar(50),

@.InternetAvail bit,

@.FlowersAvail bit,

@.CityID int OUTPUT

)

AS

IF EXISTS(SELECT 'True' FROM city WHERE CityName = @.CityName AND CountryID = @.CountryID)

BEGIN

SELECT

@.CityID = 0

END

ELSE

BEGIN

INSERT INTO City

(

CountryID,

CityName,

InternetAvail,

FlowersAvail

)

VALUES

(

@.CountryID,

@.CityName,

@.InternetAvail,

@.FlowersAvail

)

SELECT

@.CityID = 'CityID' ( I have also tried = @.@.Identity but that never returned anything it is an identity column 1,1)

END

Here is the code on the other end. I have not included all the parameters, but should get a sense of what I am doing wrong.

Dim myCommand As New SqlCommand("WDR_CityAdd1", dbConn)

myCommand.CommandType = CommandType.StoredProcedure

Dim parameterCityName As New SqlParameter("@.CityName", SqlDbType.NVarChar, 50)

parameterCityName.Value = CityName

myCommand.Parameters.Add(parameterCityName)

Dim parameterCityID As New SqlParameter("@.CityID", SqlDbType.Int, 4)

parameterCityID.Direction = ParameterDirection.Output

myCommand.Parameters.Add(parameterCityID)

Try

dbConn.Open()

myCommand.ExecuteNonQuery()

dbConn.Close()

Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)

'AlertMessage = "City Added"

Catch ex As Exception

AlertMessage = ex.ToString

End Try

Here is the error I get. So what am I doing wrong? I figured maybe in the stored procedure. CityID is difined in the table as an int So why is it telling me that it is a varchar when it is defined in the stored procedure, table and code as an int? Am I missing something?

System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'CityID' to data type int. at System.Data.SqlClient.SqlConnection.OnError

Thanks

Jerry

There are a couple of issues:

grbourque wrote:

...

SELECT @.CityID = 'CityID'

...

Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)

...

--> Conversion failed when converting the varchar value 'CityID' to data type int.

In this location,

SELECT @.CityID = 'CityID'

You are attempting to assign the @.CityID (an integer) the string value 'CityID'. That is what caused the error you reported.

I suggest using SCOPE_IDENTITY to capture the IDENTITY value of the last row entered by the current user/session.

SET @.CityID = SCOPE_IDENTITY()

Also, the RETURN value 'should' be an integer, and you are attempting to return a string value.

('Normally', one would use the RETURN value for success/failure reporting.)

Somewhere prior to the RETURN statement, you 'should' assign the parameterCityID value to a previously declared variable, then use that variable in your application.

|||

Arnie

I had tried playing around with the 'cityID' fieldname and was getting different error messages. I have re-written the code with your help and it works. Sometimes this just amazes me. I do appreciate the help. Maybe with time I will be able to help others as I get a handle on this.

Jerry