Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Web Services > webservice with datasets

Reply
Thread Tools

webservice with datasets

 
 
Simon Whale
Guest
Posts: n/a
 
      01-20-2010
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


 
Reply With Quote
 
 
 
 
Simon Whale
Guest
Posts: n/a
 
      02-08-2010
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" <(E-Mail Removed)> wrote in message
news:%23Wm%(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do Datasets manage to get deserialized as DataSets instead of a wsdl.exe-created proxy class? Francisco Garcia ASP .Net Web Services 3 04-13-2006 05:53 PM
How do Datasets manage to get deserialized as DataSets instead of a wsdl.exe-created proxy class? Francisco Garcia ASP .Net 2 04-13-2006 10:41 AM
How do Datasets manage to get deserialized as DataSets instead of a wsdl.exe-created proxy class? news.microsoft.com ASP .Net Web Services 0 04-12-2006 09:07 AM
How do Datasets manage to get deserialized as DataSets instead of a wsdl.exe-created proxy class? news.microsoft.com ASP .Net 0 04-12-2006 09:07 AM
Can I use a WebService Architecture to transfer large datasets ? Sanjay ASP .Net 0 11-20-2003 04:32 AM



Advertisments