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

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

[Steven Nagy]

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

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

SN

[Cor Ligthert [MVP]]

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

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(Conn ectionString)
'Make a dataadapter
Dim da as new OleDBDataAdapte r("Select * from purord where ponum =
@ffponumer)
'Make a parameter
da.SelectComman d.Parameters.Ad d _
(New OleDb.OleDbPara meter("", OleDb.OleDbType .String))
'Create a Commandbuilder
dim cmb as new OleDbCommandbui lder(da)
Read your flat file and while reading in the loop
dim dt as new datatable
da.SelectComman d.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)("fie ld1") = "whatever"
Try
da.update(dt)
Catch ex as OleDbException
'do whatever you want
end Try

I hope this helps,

Cor


[color=blue]
> 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...
>[/color]

[Hexman]

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

On 13 Mar 2006 19:13:01 -0800, "Steven Nagy" <learndotnet@ho tmail.com>
wrote:
[color=blue]
>Post what you have please.[/color]

I have many versions, some with SQL, ADODB.RecordSet s, DataReaders,
DataSets, etc. I don't know where I really left off. So confused.
[color=blue]
>When you say "flat file", do you mean a second file that is seperate to
>your database?
>[/color]
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.).
[color=blue]
>SN[/color]

Hexman

[Hexman]

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

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 OleDBDataAdapte r, OleDbParameter,
OledbCommandBui lder (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]"
<notmyfirstname @planet.nl> wrote:
[color=blue]
>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(Conn ectionString)
>'Make a dataadapter
>Dim da as new OleDBDataAdapte r("Select * from purord where ponum =
>@ffponumer)
>'Make a parameter
>da.SelectComma nd.Parameters.A dd _
>(New OleDb.OleDbPara meter("", OleDb.OleDbType .String))
>'Create a Commandbuilder
>dim cmb as new OleDbCommandbui lder(da)
>Read your flat file and while reading in the loop
> dim dt as new datatable
> da.SelectComman d.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)("fie ld1") = "whatever"
> Try
> da.update(dt)
> Catch ex as OleDbException
> 'do whatever you want
> end Try
>
>I hope this helps,
>
>Cor
>
>
>[color=green]
>> 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...
>>[/color]
>[/color]