Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><em>As you've commented in <a href="https://stackoverflow.com/questions/7152273/batch-update-table-across-non-linked-sql-servers/7153047#7153047">your other question</a>, here is my answer:</em></p> <p>You should set the <a href="http://msdn.microsoft.com/en-us/library/system.data.common.dbdataadapter.updatebatchsize%28v=VS.100%29.aspx" rel="nofollow noreferrer">UpdateBatchSize</a> property of the SqlDataAdapter to 0 (unlimited). I don't see a way to update table2 without looping table1.</p> <p>Here is a sample code to show you one way to achieve this:</p> <pre><code>Public Sub BatchUpdate(ByVal table1 As DataTable) Dim connectionStringServer2 As String = GetConnectionString() Using connection As New SqlConnection(connectionStringServer2) Dim adapter As New SqlDataAdapter() 'Set the UPDATE command and parameters' adapter.UpdateCommand = New SqlCommand( _ "UPDATE Table2 SET " _ &amp; "NAME=@NAME,Date=@Date WHERE TableOneId=@TableOneId;", _ connection) adapter.UpdateCommand.Parameters.Add("@Name", _ SqlDbType.NVarChar, 50, "Name") adapter.UpdateCommand.Parameters.Add("@Date", _ SqlDbType.DateTime, 0, "Date") adapter.UpdateCommand.Parameters.Add("@TableOneId", _ SqlDbType.Int, 0, "TableOneId") adapter.UpdateCommand.UpdatedRowSource = _ UpdateRowSource.None ' Set the batch size,' ' try to update all rows in a single round-trip to the server' adapter.UpdateBatchSize = 0 ' You might want to increase the UpdateCommand's CommandTimeout as well' adapter.UpdateCommand.CommandTimeout = 600 '10 minutes' Dim table2 As New DataTable("table2") table2.Columns.Add(New DataColumn("Name", GetType(String))) table2.Columns.Add(New DataColumn("Date", GetType(Date))) table2.Columns.Add(New DataColumn("TableOneId", GetType(Int32))) ' copy content from table1 to table2' For Each row As DataRow In table1.Rows Dim newRow = table2.NewRow newRow("TableOneId") = row("ID") newRow("Name") = row("Name") newRow("Date") = row("Date") table2.Rows.Add(newRow) ' note: i have not tested following, but it might work or give you a clue' newRow.AcceptChanges() newRow.SetModified() Next ' Execute the update' AddHandler adapter.RowUpdated, _ New SqlRowUpdatedEventHandler(AddressOf OnRowUpdated) adapter.UpdateBatchSize = 5000 adapter.UpdateCommand.CommandTimeout = 6000 adapter.ContinueUpdateOnError = True adapter.Update(table2) End Using End Sub Private Shared Sub OnRowUpdated(sender As Object, args As SqlRowUpdatedEventArgs) If args.RecordsAffected = 0 Then args.Row.RowError = "Optimistic Concurrency Violation!" args.Status = UpdateStatus.SkipCurrentRow End If End Sub </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSqlDataAdapter Not Updating
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USTim Schmelter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSqlDataAdapter Not Updating
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTBountyClose
    1. CODid not report any errors.
      singulars
      1. PO
      1. USInternet Engineer
    2. CO@Internet: and how many rows were updated?
      singulars
      1. PO
      1. USTim Schmelter
    3. CONo rows updated and SQL Server Profiler reveals no connection attempt. But I am connecting to same DB while populating the DataTable, so it is not a connection problem.
      singulars
      1. PO
      1. USInternet Engineer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload