DataAdapter - What is writen back to Server in UPDATE

[RichardBerry]

Hi If you use: DataAdapter.FillSchema(dtTable, SchemaType.Source) DataAdapter.Fill(dtTable) ... Add records to dtTable DataAdapter.Update(dtTable) Do ONLY the modified records get written back to the database, or will this command copy ALL the records back? (Basically what I am asking is: Is there any chance or "Wrecking" the original records that were in the database, or is it only the rows added that will be inserted) Regards Richard

[jsampsonPC]

If I'm not mistaken, you should be able to print out the .UpdateCommand property of the DataAdapter.

Jonathan Sampson www.SampsonResume.com

[RichardBerry]

HI How do I do this? I tried: MessageBox.Show(da.UpdateCommand.CommandText) but get an "Object Reference not set to an instance of an object" exception.

[jsampsonPC]

Post the block of code you are referring to. Make sure "da" is the name of your DataAdapter, as well as all of your other objects having the proper name.

Jonathan Sampson www.SampsonResume.com

[RichardBerry]

Hi Here is the code. there is a bit of redumndant code where I tried other methods of adding the data...

    Dim drTmplt As DataRow = Me.dtStockmSample.Rows(0) 'I Use this row as a template for all ne rows
    Dim drNewRow As DataRow ' This is the row to be added
    Dim drXLRow As DataRow 'Row that contains new values imported from Excel
    Dim cnStr As String = "Server='" & Me.txtServer.Text & "';Database='" & Me.txtDatabase.Text & "';uid='userid';pwd='pwd'"
    Dim cn As New SqlClient.SqlConnection(cnStr)
    Dim da As New SqlClient.SqlDataAdapter("SELECT \* FROM " & Me.txtDatabase.Text & ".scheme.stockm", cn)
    Dim i As Integer
    
    dtStockm.Rows.Clear()
    Try
        cn.Open()
        da.FillSchema(dtStockm, SchemaType.Source)
        da.Fill(Me.dtStockm)
        da.SelectCommand.CommandText = "SELECT \* FROM " & Me.txtDatabase.Text & ".scheme.stockm"
        Dim cb As New SqlClient.SqlCommandBuilder(da)
        cb.GetInsertCommand(True)
        For i = 0 To Me.dtXlData.Rows.Count - 1
            drXLRow = Me.dtXlData.Rows(i)
            drNewRow = dtStockm.NewRow
            drNewRow.Item(0).GetType.Name.ToString()
            For j As Integer = 0 To Me.dtStockmSample.Columns.Count - 1
                drNewRow.Item(j) = drTmplt.Item(j)
            Next
            drNewRow.Item("warehouse") = drXLRow.Item("warehouse")
            drNewRow.Item("product") = drXLRow.Item("product")
            drNewRow.Item("alpha") = drXLRow.Item("alpha")
            drNewRow.Item("description") = drXLRow.Item("description")
            drNewRow.Item("unit\_code") = drXLRow.Item("unit\_code")
            drNewRow.Item("long\_description") = drXLRow.Item("long\_description")
            drNewRow.Item("nominal\_key") = drXLRow.Item("nominal\_key")
            drNewRow.Item("purchase\_key") = drXLRow.Item("purchase\_key")
            drNewRow.Item("analysis\_a") = drXLRow.Item("analysis\_a")
            drNewRow.Item("qty\_decimal\_places") = drXLRow.Item("qty\_decimal\_places")
            drNewRow.Item("batch\_traceability") = drXLRow.Item("batch\_traceability")
            drNewRow.Item("standard\_cost") = drXLRow.Item("standard\_cost")
            drNewRow.Item("lead\_time") = drXLRow.Item("lead\_time")
            drNewRow.Item("standard\_labour") = drXLRow.Item("standard\_labour")
            drNewRow.Item("standard\_overhead") = drXLRow.It