Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 2005 > Pro Visual Basic 2005
|
Pro Visual Basic 2005 For advanced Visual Basic coders working in version 2005. Beginning-level questions will be redirected to other forums, including Beginning VB 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro Visual Basic 2005 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 30th, 2006, 10:06 AM
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 and any solution that you can think of.. Let me know


 
Old June 30th, 2006, 01:37 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

I don't see where the actual value is going in the VB. The create parameter method creates a parameter named "varNewStudentID", and gives it a parameter type that is adBigInt to allocate storage size, and is identified as coming from the procedure rather than going to the procedure.

But when you execute the procedure, how do you obtain the result in the VB? Is it in the dbcommand.Parameters collection?

The data type (for storage) that you specify as adBigInt: The storage area will be under VB's auspices (I believe), so perhaps specifying Long there (an intrinsic VB type rather than something defined in the library of properties and such in the db-conneting software) would give you the necessary headroom. (Then again, it might be a syntax error...)

Why is the ID so big? This sequence has generated 21.1 quadrillion distinct numbers in its time of existence? (Wow! That's a lot of IDs!)

Why are you outputting a varchar instead of outputting the value as a true number?





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 SQL Server 2000 7 July 1st, 2006 10:26 AM
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.