Trouble Using DataAdapter.Update()

[ccrookston]

Hi, and thanks in advance for any help

From a high level, heres what I'm trying to do. I have a flat comma
delimited file that I need to import into a database, but after some of the
data is massaged and mingled with data in the destination table. So, I first
import the flat file data into a dataset using one dataadapter and one
dataset. Then, I use another dataadapter and dataset to import the current
data from the eventual destination table. Then, I do the logic and place the
resulting data into the *second* dataset.

So far so good. Now, all I have left to do is update the database table
with the new data. Easy, right?

But I can't make it happen. I know it's a lot of code, but here's what I've
got:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' First, import the flat file data
Dim TextConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" &
"C:\Inetpub\wwwroot\GrandCentralCoupons\comissionjunction\" & ";" & _
"Extended Properties=""Text;HDR=Yes;FMT=Delimited;"""

Dim TextConn As New System.Data.OleDb.OleDbConnection(TextConnectionString)
TextConn.Open()
Dim dad1 As New System.Data.OleDb.OleDbDataAdapter("Select top 5 * from
cj.txt", TextConn)
Dim dsSource As DataSet = New DataSet
dad1.Fill(dsSource)
TextConn.Close()
dad1.Dispose()

' Verify that the data is corect by binding to a gridview for visual display
GridView1.DataSource = dsSource
GridView1.DataBind()

' Now, get the currentdata from the destination database table
Dim dbConn As New
SqlConnection(WebConfigurationManager.ConnectionStrings("GCC_Remote").ConnectionString)
Dim dad2 As New SqlDataAdapter()
dad2.SelectCommand = New SqlCommand("SELECT * FROM import_temp_cj2", dbConn)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(dad2)

dbConn.Open()

Dim dsDestination As DataSet = New DataSet
dad2.Fill(dsDestination, "temp_import_cj")

' verify the data is correct by binding to a second gridview for display
GridView2.DataSource = dsDestination
GridView2.DataBind()

' mix. mingle, manage the data form the two datasets
'/* lots of code snipped for easy of viewing */

' verify the new data is correct by binding to a third gridview for display
GridView3.DataSource = dsDestination
GridView3.DataBind()

' update the database table
dad2.Update(dsDestination, "temp_import_cj") '<--- NOT WORKING!!!

dbConn.Close()
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

All three gridviews display exactly what they should be displaying. No
errors at compile or runtime. But, the new data just never makes it into the
db table.

I have a hunch I am missing something really simple... but I can't pick it
out. This is the article I am using as a guide:

http://msdn.microsoft.com/library/d...mdatasqlclientsqlcommandbuilderclasstopic.asp

Thanks!

 

[Miha Markic]

Hi,

You have to define Update/Insert/Delete commands for dad2 - verify what has
SqlCommandBuilder done for you by inspecting them.
Next, check row RowState properties to see if the state is correct.

 

[ccrookston]

:

"You have to define Update/Insert/Delete commands for dad2"

Miha, thank you so much for your help! I really appreciate it. I'm
wondering if you can help me out here.... how do I do this?

dad2.UpdateCommand(" Update Query Goes Here ")

Is this right? But, I am not sure how to formulate such an update query. A
normal update query would look like:

UPDATE dbo.table SET columnName = 'Value'

What would the query look like when I want to update the entire table with
the value of the dataset dsDestination? Do I have to name every column? Do
I loop through each row?

.... Not sure how to proceeded.

Thanks you again!!!

Casey

 

[ccrookston]

Ok, ignore my previous reply.

After reading up on DataAdapters in Sam's Asp.Net 2.0 Unleashed, this is the
code with which I am now working:

~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Create connection to the flat file
Dim TextConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "C:\Inetpub\wwwroot\GrandCentralCoupons\comissionjunction\"
& ";" & _
"Extended Properties=""Text;HDR=Yes;FMT=Delimited;"""

' Load data from flat file into a DataTable
Dim TextConn As New System.Data.OleDb.OleDbConnection(TextConnectionString)
Dim dad1 As New System.Data.OleDb.OleDbDataAdapter("Select top 5 * from
cj.txt", TextConn)
Dim dtblSource As DataTable = New DataTable
dad1.Fill(dtblSource)

' Verify that the data is corect by binding to a gridview for visual display
GridView1.DataSource = dtblSource
GridView1.DataBind()

' Now, get the current data from the destination database table
Dim dbConn As New
SqlConnection(WebConfigurationManager.ConnectionStrings("GCC_Remote").ConnectionString)
Dim dad2 As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM
import_temp_cj2", dbConn)

'Create update, insert, and delete commands
Dim builder As New SqlCommandBuilder(dad2)

' Add new data to a second datatable
Dim dtblDestination As DataTable = New DataTable
dad2.Fill(dtblDestination)

' verify the data is correct by binding to a second gridview for display
GridView2.DataSource = dtblDestination
GridView2.DataBind()

' mix. mingle, manage the data form the two datasets
' /* code removed for easy of reading */

' verify the new data is correct by binding to a third gridview for display
GridView3.DataSource = dtblDestination
GridView3.DataBind()

' Update the database table with new data
dad2.UpdateBatchSize = 0
Dim numUpdates As Integer = dad2.Update(dtblDestination)

label1.Text = "Rows updated: " & numUpdates.ToString
~~~~~~~~~~~~~~~~~~~~~~~~~~~

Still, NOTHING!!!! The label always says "Rows updated: 0" and the data in
the db remains unchanged.

I have been over the example in the book again and again, and it looks like
I am doing everything by the book, and yet it does not work.

Arrrgggg!!!! What am I doing wrong!!!

Please... somebody put me out of my misery.

Casey

 

[Miha Markic]

Try looking dtblDestination.GetChanges() method before you Update to see if
there are modified rows in first place.

 

[ccrookston]

Try looking dtblDestination.GetChanges() method before you Update to see if
there are modified rows in first place.

Ah! Ok, we are on to something here. Thanks for the hint, Miha.
when I do this:

Dim xDataTable As DataTable = dtblDestination.GetChanges()
If Not xDataTable Is Nothing Then
If xDataTable.HasErrors Then
label1.Text = label1.Text & "ERRORS "
End If
Else
label1.Text = " No Changes "
End If

I get "No Changes". So, despite the fact that I merge dtblSource into
dtblDestination and then delete a column from dtblDestination, it still
thinks there are no changes to it. What do I need to do so that it will
think it has changed? Is an update, insert, or delete statement required
before it's been "changed"?

Thanks for your help, Miha.

Casey

 

[ccrookston]

The Answer: http://www.knowdotnet.com/articles/datasetmerge.html