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=branchprefix & (id+!)
plz help me ......
prem........

[Delerna]

create proc GenerateCentreID @intBranchPrefix int
as
declare @MaxCentreID int,@intID varchar(50)
set @MaxCentreID=(select convert(int,max(substring(centreid,3,13)) 
                  from regionmaster 
                  where centreid like @intBranchPrefix%
                  )
 
set @intID=@intBranchPrefix*10000000000000 + @MaxCentreID+1
go
 

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

[premprakashbhati]

Mr.Delerna..GoodMorning..,
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(substring(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

create proc GenerateCentreID @intBranchPrefix varchar(20)
as 
declare @MaxCentreID bigint,@intID varchar(20) 
set @MaxCentreID=(select convert(bigint,max(substring(centreid,3,13))) 
                  from regionmaster 
                  where centreid like @intBranchPrefix+'%'
                  ) 
 
set @intID=@intBranchPrefix + convert(varchar(13),@MaxCentreID+1)
select @intID
 
go 
 

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!

create proc GenerateCentreID @intBranchPrefix varchar(20) 
as  
declare @MaxCentreID bigint,@intID varchar(20)  
set @MaxCentreID=(select convert(bigint,max(substring(convert(varchar(20),centreid),3,13)))  
                  from regionmaster  
                  where convert(varchar(20),centreid) like @intBranchPrefix+'%' 
                  )  
 
set @intID=@intBranchPrefix + convert(varchar(20),@MaxCentreID+1) 
select @intID 
 
go  
 

[premprakashbhati]

thanks Delerna...i got it..

[premprakashbhati]

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]

hi, Delerna
good evening..
i have in my sql table the field name:CLASS_CODE datatype:numeric(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...