Error converting data type varchar to bigint.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Error converting data type varchar to bigint.

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2011-01-31 : 14:07:33

My Table Structure
RegNo Varchar(50)
IDNo BigInt

SQL PROCEDURE


CREATE PROCEDURE [dbo].[UpdatePins]
(
@RegNo Varchar (50),
@IDNo VarChar(Max)
)
AS 	
BEGIN
Update ScratchTable Set RegNo = @RegNo Where IDNo in (@IDNo)	
END

ASP.CODE


 Dim RsT As New SqlCommand("UpdatePins", DBConn)
        RsT.CommandType = CommandType.StoredProcedure
        RsT.Parameters.Add(New SqlParameter("@RegNo", Data.SqlDbType.VarChar, 50)).Value = RNo
        RsT.Parameters.Add(New SqlParameter("@IDNo", Data.SqlDbType.VarChar)).Value = PinDets
        RsT.ExecuteNonQuery()

In My ASP Code I got large no of pins which i set like
'(1,2,3,4,5,6,7,8)'
BUT I GOT ERROR Error converting data type varchar to bigint.

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-31 : 14:32:21

You should really be using a Table Valued Parameter to pass in a set of data to your stored procedure.

Some other less desirable options would be to do a LIKE hack (possible poor performance). for example:

Update ScratchTable Set RegNo = @RegNo Where ',' + @IDNo + ',' LIKE '%,' + IDNo ',%'

Another option is to use a Split/Parse function to parse the delmimited string into a table variable, temptable or directly from table-valued function.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 14:36:56

Because the variable @IDNo is a string of '(1,2,3,4,5,6,7,8)'

And that string is not a comma separated values list like you are trying to do.

You need to split the string or use dynamic SQL.

CREATE PROCEDURE [dbo].[UpdatePins]
(
@RegNo Varchar (50),
@IDNo VarChar(Max)
)
AS 	

BEGIN
Declare @SQL varchar(max)
Select @SQL = 'Update ScratchTable Set RegNo = ' + @RegNo + ' Where IDNo in ' + @IDNo + ''
Exec (@SQL)

END


GO

Declare @SQL varchar(max)
Declare @RegNo varchar(50)
Declare @IDNo varchar(50)
SET @Regno= 'xxxxxxx'
SET @IDNo = '(1,2,3,4,5,6,7,8)'

Select @SQL = 'Update ScratchTable Set RegNo = ' + @RegNo + ' Where IDNo in ' + @IDNo + ''
Print @SQL

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources