I'm perplexed with simple ADO.NET - Access DB Access - Please Help

  • Thread starter Thread starter Hexman
  • Start date Start date
H

Hexman

I've been trying to figure ADO.net/VB/AccessDB out for some time.
I've read many internet articles that have been pointed out to me by
people in this group. Thanks for trying to help. I just can't get
it. All I'm trying to do (in pseudo code) is:

Open a table in Access.
read a transaction from flat file until eof
Build a query ("Select * from purord where ponum = 'flat file
ponumber' ")
execute query
if record found
update the record
else
add the record.
Go read another transaction above.
Close all files


The transaction file will contain between 50 and 400 records so I
don't think I'll need to open and close the connection on each
transaction (as has been shown on many examples).

If the above logic is not the way to go using ADO.NET please show
proper logic and if possible, code.

I'd really like to get someone's example of the code required. It
seems like an easy task, but I just don't get it.

Thanks in advance,

Hexman

I just don't get it...I just don't get it...I just don't get it...
 
Post what you have please.
When you say "flat file", do you mean a second file that is seperate to
your database?

SN
 
Hexman,

I am a lazy one so I like short code. All typed here so watch typos or
whatever.

'Make a connection
Dim conn as new Connection(ConnectionString)
'Make a dataadapter
Dim da as new OleDBDataAdapter("Select * from purord where ponum =
@ffponumer)
'Make a parameter
da.SelectCommand.Parameters.Add _
(New OleDb.OleDbParameter("", OleDb.OleDbType.String))
'Create a Commandbuilder
dim cmb as new OleDbCommandbuilder(da)
Read your flat file and while reading in the loop
dim dt as new datatable
da.SelectCommand.Parameters(0).Value = ffponumer 'from
file
da.fill(dt)
if dt.rows.count = 0 then
dim dr as datarow = dt.newrow
dr.("keycolumn") = ffponumer
end if
'fill the fields I assume that there is only one record per ffponumer
dt.rows(0)("field1") = "whatever"
Try
da.update(dt)
Catch ex as OleDbException
'do whatever you want
end Try

I hope this helps,

Cor
 
Post what you have please.
Click to expand...

I have many versions, some with SQL, ADODB.RecordSets, DataReaders,
DataSets, etc. I don't know where I really left off. So confused.
When you say "flat file", do you mean a second file that is seperate to
your database?
Click to expand...
yes, As for the flat file, its just a simple delimited text file that
has been built by extracting data from various places (other text
files, excel files, etc.).
SN
Click to expand...

Hexman
 
Cor,

Thanks. That's what I've been looiking for. I haven't coded using
your "template" yet. I now have to read about the items I haven't
tried before such as OleDBDataAdapter, OleDbParameter,
OledbCommandBuilder (I was using oledbcommand), DataTable.
-------------------------------------------------------------------------------
If I understand the logic correctly:

Create a new datatable with each transaction
Fill the datatable with results of dataadapter select command
check if any rows found
if not, add new row, setup key fields
replace additional fields
update the datatable
---catch any errors during update
-------------------------------------------------------------------------------

I'm sure I'll be back with more questions (like datatable dispose?,
when does the actual Access table get updated, etc), but you've given
me the jump start I need. An no, I don't think you're a lazy one.

It sure has helped,

Hexman.
 
Back
Top