Solved

returning a DataSet in a Web Service method

Posted on 2013-05-30
2
465 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
2 Comments
 
LVL 1

Accepted Solution

by:
manbehindthecurtain earned 250 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 250 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Testing of the web services 1 48
GeoClustering  and AOG 25 42
SQL works but want to get the XML node data separately 11 27
sql query 5 44
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…

756 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