• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

XML Web Service to add rows to sql table


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:="http://XXXX.co.uk/XXXX/")> _
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:="http://xxxx.co.uk/xxxx/")> _
<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

1 Solution
Tra71Author Commented:
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...
I don't think that yo can pass dataset as parameter to your webservice. Instead pass xml using the GetXML method from dataset.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now