We help IT Professionals succeed at work.

XML Web Service to add rows to sql table

Tra71 asked
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:="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

Watch Question


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...
Technical Architect
Unlock this solution and get a sample of our free trial.
(No credit card required)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.