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. Advertising

  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" <> wrote in message
    news:%23Wm%...
    > 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, Feb 8, 2010
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sanjay
    Replies:
    0
    Views:
    1,625
    Sanjay
    Nov 20, 2003
  2. news.microsoft.com
    Replies:
    0
    Views:
    422
    news.microsoft.com
    Apr 12, 2006
  3. Francisco Garcia
    Replies:
    2
    Views:
    457
    Fran Garcia
    Apr 13, 2006
  4. news.microsoft.com
    Replies:
    0
    Views:
    180
    news.microsoft.com
    Apr 12, 2006
  5. Francisco Garcia
    Replies:
    3
    Views:
    238
    vincent
    Apr 13, 2006
Loading...

Share This Page