Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 29th, 2006, 05:21 PM
Authorized User
 
Join Date: Jun 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default The conversion of the varchar value '2110000000000

Hi this is Cindy Priya..

This is my VB Code trying to execute a SP called "CAMS_GetNextStudentId"

Dim varNewStudentID as Variant

dbcommand.CommandType = adCmdStoredProc
dbcommand.CommandText = "CAMS_GetNextStudentId"
dbcommand.Parameters.Append _
                    dbcommand.CreateParameter("varNewStudentID", adBigInt, adParamOutput)
dbcommand.ActiveConnection = conn
dbcommand.Execute

Here is my actual Stored procedure. When i execute this from Sql server it just works fine but when i run this SP from VB(above code) it gives an error..

"The conversion of the varchar value '21100000000000111' overflowed an int column. Maximum integer value exceeded"

CREATE Procedure [dbo].CAMS_GetNextStudentID
    @Value varchar(20) OUTPUT
AS

SET NOCOUNT ON
Begin Transaction
Declare @NextID as bigInt

if exists(Select StudentIdSeq From CAMSConfig)
    begin
        Select @NextID = CAST(StudentIDSeq as bigInt) from CAMSConfig With(Tablockx)
        Update CAMSConfig set StudentIDSeq = Cast(@NextID + 1 as VarChar(20))
        Set @Value = cast((@NextID) as varchar(20))
    end

Commit

Return @Value
GO
Any idea what might caused the problem

 
Old June 30th, 2006, 12:29 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dim varNewStudentID as String

VbScript tries to convert the returned string into a datatype that it think fits, since parameter type is of type integer.
And since there are all digits, VbScript tries to convert to the biggest integer variable it can, INT. No can do...
 
Old June 30th, 2006, 02:45 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

To be fair a variable of type Long is bigger than Integer but even that only goes to 2,147,483,647 (2^31) so it's still not big enough.

--

Joe (Microsoft MVP - XML)
 
Old June 30th, 2006, 03:26 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You're right Joe. I was thinking of Long, but wrote the SQL equivialent.

Using ASP.NET 2.0 could make a difference. Old Long (signed 32 bit) is now signed 64 bit, old Integer (signed 16 bit) is now signed 32 bit.

 
Old June 30th, 2006, 08:30 AM
Authorized User
 
Join Date: Jun 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

So whats the solution here.. is this not possible to execute SP through Visual Basic or do i need to declare varNewStudentID as String instead of variant. Let me know if you guys think this can be solved.

Thanks
Cindy!

 
Old June 30th, 2006, 09:42 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

You don't need to change anything in the stored proc. You need to change the variable type in the VB code to be a string.

--

Joe (Microsoft MVP - XML)
 
Old June 30th, 2006, 10:01 AM
Authorized User
 
Join Date: Jun 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm not sure if i can do that because look at my VB Code
Dim varNewStudentID as String

dbcommand.CommandType = adCmdStoredProc
dbcommand.CommandText = "CAMS_GetNextStudentId"
dbcommand.Parameters.Append _
dbcommand.CreateParameter("varNewStudentID", adBigInt, adParamOutput)
dbcommand.ActiveConnection = conn
dbcommand.Execute

But outparameter has adBigInt not String, I have tried several different types like adVarChar or adbigvarchar nothing worked and i get an error
"Parameter object is improperly defined. Inconsistent or incomplete information was provided."

output variable from SP is varchar(20) so i'm pretty sure declaring varNewStudentID as Variant should have worked but since the return value is more than 12 chars/digits its not working.

I tried using 11 chars/digits everything fine.. same exact code that i sent you guys first time... did work perfectly but now only difference is i increased it to varchar(20) every where.. thats where hassle started off.

 
Old July 1st, 2006, 10:26 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tell you one more time. Since output is all digits/numbers VB tries to convert this to a numeric variable subtype for variant datatype. Since there is no integer subtype in VB that can handle this large value, an error like your's occur.

If you insist to keep varNewStudentID as VARIANT, please be sure to set this casted as string when getting back the output parameter from your Stored Procedure, as this

varNewStudentID = CStr(dbcommand.Parameters("varNewStudentID"))





Similar Threads
Thread Thread Starter Forum Replies Last Post
Syntax error converting the varchar value... Earl Hickey ASP.NET 2.0 Basics 5 January 23rd, 2008 10:25 PM
The conversion of the varchar value '2110000000000 vitthala Pro Visual Basic 2005 1 June 30th, 2006 01:37 PM
column with one character - use Char or Varchar crmpicco MySQL 4 January 17th, 2006 07:35 AM
change nvarchar to varchar vincentc SQL Server 2000 3 May 24th, 2005 10:56 PM
VarChar Problem acko SQL Server 2000 3 August 28th, 2003 10:13 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.