Update One Database from Another Using a WebService and Two DataSets...

Discussion in 'ASP .Net Web Services' started by Jon Jaques, Oct 31, 2006.

  1. Jon Jaques

    Jon Jaques Guest

    Hello, I have a database application used by a small company that needs to
    transfer some updates back and forth, to and from, their website. The
    database uses an Access frontend connected to a MSSQL 2005 server backend,
    but I've created an ASP.net intranet application to handle the data
    transfers independantly of the access frontend.

    The application uses a web services, located on the local intranet; I had
    been using a more traditional approach of simply pushing CSV files back and
    forth, but performance is suffering now that the number of records has
    increased, and, as I said, I need to offload that process so that the normal
    functioning of the access application is not affected.

    I wish the web host supported SSIS, but it does not, plus I think it's SQL
    2000, anyhow.

    The code I've been trying to use looks like this, but does not work:

    ' Call update method with get method as parameter
    <WebMethod()> Public Function AutoUpdateTripsClientsTravelers() As
    String
    Try
    UpdateTripsClientsTravelers(GetTripsClientsTravelers)
    Return "Success!"
    Catch ex As Exception
    Return "Failed: #: (" & Err.Number & ") " & Err.Description
    End Try
    End Function

    ' Accept a dataset as a param and update external database
    <WebMethod()> Public Sub UpdateTripsClients(ByVal ds As DataSet)
    Dim con As New SqlConnection(strGTPcon)
    Dim daCust As New SqlDataAdapter("Select * From tblTripsClients",
    con)
    Dim cbCust As New SqlCommandBuilder(daCust)
    Dim ds2 As New DataSet
    daCust.Fill(ds2, "TripsClients")
    ' Dim strOut As String
    ' books.Merge(dsBooks3, False, MissingSchemaAction.Add)
    ds.Merge(ds2, False, MissingSchemaAction.Add)
    'If ds.HasChanges Then
    daCust.Update(ds, "TripsClients")
    'End If
    ' Return ds
    End Sub

    ' Get data from local database and return as dataset
    <WebMethod()> Public Function GetTripsClientsTravelers() As DataSet
    'Modify this Connection string to use your SQL Server and log on.
    Dim con As New SqlConnection(strKTGPDATAcon)
    Dim daCust As New SqlDataAdapter("Select * From
    qryTripsClientsTravelers", con)
    Dim ds As New DataSet()
    daCust.Fill(ds, "TripsClientsTravelers")
    Return ds
    End Function


    Any ideas? Or, a better way to push/pull those updates?

    Much Appreciated,

    --Jon
     
    Jon Jaques, Oct 31, 2006
    #1
    1. Advertising

  2. Jon Jaques

    CoastalData Guest

    Hmmm, a little more research seems to say that I should go back to using
    some variation of CSV, as there's less overhead in my data, but I'd still
    like to use a web service for this... How might I modifiy this?

    The challenge to the updates will be updating new records where they exist,
    and inserting new records where they don't; there will never be two-way
    updates, though.

    Any suggestions?

    Thanks in advance,

    --Jon

    "Jon Jaques" <> wrote in message
    news:e8AvuiS$...
    > Hello, I have a database application used by a small company that needs to
    > transfer some updates back and forth, to and from, their website. The
    > database uses an Access frontend connected to a MSSQL 2005 server
    > backend, but I've created an ASP.net intranet application to handle the
    > data transfers independantly of the access frontend.
    >
    > The application uses a web services, located on the local intranet; I had
    > been using a more traditional approach of simply pushing CSV files back
    > and forth, but performance is suffering now that the number of records has
    > increased, and, as I said, I need to offload that process so that the
    > normal functioning of the access application is not affected.
    >
    > I wish the web host supported SSIS, but it does not, plus I think it's SQL
    > 2000, anyhow.
    >
    > The code I've been trying to use looks like this, but does not work:
    >
    > ' Call update method with get method as parameter
    > <WebMethod()> Public Function AutoUpdateTripsClientsTravelers() As
    > String
    > Try
    > UpdateTripsClientsTravelers(GetTripsClientsTravelers)
    > Return "Success!"
    > Catch ex As Exception
    > Return "Failed: #: (" & Err.Number & ") " & Err.Description
    > End Try
    > End Function
    >
    > ' Accept a dataset as a param and update external database
    > <WebMethod()> Public Sub UpdateTripsClients(ByVal ds As DataSet)
    > Dim con As New SqlConnection(strGTPcon)
    > Dim daCust As New SqlDataAdapter("Select * From tblTripsClients",
    > con)
    > Dim cbCust As New SqlCommandBuilder(daCust)
    > Dim ds2 As New DataSet
    > daCust.Fill(ds2, "TripsClients")
    > ' Dim strOut As String
    > ' books.Merge(dsBooks3, False, MissingSchemaAction.Add)
    > ds.Merge(ds2, False, MissingSchemaAction.Add)
    > 'If ds.HasChanges Then
    > daCust.Update(ds, "TripsClients")
    > 'End If
    > ' Return ds
    > End Sub
    >
    > ' Get data from local database and return as dataset
    > <WebMethod()> Public Function GetTripsClientsTravelers() As DataSet
    > 'Modify this Connection string to use your SQL Server and log on.
    > Dim con As New SqlConnection(strKTGPDATAcon)
    > Dim daCust As New SqlDataAdapter("Select * From
    > qryTripsClientsTravelers", con)
    > Dim ds As New DataSet()
    > daCust.Fill(ds, "TripsClientsTravelers")
    > Return ds
    > End Function
    >
    >
    > Any ideas? Or, a better way to push/pull those updates?
    >
    > Much Appreciated,
    >
    > --Jon
    >
     
    CoastalData, Nov 1, 2006
    #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. news.microsoft.com
    Replies:
    0
    Views:
    439
    news.microsoft.com
    Apr 12, 2006
  2. Francisco Garcia
    Replies:
    2
    Views:
    470
    Fran Garcia
    Apr 13, 2006
  3. Dotnet developer

    How to communicates with another webservice with another webservice

    Dotnet developer, Apr 9, 2004, in forum: ASP .Net Web Services
    Replies:
    3
    Views:
    103
    Jan Tielens
    Apr 13, 2004
  4. news.microsoft.com
    Replies:
    0
    Views:
    187
    news.microsoft.com
    Apr 12, 2006
  5. Francisco Garcia
    Replies:
    3
    Views:
    251
    vincent
    Apr 13, 2006
Loading...

Share This Page