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

how to call stored procedure in vb.net

P: 1
I have stored procedure in sql server as follows

Expand|Select|Wrap|Line Numbers
  1. USE [KHHAPS1]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[sp_transcrt]    Script Date: 09/30/2014 16:22:48 ******/
  4. SET ANSI_NULLS OFF
  5. GO
  6. SET QUOTED_IDENTIFIER OFF
  7. GO
  8. -- Used to Create the HAPSTran Table By copying All Required fields from HAPSMast Table
  9. -- @YrMn  contains the present YearMonth value
  10. create PROCEDURE [dbo].[sp_transcrt]
  11. @YrMn as char(6),
  12. @TypeInst as varchar(6),
  13. @User as varchar(3),
  14. @Cnt as int OUTPUT as
  15. declare @TTableName as Char(11)
  16. select @TTableName='TT' +  @TypeInst+@User
  17. declare @m1 as int
  18. declare @YrMn2 as char(6)
  19. select @m1=convert(int,right(@YrMn,2))
  20. if @m1=1 
  21. begin
  22. declare @y1 as int
  23. select @y1=convert(int,left(@YrMn,4))
  24. select @y1=@y1-1
  25. select @YrMn2=CONVERT(CHAR(4),@y1)+'12'
  26. end
  27. else
  28. begin
  29. select @m1=@m1-1
  30. declare @mm as char(2)
  31. if @m1<10 
  32. select @mm='0' + convert(char(1),@m1)
  33. else
  34. select @mm= convert(char(2),@m1)
  35. select @YrMn2= left(@YrMn,4)+@mm
  36. end
  37. insert  HAPSTran(InstId,EmpCode,Yrmn,DeptCode,UnitCode,HapsCat,Basic,Basic35,SpfAmt,Mhap,Haps3,Short,Incentive,Eligible,EveAct,EveElg,Arrears,Gross,Tds,Deduction,Net,HAPS,Variable,AGP,hapsperc,userid,Datem,timem,Moddate,Moduser) select @TypeInst,EmpCode,@YrMn,DeptCode,UnitCode,HapsCat,Basic,0,PLI,0,0,0,0,0,0,0,0,0,0,0,0,'Y',Variable,AGP,HapsPerc,@User,getdate(),null,null,null
  38. from HAPSMast where active <> 'N' AND HAPSMast.InstId = @TypeInst
  39. UPDATE HAPSTran 
  40. SET basic35 =  ROUND((b.basic+a.AGP) * (cast(a.NPAPerc as float) / CAST(100 AS FLOAT)),0) from HAPSMAST a INNER JOIN HAPSTran b ON A.Empcode = B.Empcode AND A.InstId = B.InstId where b.yrmn = @Yrmn AND A.InstId = @TypeInst AND b.InstId = @TypeInst
  41. UPDATE HAPSTran 
  42. SET WDays =  (select nodays from hapscutoff ) where unitCode='ANS001' and  yrmn = @Yrmn 
  43.  
  44. UPDATE HAPSTran 
  45. SET WDays =  0 where unitCode <> 'ANS001' and  yrmn = @Yrmn 
and I want to call this in vb.net. please send the source code
Sep 30 '14 #1
Share this Question
Share on Google+
1 Reply


Frinavale
Expert Mod 5K+
P: 9,731
Use the SqlConnection class to open a connection to your database and use the SqlCommand Class to execute a command that calls your stored procedure.

For example:
Expand|Select|Wrap|Line Numbers
  1.   Dim connectionString As String = "Server=ipOfSQLServerHost;Database=DatabaseName;User ID=userID; Password=theDatabasePassword;" 'your specific connection string needs to go here
  2.   Dim queryString As String= "sp_transcrt"
  3.   Dim output As String
  4.  
  5.   Using connection As New SqlConnection(connectionString)
  6.  
  7.       Dim command As New SqlCommand(queryString, connection)
  8.       command.CommandType = CommandType.StoredProcedure
  9.  
  10.       command.Parameters.Add("@YrMn", SqlDbType.VarChar, 6)
  11.       command.Parameters.Add("@TypeInst", SqlDbType.VarChar, 6)
  12.       command.Parameters.Add("@User", SqlDbType.VarChar, 6)
  13.       command.Parameters.Add("@Cnt", SqlDbType.VarChar)
  14.       command.Parameters("@Cnt").Direction = ParameterDirection.Output
  15.  
  16.       command.Connection.Open()
  17.  
  18.       Dim numRows As Integer = command.ExecuteNonQuery()
  19.       If IsDBNull(command.Parameters("@Cnt").Value) = False Then
  20.             output = command.Parameters("@Cnt").Value
  21.       End If
  22.  
  23.   End Using 
Oct 2 '14 #2

Post your reply

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