XML Web Service to add rows to sql table

Posted on 2011-04-28
Last Modified: 2012-05-11

I'm trying to learn web services using Visual Studio 2010.  I am getting there with writing the web service but struggling with the testing.  I have one I'm playing with and wonder if someone could point me the in right direction on how to test.  The second method example is meant to add a row to the table but nothing happens, I just get the message "The following operations are supported. For a formal definition, please review the Service Description."  This is all for something I need to create urgently, so desperate for some help :)...

<% @ WebService Language = "vb" Class = "Sample" %>
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

<WebService(Namespace:="")> _
Public Class Sample

    Public connection As SqlConnection = New SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=XXXX; User Id=sa; Password=XXXX")

    <WebMethod(Description:="Returns Customers", EnableSession:=False)> _
    Public Function GetCustomers() As DataSet
        Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
          "SELECT CustomerID, CompanyName FROM Customers", connection)

        Dim custDS As DataSet = New DataSet()
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
        adapter.Fill(custDS, "Customers")

        Return custDS
    End Function

    <WebMethod(Description:="Updates Customers", EnableSession:=False)> _
    Public Function UpdateCustomers(ByVal custDS As DataSet) As DataSet
        Dim adapter As SqlDataAdapter = New SqlDataAdapter()

        adapter.InsertCommand = New SqlCommand( _
          "INSERT INTO Customers (CustomerID, CompanyName) " & _
          "Values(CustomerID, CompanyName)", connection)
        adapter.InsertCommand.Parameters.Add( _
          "@CustomerID", SqlDbType.NChar, 5, "CustomerID")
        adapter.InsertCommand.Parameters.Add( _
          "@CompanyName", SqlDbType.NChar, 15, "CompanyName")

        adapter.UpdateCommand = New SqlCommand( _
          "UPDATE Customers Set CustomerID = @CustomerID, " & _
          "CompanyName = @CompanyName WHERE CustomerID = " & _
          "@OldCustomerID", connection)
        adapter.UpdateCommand.Parameters.Add( _
          "@CustomerID", SqlDbType.NChar, 5, "CustomerID")
        adapter.UpdateCommand.Parameters.Add( _
          "@CompanyName", SqlDbType.NChar, 15, "CompanyName")

        Dim parameter As SqlParameter = _
          adapter.UpdateCommand.Parameters.Add( _
          "@OldCustomerID", SqlDbType.NChar, 5, "CustomerID")
        parameter.SourceVersion = DataRowVersion.Original

        adapter.DeleteCommand = New SqlCommand( _
          "DELETE FROM Customers WHERE CustomerID = @CustomerID", _
        parameter = adapter.DeleteCommand.Parameters.Add( _
          "@CustomerID", SqlDbType.NChar, 5, "CustomerID")
        parameter.SourceVersion = DataRowVersion.Original

        adapter.Update(custDS, "Customers")

        Return custDS
    End Function
End Class

<%@ WebService Language="vb" CodeBehind="~/App_Code/Service.vb" Class="Client" %>

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System
Imports System.Data

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class Client
    Public Shared Sub Main()
        Dim proxySample As New DsSample.Sample()  ' Proxy object.
        Dim customersDataSet As DataSet = proxySample.GetCustomers()
        Dim customersTable As DataTable = _

        Dim row As DataRow = customersTable.NewRow()
        row("CustomerID") = "ABCDE"
        row("CompanyName") = "New Company Name"

        Dim updateDataSet As DataSet = _

    End Sub
End Class
Many thanks

Question by:Tra71

    Author Comment

    I want to actually post an XML document to the web service using Post but not sure how to go about it.  I've been looking at tutorials but not getting anywhere...
    LVL 25

    Accepted Solution

    I don't think that yo can pass dataset as parameter to your webservice. Instead pass xml using the GetXML method from dataset.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
    The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now