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/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandbuilderclasstopic.asp
Thanks!