import from text file, change rowstate

From: NancyASAP (nancy.l.steinmann_at_boeing.com)
Date: 04/06/04 

Date: 6 Apr 2004 11:44:40 -0700

I am importing a text file into a dataset via Microsoft Jet. Works
fine. However, all rows in the table are rowstate=Unchanged, which
means they won't add to the database during dataadapter.update.

I can loop through the rows and change them to rowstate=modified by
changing them, but update command needs parameter for primary key,
which is an Access autonumber, so I don't want to update here. I want
to add rows.

I see a reference in this newsgroup to using LoadDataRow to copy the
rows to another table so they have rowstate=added. However, I get
confused at that point. How the heck do I then get the new table into
my dataset as if it were the old table, so I can do the update via
dataadapter? Can someone toss me a code snippet?

I'm beginning to miss my nice little VB6 ADO import routine.... In my
dreams, I just open a streamreader and copy records directly to the
database....sigh.

In the code below, database is never updated because all rows are
rowstate=unchanged.

Code snippets:

'------Fill da from text file, works fine
Try
                        'copy file to temp filename so our schema.ini file has
                        'the right filename in it.
                        sFileNew = sPath & "\TempAccountData.txt"
                        File.Copy(sFile, sFileNew, True)

                        'open connection and read file into dataset via dataadapter

                        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                         "Data Source=" & sPath & ";Extended Properties=""Text;HDR=YES"""
                        Dim objConn As New
System.Data.OleDb.OleDbConnection(sConnectionString)
                        objConn.Open()
                        Dim sSQL As String
                        sSQL = "SELECT * FROM TempAccountData.txt"
                        _da = New OleDbDataAdapter(sSQL, objConn)
                        Dim ds As New DataSet("AccountData")
                        _da.Fill(ds, "VeoData")
                        Dim dt As DataTable
                        dt = ds.Tables("VeoData")

'------- starting here, I want to
1) Change connection on the dataadapter to my Access database
2) configure dataadapter with correct select, insert, update, delete
commands
for the access table.
3) Perform update

'write rows to table. open connection to our database and pass in
                        'dataset
                        ConfigureAcctDataDataAdapter()
                        If Not _conn.State = ConnectionState.Open Then _conn.Open()
                        ds.EnforceConstraints = False
                        _da.Update(ds, "VeoData")

                        objConn.Close()

Thanks for any help on this.
NancyASAP

Relevant Pages

  • Re: import from text file, change rowstate
    ... > means they won't add to the database during dataadapter.update. ... Can someone toss me a code snippet? ... > 1) Change connection on the dataadapter to my Access database ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: import from text file, change rowstate
    ... I am importing a text file into a dataset via Microsoft Jet. ... Can someone toss me a code snippet? ... database is never updated because all rows are ... Change connection on the dataadapter to my Access database ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Updating database record
    ... You can also cut out a line of code if you want...from this code snippet, ... doesn't look like tbl is doing anything. ... > What's next to update the dataAdapter and thus the database? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Datasets - what is the best approach?
    ... I seem to still be struggling with the best place to declare the DataAdapter ... > I try to retrieve only the data that is currently relevant to the task at hand. ... Crystal report projects with master/detail type ... It accesses data in an SQL Server database. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Requery DataAdapter
    ... The only way the DataAdapter can "see" database changes is to re-execute the ... I display some info in labels on the form from the dataset. ... I want to have the form update the ...
    (microsoft.public.data.ado)