473,836 Members | 1,538 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Web service to call sql stored procedure

55 New Member
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 30957
PRR
750 Recognized Expert Contributor
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
barmatt80
55 New Member
well i got the webservice to work.....partia lly.

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?

Thanks.
May 26 '09 #3
PRR
750 Recognized Expert Contributor
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;
  3.  
Calling SQL Server stored procedures in ASP.NET

Retrieving Scalar Data from Stored Procedure Scott Mitchell
May 27 '09 #4
Frinavale
9,735 Recognized Expert Moderator Expert
@barmatt80
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
barmatt80
55 New Member
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)
  3.  
  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
  13.  
  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)
  27.  
  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
  38.  
  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)
  49.  
  50.         SQLCon.Open()
  51.         SQLCmd.ExecuteNonQuery()
  52.  
  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
  62.  
  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
barmatt80
55 New Member
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
Frinavale
9,735 Recognized Expert Moderator Expert
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
barmatt80
55 New Member
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
Frinavale
9,735 Recognized Expert Moderator Expert
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

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

Similar topics

7
2276
by: Luis | last post by:
I'm using code similar to the following in an asp page to call a stored procedure that does a select on a SQLServer 2000 table: dim rs, SelectSql SelectSql = "EXEC spSelectStuff @param = '" & ParamValue & "'" set rs = conn.Execute(SelectSql) -- do some stuff with rs -- rs.close : set rs = nothing...
2
9728
by: syoung | last post by:
I would like to know if anyone knows how to execute a stored procedure from ASP.NET 2.0. I'm using the NorthWind database and I'm trying to execute the "CustOrderHist" stored procedure. The error I get is "Incorrect syntax near 'CustOrderHist'. " Public Function GetCustomerOrderHistory(ByVal customerid As String) As SqlDataReader Dim conn As New SqlConnection(conString) Dim cmd As New SqlCommand("CustOrderHist", conn)
1
1837
by: Jenny C. | last post by:
Hi I am fairly new to .NET, and I am having a hard time to call a stored procedure from C# code in a windows application I have tested my connection (and login) several time and it is OK. If I try to do a query it works, but when I try to call a stored procedure i get the message "System Error". My code is shown below Any idea what could be the problem A million thanks Jenn ------------------------
6
2321
by: Sam | last post by:
I had created stored procedure at SQL Server 2000 and how do I call it via ASP.Net using VB Language? CREATE PROCEDURE STK As if exists (select * from dbo.sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table . GO
3
7227
by: sarma | last post by:
Hi friends, I created a stored procedure in MySQL like create procedure p() select NOW(); I write a C program, from which i wish to call this stored procedure, i used both mysql_query() and mysql_real_query() functions, but unable to call this stored procedure. Can U please help me out in this...... "CALL p() failed: PROCEDURE sarma.p can't return a result set in the given context" is the error Im getting.
3
3308
by: JM | last post by:
Hi, I am using SQL Server 2000 and ASP.NET 2.0 and want to call a stored procedure using Latest Enterprise Library 2.0. My stored procedure has 3 input parameters: CustId (int), RefId(int) and EmailId(varchar 200) and it returns a dataset. This is how I am trying to do: ------------- int CustId = 1
1
3821
by: eholz1 | last post by:
Hello PHP Group, Is there any advantages (or disadvantages) in using mysqli instead of mysql (querys, connections to database, etc). I am currently using mysql_connect, and things like this: $result = @mysql_query("SELECT * FROM images3 WHERE id=" . $img . " AND p=" .$p. ""); to access my database. I see that some people are using the
0
3815
by: pbaillard | last post by:
Here a sample to call a stored procedure with an Oracle Database. odc.Connection = m_cDb ' use an open connection to your database odc.CommandType = CommandType.StoredProcedure odc.CommandText = "pkg_func.GetReqStatus" ' just set the name of the function, don't used syntax like {? = call pkg_func.GetReqStatus(?,?,?)} ' the return parameter odp = New OleDbParameter("result", OleDbType.VarChar)
2
3018
by: E11esar | last post by:
Hello there. I am going in bit of a circle with this matter; First some background: I am trying to upload the details of a CSV file into an Oracle table. I am using a StreamReader to copy a line at a time into an array, using a string columns = line.Split(separators.ToCharArray()); command. Here is a definition of the table I am loading into:
3
7700
akashazad
by: akashazad | last post by:
Hi Friends My question is "can we call Stored Procedure from a User Defined Function in SQL" If yes then pl tell me how? If No then pl tell me why not ? While I was trying finding about the this question on internet I m getting both kind of answers hence I am confused . Pl help,Thanx in advance
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10558
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10600
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10257
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9387
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7794
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6981
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4459
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.