Solved

returning a DataSet in a Web Service method

Posted on 2013-05-30
2
460 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 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