generate & compare id from table in sql storeprocedure

[premprakashbhati]

hello sir,
goodevening....
iam working on vb6.0 and sql2005.

 "select max(centreid) from regionmaster where centreid like '" & intBranchPrefix & "%'", DBConnection, adOpenKeyset, adLockOptimistic
         If rsMain.RecordCount > 0 Then
            intID = Mid(rsMain(0), 3, 13)
        End If
         intCentreID = intBranchPrefix & (intID + 1)

i have to convert the above code command in sql storeprocedure
where i have to generate centreid with branchprefix(ex :99 or 11) which is supplied by the application.i.e , intBranchPrefix. i have to take that branchprefix in storeprocedure and generate the centre id....that is
centerid=branch prefix & (id+!)
plz help me ......
prem........

[Delerna]

[code=sql]
create proc GenerateCentreI D @intBranchPrefi x int
as
declare @MaxCentreID int,@intID varchar(50)
set @MaxCentreID=(s elect convert(int,max (substring(cent reid,3,13))
from regionmaster
where centreid like @intBranchPrefi x%
)

set @intID=@intBran chPrefix*100000 00000000 + @MaxCentreID+1
go
[/code]

I think thats right, you might need to fix some syntax. I haven't tested it

[premprakashbhati]

Mr.Delerna..Goo dMorning..,
i have got what u wrote but when i executed the proceure ..the error cameIncorrect syntax near ) of select statement

[SnehaAgrawal]

Hi the proc is correct just one closing bracket is missing
(int,max(substr ing(centreid,3, 13)))
I hope u got the ans.

[Delerna]

what is the data type of the field centreid in table regionmaster?
I will mock up a table so I can test the code.
I am guessing it is string but I don't want to guess

[Delerna]

if it is a character type then
[code=sql]
create proc GenerateCentreI D @intBranchPrefi x varchar(20)
as
declare @MaxCentreID bigint,@intID varchar(20)
set @MaxCentreID=(s elect convert(bigint, max(substring(c entreid,3,13)))
from regionmaster
where centreid like @intBranchPrefi x+'%'
)

set @intID=@intBran chPrefix + convert(varchar (13),@MaxCentre ID+1)
select @intID

go
[/code]

the error you mention was due to there not being enough closing brackets in the select criteria of the query.
Also the parameter was not the correct type.
Also I forgot the + '%' in the where

[premprakashbhati]

NOTE:datatype of centerid is decimal(13,2) in table...
here came the error when i execute the given proc

Argument data type decimal is invalid for argument 1 of substring function.

[Delerna]

Then try converting centreid to character in the query.
The like opperator works with strings!

[code=sql]
create proc GenerateCentreI D @intBranchPrefi x varchar(20)
as
declare @MaxCentreID bigint,@intID varchar(20)
set @MaxCentreID=(s elect convert(bigint, max(substring(c onvert(varchar( 20),centreid),3 ,13)))
from regionmaster
where convert(varchar (20),centreid) like @intBranchPrefi x+'%'
)

set @intID=@intBran chPrefix + convert(varchar (20),@MaxCentre ID+1)
select @intID

go
[/code]

[premprakashbhati]

thanks Delerna...i got it..

[premprakashbhati]

calling a storeprocedure for CmdSave_Click in vb6 app

hi delerna ...sorry for troubling u again...
when iam calling the store procedure from vb6 application in CmdSave_Click
it is giving Error:The Precision is invalid ...
iam not getting the exact thing/meaning...
so plz help me out...
here is the code:

Private Sub SaveBranchProcedure()

Dim cmd  As New ADODB.Command
Dim param1 As ADODB.Parameter
Dim pm As Integer
Dim i As Integer

Set cmd.ActiveConnection = DBConnection

     cmd.CommandType = adCmdStoredProc
'     cmd.CommandText = "sp_savebranchmaster"
       cmd.CommandText = "sp_branchonly"
     cmd.Parameters.Append cmd.CreateParameter("BranchPrefix", adNumeric, adParamInput, 13, intBranchPrefix)
'    cmd.Parameters.Append cmd.CreateParameter("centreid", adNumeric, adParamOutput, intID)
    cmd.Parameters.Append cmd.CreateParameter("centretype", adVarChar, adParamInput, 3, "SR")
    cmd.Parameters.Append cmd.CreateParameter("centrename", adVarChar, adParamInput, 60, Val(TxtName))
    cmd.Parameters.Append cmd.CreateParameter("code", adVarChar, adParamInput, 6, Val(TxtCode))
    
    If CmbBusinessType.Text = "Booking" Then
        cmd.Parameters.Append cmd.CreateParameter("BRANCH_BUSINESS_TYPE", adVarChar, adParamInput, 6, "BOOK")
        
    ElseIf CmbBusinessType.Text = "Delivery" Then
        cmd.Parameters.Append cmd.CreateParameter("BRANCH_BUSINESS_TYPE", adVarChar, adParamInput, 6, "DELI")
        
    ElseIf CmbBusinessType.Text = "OTHER" Then '"Booking and Delivery"
        cmd.Parameters.Append cmd.CreateParameter("BRANCH_BUSINESS_TYPE", adVarChar, adParamInput, 6, "BKDL")
       
    ElseIf CmbBusinessType.Text = "Transhipment" Then
        cmd.Parameters.Append cmd.CreateParameter("BRANCH_BUSINESS_TYPE", adVarChar, adParamInput, 6, "TRAN")
    End If
    
    cmd.Parameters.Append cmd.CreateParameter("levelno", adNumeric, adParamInput, 13, 0)
    cmd.Parameters.Append cmd.CreateParameter("indexno", adNumeric, adParamInput, 13, 0)
    cmd.Parameters.Append cmd.CreateParameter("groupno", adNumeric, adParamInput, 13, 0)
    
     cmd.Parameters.Append cmd.CreateParameter("group_node_flag", adVarChar, adParamInput, 1, "A")
     
     If Trim(TxtAddress) = "" Then
        cmd.Parameters.Append cmd.CreateParameter("address", adVarChar, adParamInput, 4, Null)
     Else
        cmd.Parameters.Append cmd.CreateParameter("address", adVarChar, adParamInput, 50, Val(TxtAddress))
    End If
    
     If Trim(txtAdd1) = "" Then
        cmd.Parameters.Append cmd.CreateParameter("address1", adVarChar, adParamInput, 4, Null)
     Else
        cmd.Parameters.Append cmd.CreateParameter("address1", adVarChar, adParamInput, 50, Val(txtAdd1))
    End If
    
     If Trim(txtAdd2) = "" Then
        cmd.Parameters.Append cmd.CreateParameter("address2", adVarChar, adParamInput, 4, Null)
     Else
        cmd.Parameters.Append cmd.CreateParameter("address2", adVarChar, adParamInput, 50, Val(txtAdd2))
    End If
    
       If Trim(TxtPhone) = "" Then
        cmd.Parameters.Append cmd.CreateParameter("phone", adVarChar, adParamInput, 4, Null)
     Else
        cmd.Parameters.Append cmd.CreateParameter("phone", adVarChar, adParamInput, 20, Val(TxtPhone))
    End If
    
    If Trim(TxtFax) = "" Then
        cmd.Parameters.Append cmd.CreateParameter("fax", adVarChar, adParamInput, 4, Null)
     Else
        cmd.Parameters.Append cmd.CreateParameter("fax", adVarChar, adParamInput, 20, Val(TxtFax))
    End If
    
    If Trim(TxtEmail) = "" Then
      cmd.Parameters.Append cmd.CreateParameter("email", adVarChar, adParamInput, 4, Null)
    Else
      cmd.Parameters.Append cmd.CreateParameter("email", adVarChar, adParamInput, 30, Val(TxtEmail))
   End If
   
    If Trim(TxtManager) = "" Then
     cmd.Parameters.Append cmd.CreateParameter("manager", adVarChar, adParamInput, 4, Null)
    Else
     cmd.Parameters.Append cmd.CreateParameter("manager", adVarChar, adParamInput, 30, Val(TxtManager))
    End If
    
    If Trim(TxtMgrPhone) = "" Then
       cmd.Parameters.Append cmd.CreateParameter("MANAGER_PHONE", adVarChar, adParamInput, 4, Null)
    Else
      cmd.Parameters.Append cmd.CreateParameter("MANAGER_PHONE", adVarChar, adParamInput, 20, Val(TxtMgrPhone))
    End If
    
    If Trim(TxtRemarks) = "" Then
      cmd.Parameters.Append cmd.CreateParameter("REMARKS", adVarChar, adParamInput, 4, Null)
    Else
      cmd.Parameters.Append cmd.CreateParameter("REMARKS", adVarChar, adParamInput, 50, Val(TxtRemarks))
    End If
    
    cmd.Parameters.Append cmd.CreateParameter("mainparent", adNumeric, adParamInput, 13, 0)  'frmTreeMaster.sngMainParentKey
    
    
    If ChkAcct.Value = 1 Then
      cmd.Parameters.Append cmd.CreateParameter("acct_y_n", adVarChar, adParamInput, 1, "Y")
    ElseIf ChkAcct.Value = 0 Then
      cmd.Parameters.Append cmd.CreateParameter("acct_y_n", adVarChar, adParamInput, 1, "N")
    End If
    
    cmd.Parameters.Append cmd.CreateParameter("acct_branch", adVarChar, adParamInput, 20, vsgBranch.Text)
    
     cmd.Parameters.Append cmd.CreateParameter("CREATED_BY", adNumeric, adParamInput, 13, g_UserData.UserID)
    cmd.Parameters.Append cmd.CreateParameter("CREATED_DATE", adDate, adParamInput, 13, Format(g_LoginData.ServerDate, "DD-MMM-YY"))
    cmd.Parameters.Append cmd.CreateParameter("delete_flag", adVarChar, adParamInput, 1, "N")
    
cmd.Execute
    
    
End Sub

[Delerna]

You haven't identified the line where the error is occuring!
However, this is an SQL error being returned to your app and is therefore probably identifying the cmd.execute line.

SQL server has a datatype called decimal and another called numeric
They have 2 properties called precision and scale.
precision governs the max number of digits that can exist in the value
scale determins the number of digits within the precision that are used for
the decimal part of the value
ie decimal(6,2) is for numbers such as 1236.99 (6 digits total 2 of which are the decimal part)

The error probably means that one of the parameters in your code is decimal or numeric and its precision does not match (probably less than) the referenced property in the SQLserver stored proc that you are calling.

Check all the parameters in your code that they are matching the types in your stored proc

[premprakashbhati]

calling a storeprocedure for CmdSave_Click in vb6 app

hi, Delerna
good evening..
i have in my sql table the field name:CLASS_CODE datatype:numeri c(6,0) and in sql storeprocedure: @CLASS_CODE numeric(6) .and in my vb application iam passing

cmd.Parameters.Append cmd.CreateParameter("CLASS_CODE", adNumeric, adParamInput, 6, Val(txtSpareCode))

but iam getting error :Precision is invalid...
can u plz mention for me where iam wrong...