Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

returning a DataSet in a Web Service method

Posted on 2013-05-30
2
Medium Priority
?
473 Views
Last Modified: 2013-05-31
Hi experts,

I'm using Visual Studio 2010 and VB in a Web Application and SQL Server 2008.
I'm using the Employees table of the Northwind Database.

I have a stored procedure called GetEmployeeDetailsByID that uses 1 parameter called @EmployeeID.  

The code for the stored procedure is this:

CREATE PROCEDURE [dbo].[GetEmployeeDetailsByID] 
	@EmployeeID int = 0
AS
BEGIN
	SET NOCOUNT ON;
	SELECT FirstName, LastName, BirthDate, City, Country 
	FROM Employees WHERE EmployeeID=@EmployeeID
END

Open in new window


I have a Web Service called EmployeeByIDWebServiceDS.asmx

My code looks like this:

Imports System
Imports System.Collections.Generic
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data.SqlClient

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class EmployeeByIDWebServiceDS
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function HelloWorld() As String
        Return "Hello World"
    End Function

    <WebMethod(Description:="Simple Example")> _
    Public Function GetEmployeeDetails(EmployeeID As Integer) As DataSet

        'Create objects
        Dim MyDataSet As DataSet
        Dim MyConnection As SqlConnection
        Dim MyDataAdapter As SqlDataAdapter
        Dim comm As SqlCommand

        'Create a connection to the SQL Server.
        '********************* Windows Authentication ************ FCOE-DPN1TR1 ***********************************
        MyConnection = New SqlConnection("Data Source=FCOE-DPN1TR1;Initial Catalog=Northwind;Integrated Security=True")
        '**********************************************************************************************************

        MyConnection.Open()

        comm = New SqlCommand()

        'Set the command type as StoredProcedure.
        comm.CommandType = System.Data.CommandType.StoredProcedure
        comm.CommandText = "GetEmployeeDetailsByID"
        comm.Parameters.AddWithValue("@EmployeeID", EmployeeID)

        'Create a DataAdapter, and then provide the name of the stored procedure.
        'adapter called myDataAdapter - name of stored procedure - connection
        MyDataAdapter = New SqlDataAdapter("GetEmployeeDetailsByID", MyConnection)

        'Create a new DataSet to hold the records.
        MyDataSet = New DataSet()

        'Fill the DataSet with the rows returned.
        MyDataAdapter.Fill(MyDataSet, "Employees")

        Try

        Catch ex As Exception
            Throw ex
        Finally
            MyConnection.Dispose() 'Dispose of the DataAdapter.
            MyConnection.Close() 'Close the connection.
        End Try

        Return MyDataSet

    End Function

End Class

Open in new window


When I run this service in the web browser I get this:

when i run my web service
If I click on the GetEmployeeDetails  method link I get this:

GetEmployeeDetails method
If I type in 1 for the EmployeeID and then click Invoke I get this:

result of my web service
That only shows the column titles returned by my stored procedure.
It's missing the values.

In my example when I type in 1 for Employee ID it should return one record in xml format for Nancy Davolio.


It should return something like this (this pic is from another example just to show what the xml returned by the web service might look like):

correct result from a different example
I think I'm not doing the MyDataAdapter.Fill  correctly.

Anyone know where the error in my code is?
0
Comment
Question by:maqskywalker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 1

Accepted Solution

by:
manbehindthecurtain earned 1000 total points
ID: 39209688
MyConnection.Open()

        comm = New SqlCommand()

        'Set the command type as StoredProcedure.
        comm.CommandType = System.Data.CommandType.StoredProcedure
        comm.CommandText = "GetEmployeeDetailsByID"
        comm.Parameters.AddWithValue("@EmployeeID", EmployeeID)

        comm.Connection = MyConnection

        'Create a DataAdapter, and then provide the name of the stored procedure.
        'adapter called myDataAdapter - name of stored procedure - connection
        MyDataAdapter = New SqlDataAdapter(comm)

        'Create a new DataSet to hold the records.
        MyDataSet = New DataSet()
0
 
LVL 16

Assisted Solution

by:Rose Babu
Rose Babu earned 1000 total points
ID: 39209958
Try the below code which works well for the response you expected...

<WebMethod(Description:="Simple Example")> _
Public Function GetEmployeeDetails(ByVal EmployeeID As Integer) As DataSet

	'Create objects
	Dim MyDataSet As DataSet
	Dim MyConnection As SqlConnection
	Dim MyDataAdapter As SqlDataAdapter
	Dim comm As SqlCommand

	'Create a connection to the SQL Server.
	'********************* Windows Authentication ************ FCOE-DPN1TR1 ***********************************
	MyConnection = New SqlConnection("Data Source=FCOE-DPN1TR1;Initial Catalog=Northwind;Integrated Security=True")	
	'**********************************************************************************************************

	MyConnection.Open()


	comm = New SqlCommand("GetEmployeeDetailsByID", MyConnection)

	'Set the command type as StoredProcedure.
	comm.CommandType = System.Data.CommandType.StoredProcedure
	'comm.CommandText = "GetEmployeeDetailsByID"
	comm.Parameters.AddWithValue("@EmployeeID", EmployeeID)


	'Create a DataAdapter, and then provide the name of the stored procedure.
	'adapter called myDataAdapter - name of stored procedure - connection

	'MyDataAdapter = New SqlDataAdapter("GetEmployeeDetailsByID", MyConnection)
	MyDataAdapter = New SqlDataAdapter(comm)

	'Create a new DataSet to hold the records.
	MyDataSet = New DataSet()

	'Fill the DataSet with the rows returned.
	MyDataAdapter.Fill(MyDataSet, "Employees")

	Try

	Catch ex As Exception
		Throw ex
	Finally
		MyConnection.Dispose() 'Dispose of the DataAdapter.
		MyConnection.Close() 'Close the connection.
	End Try

	Return MyDataSet

End Function

Open in new window

0

Featured Post

When ransomware hits your clients, what do you do?

MSPs: Endpoint security isn’t enough to prevent ransomware.
As the impact and severity of crypto ransomware attacks has grown, Webroot has fought back, not just by building a next-gen endpoint solution capable of preventing ransomware attacks but also by being a thought leader.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The purpose of this video is to demonstrate how to set up the WordPress backend so that each page automatically generates a Mailchimp signup form in the sidebar. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question