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

13 Mar 2006 9:17 PM

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...

14 Mar 2006 3:13 AM

Steven Nagy

Post what you have please.
When you say "flat file", do you mean a second file that is seperate to
your database?

SN

14 Mar 2006 5:41 PM

Hexman

On 13 Mar 2006 19:13:01 -0800, "Steven Nagy" <learndot***@hotmail.com>
wrote:

>Post what you have please.

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?
>

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

Hexman

14 Mar 2006 6:32 AM

Cor Ligthert [MVP]

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

Show quoteHide quote

> 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...
>

14 Mar 2006 5:59 PM

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.

On Tue, 14 Mar 2006 07:32:36 +0100, "Cor Ligthert [MVP]"
<notmyfirstn***@planet.nl> wrote:

Show quoteHide quote

>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
>
>
>
>> 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...
>>
>

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