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

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?