Getting Data from One Dataset(from WebService) to another dataset 2

[bsimbeck]

I have two datasets. One comes from a webservice. The other is in my 'client' program. What I want todo is take the dataset from the webservice and write it to my database (it contains one table with about 4,000 records). Now I can get the dataset to my 'client' program but once I do I can't do anything with it. I am trying to fill the program's dataset from a data adapter pointing to a table that is empty and I get an error(generic systems error from the system.data.sql.dll or somthing like that).

Here is the code I have:

Sub GetEMS()

'--open the connection to my SQL Server database (local)
Dim cn As SqlConnection = New SqlConnection("user id=MEDCOMSITE;data source=192.168.1.100\VSDOTNET;persist security info=False;initial catalog=MEDCOMMLOCAL;password=medevac")

cn.open()

'--set the sql statment to populate the data adapter and create it
Dim sql As String = "SELECT * FROM EMS"
dim da As New SqlDataAdapter(sql, cn)

'--set up the command builder
Dim autogen As New SqlCommandBuilder(da)

'--create dataset and populate it (this part gives me an error, but I know the table is empty, I have taken it out)
dim ds as dataset
'da.fill(ds, "EMS")
        
'--create the reference to webservce and get dataset it sends back
Dim myEMSCharts As New WebReference.Service1
Dim EMSds As DataSet = myEMSCharts.getEMS

'--update local db with dataset info from webservice (gives error, non specific system error)
da.Update(EMSds, "EMS")
cn.Close()

I have also tried the merge feature. And I have the acceptchangesduringfill properity on the webservices data adapter set to false.

Any one have any ideas?

Thanks in advanced
Branden

 

[SHelton]

It could be that the DataAdapter that you created is throwing the exception because you are trying to use it to update a dataset it didn't fill.

Why not just loop through the web service datatable rows and run an insert SqlCommand for each row (which is effectively what a commandbuilder would do anyway). You could wrap them up in a transaction to prevent some being committed and not others if an error occurs.

 

[bsimbeck]

That was what I was hoping to avoid but, it looks like that is going to be the only way.

Thanks!

Branden

 

[bassguy]

STOP!!!!!
don't loop

in your client declare a untyped dataset...them hit your Webservice and let it return the typed dataset.

Like this:

your client code:

Private ds_yourdataset As New DataSet
Private ws as new yourwebservice.yourASMXpage

 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

 Me.ds_yourdataset.Merge(ws.Yourwebmethod())
   
end sub

then on your webservice:

<WebMethod()> Public Function yourwebmethod() As yourwebservice.ds_yourdataset
        Me.da_yourdataadapter.Fill(Me.ds_yourdataset)
        Return Me.ds_yourdataset
End Function

thats all you need.

the client dataset will take on the type of the dataset merged with it.

then if you have updates you pass them back to the update function the same way

hope that helps

bassguy

 

[bsimbeck]

Thanks Bassguy,

That was what I was trying todo, my problem turned out to be a problem with my sql server. I didn't give the username any privlages on the database (select or otherwise).

Thanks again,

branden

 

[bassguy]

heh,
always check your webservice first
hit f5 and invoke it it will save you a lot of headaches