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