webservice with datasets

Discussion in 'ASP .Net Web Services' started by Simon Whale, Jan 20, 2010.

  1. Simon Whale

    Simon Whale Guest

    Hi

    need direction help :)

    I have created a web service to interigate a database and return a dataset
    with datatables on request of a 3rd party for intergration with their
    systems.

    I've created the webservice and when tested works great

    but when i add a reference into the form for the services say table(0)
    doesn't exist


    Many Thanks
    Simon

    -----------------------------------------------------------------------------------------
    code used - web service

    <WebMethod(Description:="Return Name Address and policy number on given
    search criteria", enableSession:=False)> _

    Public Function getPolicyOwner(ByVal Surname As String, ByVal Address1 As
    String, ByVal Address2 As String, ByVal Town As String, ByVal County As
    String, ByVal Postcode As String) As DataSet

    Return GetBasicDetails(Surname, Nothing, Nothing, Town, County, Postcode)

    End Function

    <WebMethod()> _

    Public Function getdata() As DataSet

    Dim cn As New SqlConnection

    Dim da As SqlDataAdapter

    Dim s As String = "select top 10 * from policy"

    Dim ds As New DataSet

    Try

    cn.ConnectionString = "Data Source=OAK_APPS;Initial
    Catalog=Oak_Underwriting;Integrated Security=True"

    cn.Open()

    da = New SqlDataAdapter(s, cn)

    da.Fill(ds)

    Catch ex As Exception

    End Try

    Return ds

    End Function

    Private Function GetBasicDetails(ByVal Surname As String, ByVal Address1 As
    String, ByVal Address2 As String, ByVal Town As String, ByVal County As
    String, ByVal Postcode As String) As DataSet

    'this gets the basic details to find the policy

    Dim ds As DataSet

    Dim dt As DataTable

    Dim cnString As String = "Data Source=OAK_APPS;Initial
    Catalog=Oak_Underwriting;Integrated Security=True"

    Dim cn As SqlConnection

    Dim cmd As SqlCommand

    Dim da As SqlDataAdapter

    If Not IsNothing(ds) Then ds = Nothing

    If Not IsNothing(dt) Then dt = Nothing

    ds = New DataSet()

    dt = New DataTable

    With dt

    ..Columns.Add("Title")

    ..Columns.Add("Firstname")

    ..Columns.Add("Surname")

    ..Columns.Add("Address1")

    ..Columns.Add("Address2")

    ..Columns.Add("Town")

    ..Columns.Add("County")

    ..Columns.Add("Postcode")

    ..Columns.Add("PolicyNo")

    End With



    Try

    cn = New SqlConnection

    cn.ConnectionString = cnString

    cn.Open()

    cmd = New SqlCommand

    cmd.Connection = cn

    cmd.CommandType = CommandType.StoredProcedure

    cmd.CommandText = "[claims_PolicyHolder]"

    Dim SQLParam As New SqlParameter("@surname", SqlDbType.VarChar)

    SQLParam.Value = Surname

    cmd.Parameters.Add(SQLParam)

    SQLParam = New SqlParameter("@Addressline1", SqlDbType.VarChar)

    If Address1 = "" Then

    SQLParam.Value = DBNull.Value

    Else

    SQLParam.Value = Address1

    End If

    cmd.Parameters.Add(SQLParam)

    SQLParam = New SqlParameter("@Addressline2", SqlDbType.VarChar)

    If Address2 = "" Then

    SQLParam.Value = DBNull.Value

    Else

    SQLParam.Value = Address2

    End If

    cmd.Parameters.Add(SQLParam)

    SQLParam = New SqlParameter("@town", SqlDbType.VarChar)

    If Town = "" Then

    SQLParam.Value = DBNull.Value

    Else

    SQLParam.Value = Town

    End If

    cmd.Parameters.Add(SQLParam)

    SQLParam = New SqlParameter("@county", SqlDbType.VarChar)

    If County = "" Then

    SQLParam.Value = DBNull.Value

    Else

    SQLParam.Value = County

    End If

    cmd.Parameters.Add(SQLParam)

    SQLParam = New SqlParameter("@postcode", SqlDbType.VarChar)

    If Postcode = "" Then

    SQLParam.Value = DBNull.Value

    Else

    SQLParam.Value = Postcode

    End If

    cmd.Parameters.Add(SQLParam)

    da = New SqlDataAdapter()

    da.SelectCommand = cmd

    da.Fill(dt)

    'now add the table to the dataset

    ds.Tables.Add(dt)

    ds.Tables(0).TableName = "PolicyHolderSearch"

    Catch ex As Exception

    'Return Nothing

    End Try

    Return ds

    End Function

    -------------------------------------------------------------------------------------------------------------------------------------

    code used test form

    Dim o As New localhost.Service1

    o.RequestEncoding = New System.Text.ASCIIEncoding

    Dim ds As New DataSet

    ds = o.getPolicyOwner(TextBox1.Text, "", "", "", "", "")



    If ds.Tables.Count >= 1 Then MsgBox("HI")

    Me.DataGridView1.DataSource = ds.Tables("policyHolderSearch")

    o = Nothing

    'd = Nothing
     
    Simon Whale, Jan 20, 2010
    #1
    1. Advertisements

  2. Simon Whale

    Simon Whale Guest

    Found the answer to this

    on returning the dataset you need to use the CTYPE function

    e.g. return CTYPE(ds, dataset)

    Simon

     
    Simon Whale, Feb 8, 2010
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.