468,505 Members | 1,655 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,505 developers. It's quick & easy.

Web service to call sql stored procedure

I don't know if this is the right part of the forum. But.... I have been working all night trying to create a web service to call a stored procedure in sql server 2008. The stored procedure calls a linked server to a db2 database that accepts 1 integer and returns 6 variables.

The end result would be publish the web service to our sharepoint servers. This is not a problem.

I am just trying to wrap my head around it. I know how to call the stored procedure using vb.net in a desktop app, just having problems transitioning to a web service to call that stored procedure.

Most searching finds trying to call a web service from a stored procedure, but I want to do the other way around, web service to call stored procedure.

Suggestions? Thanks for the help.
May 26 '09 #1
12 30080
750 Expert 512MB
There is no difference in calling a stored procedure from web app or web service or for that matter windows service... The procedure is same .... You need connection string , command object and other regular things....
May 26 '09 #2
well i got the webservice to work.....partially.

I can pass my input variable,but I only get one variable back. I tried a class or a structure, but no go.

My input is an integer (intEmployeeID) and my outputs are string(strFName), string(strLName), integer (intAnnual), integer (intSick) and a couple other.

Suggestions on this?

May 26 '09 #3
750 Expert 512MB
Use SqlParameters with Direction as Output for outparameters

Expand|Select|Wrap|Line Numbers
  1. SqlParameter myOutputParameter = new SqlParameter("@One", SqlDbType.Int);
  2.                 myOutputParameter.Direction = ParameterDirection.Output;
Calling SQL Server stored procedures in ASP.NET

Retrieving Scalar Data from Stored Procedure Scott Mitchell
May 27 '09 #4
9,735 Expert Mod 8TB
You had the right idea using a class to pass the data back.... Why didn't the class work?

What did you attempt to do here?

You should have made a class ...maybe called EmployeeInfo ...that could be used to pass the information back to the client application.

For example (are you using C#?) your class would have looked something like:
Expand|Select|Wrap|Line Numbers
  1. public class EmployeeInfo{
  2.   string _strFName;
  3.   string _strLName;
  4.   integer _annual;
  5.   integer _sickDays;
And your web method would look something like:
Expand|Select|Wrap|Line Numbers
  1. [WebMethod()]
  2. public EmployeeInfo GetEmployeeInfo( (int employeeID))
  3. {
  4.   //This method calls the database and retrieves the employee information.
  5.   //It creates and populates the EmployeeInfo Object that it passes
  6.   //back to the client application
  7. }
The GetEmployeeInfo web method returns an EmployeeInfo Object that contains the multiple output data that you need to pass back to the client application. This data is passed back as an Object, so is easy to use in the client application.

I might be giving you an answer that isn't even relevant to your problem though....

Are you having problems passing the data retrieved by your web service (web method) to the client application?

Or are you having problems retrieving the data from the database?
May 27 '09 #5
thanks for all the help. finavale I attempted the class again, just could not get it to work.

The ultimate goal is deploy this to our sharepoint servers for use with infopath forms. When i try the class, infopath recognizes the class(the fields defined within the class) as query fields not data fields.

But this is what I got.

Expand|Select|Wrap|Line Numbers
  1. <WebMethod(Description:="TEST: leave info")> _
  2.     Public Function LeaveInfo(ByVal intEmployeeID As Integer)
  4.         Dim strFName As String
  5.         Dim strLName As String
  6.         Dim strBnftDate As String
  7.         Dim strEmail As String
  8.         Dim decFam As Decimal
  9.         Dim decAnn As Decimal
  10.         Dim decSick As Decimal
  11.         Dim decOther As Decimal
  12.         Dim intSqlError As Integer
  14.         Dim SQLCon As SqlConnection = New SqlConnection("Connection String")
  15.         Dim SQLCmd As New SqlCommand("Stored Procedure Name", SQLCon)
  16.         SQLCmd.CommandType = CommandType.StoredProcedure
  17.         Dim Parameter0 As SqlParameter = New SqlParameter("@EmID", intEmployeeID)
  18.         Dim Parameter1 As SqlParameter = New SqlParameter("@fname", SqlDbType.VarChar, 15)
  19.         Dim Parameter2 As SqlParameter = New SqlParameter("@lname", SqlDbType.VarChar, 30)
  20.         Dim Parameter3 As SqlParameter = New SqlParameter("@bnftdate", SqlDbType.Char, 10)
  21.         Dim Parameter4 As SqlParameter = New SqlParameter("@email", SqlDbType.VarChar, 50)
  22.         Dim Parameter5 As SqlParameter = New SqlParameter("@fam", SqlDbType.Decimal)
  23.         Dim Parameter6 As SqlParameter = New SqlParameter("@ann", SqlDbType.Decimal)
  24.         Dim Parameter7 As SqlParameter = New SqlParameter("@sick", SqlDbType.Decimal)
  25.         Dim Parameter8 As SqlParameter = New SqlParameter("@oth", SqlDbType.Decimal)
  26.         Dim Parameter9 As SqlParameter = New SqlParameter("@sqlerror", SqlDbType.Decimal)
  28.         Parameter0.Direction = ParameterDirection.Input
  29.         Parameter1.Direction = ParameterDirection.Output
  30.         Parameter2.Direction = ParameterDirection.Output
  31.         Parameter3.Direction = ParameterDirection.Output
  32.         Parameter4.Direction = ParameterDirection.Output
  33.         Parameter5.Direction = ParameterDirection.Output
  34.         Parameter6.Direction = ParameterDirection.Output
  35.         Parameter7.Direction = ParameterDirection.Output
  36.         Parameter8.Direction = ParameterDirection.Output
  37.         Parameter9.Direction = ParameterDirection.Output
  39.         SQLCmd.Parameters.Add(Parameter0)
  40.         SQLCmd.Parameters.Add(Parameter1)
  41.         SQLCmd.Parameters.Add(Parameter2)
  42.         SQLCmd.Parameters.Add(Parameter3)
  43.         SQLCmd.Parameters.Add(Parameter4)
  44.         SQLCmd.Parameters.Add(Parameter5)
  45.         SQLCmd.Parameters.Add(Parameter6)
  46.         SQLCmd.Parameters.Add(Parameter7)
  47.         SQLCmd.Parameters.Add(Parameter8)
  48.         SQLCmd.Parameters.Add(Parameter9)
  50.         SQLCon.Open()
  51.         SQLCmd.ExecuteNonQuery()
  53.         strFName = SQLCmd.Parameters("@fname").Value
  54.         strLName = SQLCmd.Parameters("@lname").Value
  55.         strBnftDate = SQLCmd.Parameters("@bnftdate").Value
  56.         strEmail = SQLCmd.Parameters("@email").Value
  57.         decFam = SQLCmd.Parameters("@fam").Value
  58.         decAnn = SQLCmd.Parameters("@ann").Value
  59.         decSick = SQLCmd.Parameters("@sick").Value
  60.         decOther = SQLCmd.Parameters("@oth").Value
  61.         intSqlError = SQLCmd.Parameters("@sqlerror").Value
  63.         'not working
  64.         'Return EmployeeInfo
I commented out the return as it doesn't work. Connection and getting the data is no problem. As I can concat the returned values into one string or variable using something like strReturnValue = strFName & strLName&....... then Return strReturnValue .

Thanks again for the help.
May 28 '09 #6
I am thinking i am missing something totally simple.

Any suggestions?

thanks for the help.

I'll keep banging away on it.
Jun 2 '09 #7
9,735 Expert Mod 8TB
I don't know why I missed your last post.
Sorry that I didn't see it before now!

Your function is missing the return type.
You have:
Public Function LeaveInfo(ByVal intEmployeeID As Integer)

But it should be:
Public Function LeaveInfo(ByVal intEmployeeID As Integer) As Employee

I'm testing a web service right now...my setup is a bit different and I'm calling it from an ASPX page but I'm having no problems passing back a custom data type back to the client app.

Is your custom data type (your Employee Object) Public?
Jun 2 '09 #8
cool, let me give it a test here in the next couple hours and i'll let you know what i find out.

been a bit busy lately.

i appreciate your help.
Jun 2 '09 #9
9,735 Expert Mod 8TB
The other thing I noticed is that you're using the ExecuteNonQuery method. This method is used to execute SQL statements that don't return a value.

Since you are expecting a value to be returned you should be executing the ExecuteReader method. This method will return a SqlDataReader type that will contain all of the records that result from executing your stored procedure.

Please take a look at this basic article on How to use a database in your program.

If you are returning an Object, then you should be populating that object with the information retrieved from the database....I don't see that in your code either.
Jun 2 '09 #10
Oh my bad on the ExecuteNonQuery, i read somewhere where to use it if it returned one record, which is what i am looking for.

I'll make some changes later on this evening after i get off work and give it a try.
Jun 2 '09 #11
Frinavale, thank you very much for the help. I always find this place a wealth of knowledge. Least I can do is post my code that I got to work. Sorry for the delay in posting back, but i reorganized my favorites and couldn't find anything. I have since made 3 more web services and combined some processes within stored procedures rather than doing the owrk in web service.

My stored procedure had one input variable(@email0) and returned 5 variables (@fname, @lname, @email, @Leave & @ErrorID).

Expand|Select|Wrap|Line Numbers
  1. Imports System.Web.Services
  2. Imports System.Web.Services.Protocols
  3. Imports System.ComponentModel
  4. Imports System.Data
  5. Imports System.Data.SqlClient
  9. Public Structure sEmployee
  10. Public strFName As String
  11. Public strLName As String
  12. Public strEmail As String
  13. Public decLeave As Decimal
  14. Public intErrorID As Integer
  15. End Structure
  17. <WebMethod(Description:="GET Employee info")> _ 
  18. Public Function GetLeaveInfo(ByVal strEmail As String) As sEmployee
  20. Dim sqlCon As New SqlConnection()
  21. Dim sqlCmd As New SqlCommand()
  23. Dim ObjEmployee As New sEmployee
  25. sqlCon.ConnectionString = "***Connection String***"
  27. If sqlCon.State = ConnectionState.Closed Then
  28. sqlCon.Open()
  29. End If
  31. sqlCmd.CommandText = "***Stored Procedure Name***"
  32. sqlCmd.Connection = sqlCon
  33. sqlCmd.CommandType = CommandType.StoredProcedure
  36. Dim Parameter0 As SqlParameter = New SqlParameter("@Email0", strEmail) 
  37. Dim Parameter1 As SqlParameter = New SqlParameter("@fname", SqlDbType.VarChar, 30) 
  38. Dim Parameter2 As SqlParameter = New SqlParameter("@lname", SqlDbType.VarChar, 30) 
  39. Dim Parameter3 As SqlParameter = New SqlParameter("@email", SqlDbType.VarChar, 75) 
  40. Dim Parameter4 As SqlParameter = New SqlParameter("@Leave", SqlDbType.Decimal)
  41. Dim Parameter5 As SqlParameter = New SqlParameter("@errorID", SqlDbType.Int)
  43. Parameter4.Precision = 10
  44. Parameter4.Scale = 2
  46. Parameter0.Direction = ParameterDirection.Input 
  47. Parameter1.Direction = ParameterDirection.Output 
  48. Parameter2.Direction = ParameterDirection.Output 
  49. Parameter3.Direction = ParameterDirection.Output 
  50. Parameter4.Direction = ParameterDirection.Output
  51. Parameter5.Direction = ParameterDirection.Output
  53. SQLCmd.Parameters.Add(Parameter0) 
  54. SQLCmd.Parameters.Add(Parameter1) 
  55. SQLCmd.Parameters.Add(Parameter2) 
  56. SQLCmd.Parameters.Add(Parameter3) 
  57. SQLCmd.Parameters.Add(Parameter4)
  58. SQLCmd.Parameters.Add(Parameter5)
  60. Dim dr As SqlDataReader
  61. dr = sqlCmd.ExecuteReader()
  63. ObjEmployee.strFName = sqlCmd.Parameters("@fname").Value
  64. ObjEmployee.strLName = sqlCmd.Parameters("@lname").Value
  65. ObjEmployee.strEmail = sqlCmd.Parameters("@email").Value
  66. ObjEmployee.decLeave = sqlCmd.Parameters("@leave").Value
  67. ObjEmployee.decLeave = sqlCmd.Parameters("@errorID").Value
  69. Return ObjEmployee
Jun 12 '09 #12
Hi ,

I have done the same above procedure , I am getting the below error only when i execute. There are no errors when i debug.

System.Data.SqlClient.SqlException: Procedure or function ESS_fetchrecords has too many arguments specified.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaDat a()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at ESSWebService.Service1.GetEmplInfo(String strEmpId) in c:\users\premkumartd\documents\visual studio 2010\Projects\ESSWebService\ESSWebService\Service1 .asmx.vb:line 53

I used the below vb.net code

Expand|Select|Wrap|Line Numbers
  1. Public Structure sEmployee
  3.     Public StrEmpName As String
  4.     Public StrGender As String
  5.     Public StrStatus As String
  6.     Public StrEmail As String
  7. End Structure
  8. ' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
  9. ' <System.Web.Script.Services.ScriptService()> _
  10. <System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
  11. <System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
  12. <ToolboxItem(False)> _
  13. Public Class Service1
  14.     Inherits System.Web.Services.WebService
  16.     <WebMethod(Description:="GET Employee info")> _
  17.     Public Function GetEmplInfo(ByVal strEmpId As String) As sEmployee
  18.         Dim sqlCon As New SqlConnection()
  19.         Dim sqlCmd As New SqlCommand()
  20.         Dim ObjEmployee As New sEmployee
  21.         sqlCon.ConnectionString = "Data Source=***;Initial Catalog=****;User ID=***;Password=****"
  22.         If sqlCon.State = ConnectionState.Closed Then
  23.             sqlCon.Open()
  24.         End If
  25.         sqlCmd.CommandText = "ESS_fetchrecords"
  26.         sqlCmd.Connection = sqlCon
  27.         sqlCmd.CommandType = CommandType.StoredProcedure
  28.         Dim Parameter0 As SqlParameter = New SqlParameter("@EmpNumber", strEmpId)
  29.         'Dim Parameter1 As SqlParameter = New SqlParameter("@EmpNo", SqlDbType.VarChar, 500)
  30.         Dim Parameter2 As SqlParameter = New SqlParameter("@EmployeeName", SqlDbType.VarChar, 1000)
  31.         Dim Parameter3 As SqlParameter = New SqlParameter("@Gender", SqlDbType.VarChar, 1)
  32.         Dim Parameter4 As SqlParameter = New SqlParameter("@Status", SqlDbType.VarChar, 1)
  33.         Dim Parameter5 As SqlParameter = New SqlParameter("@EmailID", SqlDbType.VarChar, 150)
  34.         Parameter0.Direction = ParameterDirection.Input
  35.         'Parameter1.Direction = ParameterDirection.Output
  36.         Parameter2.Direction = ParameterDirection.Output
  37.         Parameter3.Direction = ParameterDirection.Output
  38.         Parameter4.Direction = ParameterDirection.Output
  39.         Parameter5.Direction = ParameterDirection.Output
  40.         sqlCmd.Parameters.Add(Parameter0)
  41.                 sqlCmd.Parameters.Add(Parameter2)
  42.         sqlCmd.Parameters.Add(Parameter3)
  43.         sqlCmd.Parameters.Add(Parameter4)
  44.         sqlCmd.Parameters.Add(Parameter5)
  45.         Dim dr As SqlDataReader
  46.         dr = sqlCmd.ExecuteReader()
  47.         ObjEmployee.StrEmpName = sqlCmd.Parameters("@EmployeeName").Value
  48.         ObjEmployee.StrGender = sqlCmd.Parameters("@Gender").Value
  49.         ObjEmployee.StrStatus = sqlCmd.Parameters("@Status").Value
  50.         ObjEmployee.StrEmail = sqlCmd.Parameters("@EmailID").Value
  51.         Return ObjEmployee
  52.     End Function
and have the below sql stored procedure code

PROCEDURE [dbo].[ESS_fetchrecords]

@EmpNumber int


FROM [Orchid].[dbo].[Employee] where EmpNo= @EmpNumber

Please help on what mistake am doing...
Jan 4 '13 #13

Post your reply

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

Similar topics

6 posts views Thread by Sam | last post: by
reply views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.