By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

generate & compare id from table in sql storeprocedure

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

Expand|Select|Wrap|Line Numbers
  1.  "select max(centreid) from regionmaster where centreid like '" & intBranchPrefix & "%'", DBConnection, adOpenKeyset, adLockOptimistic
  2.          If rsMain.RecordCount > 0 Then
  3.             intID = Mid(rsMain(0), 3, 13)
  4.         End If
  5.          intCentreID = intBranchPrefix & (intID + 1)
  6.  
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........
Apr 30 '09 #1
Share this Question
Share on Google+
11 Replies


Delerna
Expert 100+
P: 1,134
Expand|Select|Wrap|Line Numbers
  1. create proc GenerateCentreID @intBranchPrefix int
  2. as
  3. declare @MaxCentreID int,@intID varchar(50)
  4. set @MaxCentreID=(select convert(int,max(substring(centreid,3,13)) 
  5.                   from regionmaster 
  6.                   where centreid like @intBranchPrefix%
  7.                   )
  8.  
  9. set @intID=@intBranchPrefix*10000000000000 + @MaxCentreID+1
  10. go
  11.  
I think thats right, you might need to fix some syntax. I haven't tested it
May 1 '09 #2

P: 25
Mr.Delerna..GoodMorning..,
i have got what u wrote but when i executed the proceure ..the error cameIncorrect syntax near ) of select statement
May 1 '09 #3

P: 31
Hi the proc is correct just one closing bracket is missing
(int,max(substring(centreid,3,13)))
I hope u got the ans.
May 1 '09 #4

Delerna
Expert 100+
P: 1,134
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
May 1 '09 #5

Delerna
Expert 100+
P: 1,134
if it is a character type then
Expand|Select|Wrap|Line Numbers
  1. create proc GenerateCentreID @intBranchPrefix varchar(20)
  2. as 
  3. declare @MaxCentreID bigint,@intID varchar(20) 
  4. set @MaxCentreID=(select convert(bigint,max(substring(centreid,3,13))) 
  5.                   from regionmaster 
  6.                   where centreid like @intBranchPrefix+'%'
  7.                   ) 
  8.  
  9. set @intID=@intBranchPrefix + convert(varchar(13),@MaxCentreID+1)
  10. select @intID
  11.  
  12. go 
  13.  
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
May 1 '09 #6

P: 25
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.
May 1 '09 #7

Delerna
Expert 100+
P: 1,134
Then try converting centreid to character in the query.
The like opperator works with strings!

Expand|Select|Wrap|Line Numbers
  1. create proc GenerateCentreID @intBranchPrefix varchar(20) 
  2. as  
  3. declare @MaxCentreID bigint,@intID varchar(20)  
  4. set @MaxCentreID=(select convert(bigint,max(substring(convert(varchar(20),centreid),3,13)))  
  5.                   from regionmaster  
  6.                   where convert(varchar(20),centreid) like @intBranchPrefix+'%' 
  7.                   )  
  8.  
  9. set @intID=@intBranchPrefix + convert(varchar(20),@MaxCentreID+1) 
  10. select @intID 
  11.  
  12. go  
  13.  
May 3 '09 #8

P: 25
thanks Delerna...i got it..
May 4 '09 #9

P: 25
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SaveBranchProcedure()
  2.  
  3. Dim cmd  As New ADODB.Command
  4. Dim param1 As ADODB.Parameter
  5. Dim pm As Integer
  6. Dim i As Integer
  7.  
  8. Set cmd.ActiveConnection = DBConnection
  9.  
  10.      cmd.CommandType = adCmdStoredProc
  11. '     cmd.CommandText = "sp_savebranchmaster"
  12.        cmd.CommandText = "sp_branchonly"
  13.      cmd.Parameters.Append cmd.CreateParameter("BranchPrefix", adNumeric, adParamInput, 13, intBranchPrefix)
  14. '    cmd.Parameters.Append cmd.CreateParameter("centreid", adNumeric, adParamOutput, intID)
  15.     cmd.Parameters.Append cmd.CreateParameter("centretype", adVarChar, adParamInput, 3, "SR")
  16.     cmd.Parameters.Append cmd.CreateParameter("centrename", adVarChar, adParamInput, 60, Val(TxtName))
  17.     cmd.Parameters.Append cmd.CreateParameter("code", adVarChar, adParamInput, 6, Val(TxtCode))
  18.  
  19.     If CmbBusinessType.Text = "Booking" Then
  20.         cmd.Parameters.Append cmd.CreateParameter("BRANCH_BUSINESS_TYPE", adVarChar, adParamInput, 6, "BOOK")
  21.  
  22.     ElseIf CmbBusinessType.Text = "Delivery" Then
  23.         cmd.Parameters.Append cmd.CreateParameter("BRANCH_BUSINESS_TYPE", adVarChar, adParamInput, 6, "DELI")
  24.  
  25.     ElseIf CmbBusinessType.Text = "OTHER" Then '"Booking and Delivery"
  26.         cmd.Parameters.Append cmd.CreateParameter("BRANCH_BUSINESS_TYPE", adVarChar, adParamInput, 6, "BKDL")
  27.  
  28.     ElseIf CmbBusinessType.Text = "Transhipment" Then
  29.         cmd.Parameters.Append cmd.CreateParameter("BRANCH_BUSINESS_TYPE", adVarChar, adParamInput, 6, "TRAN")
  30.     End If
  31.  
  32.     cmd.Parameters.Append cmd.CreateParameter("levelno", adNumeric, adParamInput, 13, 0)
  33.     cmd.Parameters.Append cmd.CreateParameter("indexno", adNumeric, adParamInput, 13, 0)
  34.     cmd.Parameters.Append cmd.CreateParameter("groupno", adNumeric, adParamInput, 13, 0)
  35.  
  36.      cmd.Parameters.Append cmd.CreateParameter("group_node_flag", adVarChar, adParamInput, 1, "A")
  37.  
  38.      If Trim(TxtAddress) = "" Then
  39.         cmd.Parameters.Append cmd.CreateParameter("address", adVarChar, adParamInput, 4, Null)
  40.      Else
  41.         cmd.Parameters.Append cmd.CreateParameter("address", adVarChar, adParamInput, 50, Val(TxtAddress))
  42.     End If
  43.  
  44.      If Trim(txtAdd1) = "" Then
  45.         cmd.Parameters.Append cmd.CreateParameter("address1", adVarChar, adParamInput, 4, Null)
  46.      Else
  47.         cmd.Parameters.Append cmd.CreateParameter("address1", adVarChar, adParamInput, 50, Val(txtAdd1))
  48.     End If
  49.  
  50.      If Trim(txtAdd2) = "" Then
  51.         cmd.Parameters.Append cmd.CreateParameter("address2", adVarChar, adParamInput, 4, Null)
  52.      Else
  53.         cmd.Parameters.Append cmd.CreateParameter("address2", adVarChar, adParamInput, 50, Val(txtAdd2))
  54.     End If
  55.  
  56.        If Trim(TxtPhone) = "" Then
  57.         cmd.Parameters.Append cmd.CreateParameter("phone", adVarChar, adParamInput, 4, Null)
  58.      Else
  59.         cmd.Parameters.Append cmd.CreateParameter("phone", adVarChar, adParamInput, 20, Val(TxtPhone))
  60.     End If
  61.  
  62.     If Trim(TxtFax) = "" Then
  63.         cmd.Parameters.Append cmd.CreateParameter("fax", adVarChar, adParamInput, 4, Null)
  64.      Else
  65.         cmd.Parameters.Append cmd.CreateParameter("fax", adVarChar, adParamInput, 20, Val(TxtFax))
  66.     End If
  67.  
  68.     If Trim(TxtEmail) = "" Then
  69.       cmd.Parameters.Append cmd.CreateParameter("email", adVarChar, adParamInput, 4, Null)
  70.     Else
  71.       cmd.Parameters.Append cmd.CreateParameter("email", adVarChar, adParamInput, 30, Val(TxtEmail))
  72.    End If
  73.  
  74.     If Trim(TxtManager) = "" Then
  75.      cmd.Parameters.Append cmd.CreateParameter("manager", adVarChar, adParamInput, 4, Null)
  76.     Else
  77.      cmd.Parameters.Append cmd.CreateParameter("manager", adVarChar, adParamInput, 30, Val(TxtManager))
  78.     End If
  79.  
  80.     If Trim(TxtMgrPhone) = "" Then
  81.        cmd.Parameters.Append cmd.CreateParameter("MANAGER_PHONE", adVarChar, adParamInput, 4, Null)
  82.     Else
  83.       cmd.Parameters.Append cmd.CreateParameter("MANAGER_PHONE", adVarChar, adParamInput, 20, Val(TxtMgrPhone))
  84.     End If
  85.  
  86.     If Trim(TxtRemarks) = "" Then
  87.       cmd.Parameters.Append cmd.CreateParameter("REMARKS", adVarChar, adParamInput, 4, Null)
  88.     Else
  89.       cmd.Parameters.Append cmd.CreateParameter("REMARKS", adVarChar, adParamInput, 50, Val(TxtRemarks))
  90.     End If
  91.  
  92.     cmd.Parameters.Append cmd.CreateParameter("mainparent", adNumeric, adParamInput, 13, 0)  'frmTreeMaster.sngMainParentKey
  93.  
  94.  
  95.     If ChkAcct.Value = 1 Then
  96.       cmd.Parameters.Append cmd.CreateParameter("acct_y_n", adVarChar, adParamInput, 1, "Y")
  97.     ElseIf ChkAcct.Value = 0 Then
  98.       cmd.Parameters.Append cmd.CreateParameter("acct_y_n", adVarChar, adParamInput, 1, "N")
  99.     End If
  100.  
  101.     cmd.Parameters.Append cmd.CreateParameter("acct_branch", adVarChar, adParamInput, 20, vsgBranch.Text)
  102.  
  103.      cmd.Parameters.Append cmd.CreateParameter("CREATED_BY", adNumeric, adParamInput, 13, g_UserData.UserID)
  104.     cmd.Parameters.Append cmd.CreateParameter("CREATED_DATE", adDate, adParamInput, 13, Format(g_LoginData.ServerDate, "DD-MMM-YY"))
  105.     cmd.Parameters.Append cmd.CreateParameter("delete_flag", adVarChar, adParamInput, 1, "N")
  106.  
  107. cmd.Execute
  108.  
  109.  
  110. End Sub
  111.  
May 4 '09 #10

Delerna
Expert 100+
P: 1,134
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
May 4 '09 #11

P: 25
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
Expand|Select|Wrap|Line Numbers
  1. 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...
May 8 '09 #12

Post your reply

Sign in to post your reply or Sign up for a free account.