N
NancyASAP
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
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