webservice with datasets

S

Simon Whale

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
 
S

Simon Whale

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 said:
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
 

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. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
474,056
Messages
2,570,443
Members
47,091
Latest member
IsaacLuna

Latest Threads

Top