Saving/loading a structured array from an Access database

[Cary]

This may reveal my poor programming skills, but here goes...

I'm building a pricing tool for my business. I'm nearing the end of the
project, and I've been asked to be able to save quotes in some sort of
database (Major Feature Creep). For each quote, I've got two kinds of
variables. The first are global for the quote, such as

numQuoteRows
QuoteFinalCost
CustomerID

Secondly, I've got the quote rows stored in a structured array, for example:

quoteRow(3).wid getHeight is the height of quote row 3 widget.
quoteRow(6).wid getQuantity is the number of widgets in quote row 6

The question is, how can load & save this info into an Access database. I
figure I can have one table with the global quote information, and one with
the quote rows, but I'm not sure how to feed the info to & from the database.

Please bear in mind I first picked up an intro to VB.NET book a little over
a year ago, and this is my first massive programming project. I've got Access
databases for customer info and all the widget pricing & configuration, so
descriptions of an Access interface would hopefully work. Many thanks for any
advice that you can provide.

--
Cary

[Cor Ligthert]

Re: Saving/loading a structured array from an Access database

Cary,

The first thing you should do in my opinion is make from your array a
datatable.

Than it is so very simple doing what you ask.

In the most simple approach do you need than

An oledbconnection
dim conn as new oledb.oledbconn ection(myconnec tionstring)

For you connectionstrin g

http://www.connectionstrings.com/

a datatable
dim dt as new datatable

a dataadapter
dim da as new oledbdataadapte r("Select * from mytable",conn)
da.fill(datatab le)

an oledbcommandbui lder
dim cmb as new oledb.oledbcomm andbuilder(da)
'this one builds the command and probably you never use it further however
with this the commands are build (when your selectstrings becomes complex
you cannot use it anymore however with your 2 fields it is probably great).

And when you have done your changes you do
da.update(datat able)

And then when you have no concurrent users and set all the error trapping
needed using try and catch, you are almost ready

I hope this helps?

Cor

"Cary" <Cary@discussio ns.microsoft.co m>
[color=blue]
> This may reveal my poor programming skills, but here goes...
>
> I'm building a pricing tool for my business. I'm nearing the end of the
> project, and I've been asked to be able to save quotes in some sort of
> database (Major Feature Creep). For each quote, I've got two kinds of
> variables. The first are global for the quote, such as
>
> numQuoteRows
> QuoteFinalCost
> CustomerID
>
> Secondly, I've got the quote rows stored in a structured array, for
> example:
>
> quoteRow(3).wid getHeight is the height of quote row 3 widget.
> quoteRow(6).wid getQuantity is the number of widgets in quote row 6
>
> The question is, how can load & save this info into an Access database. I
> figure I can have one table with the global quote information, and one
> with
> the quote rows, but I'm not sure how to feed the info to & from the
> database.
>
> Please bear in mind I first picked up an intro to VB.NET book a little
> over
> a year ago, and this is my first massive programming project. I've got
> Access
> databases for customer info and all the widget pricing & configuration, so
> descriptions of an Access interface would hopefully work. Many thanks for
> any
> advice that you can provide.
>
> --
> Cary[/color]

[Andy O'Neill]

Re: Saving/loading a structured array from an Access database



"Cary" <Cary@discussio ns.microsoft.co m> wrote in message
news:5A6F77B7-09D3-45B5-BD90-874DBFE13C02@mi crosoft.com...[color=blue]
> This may reveal my poor programming skills, but here goes...
>
> I'm building a pricing tool for my business. I'm nearing the end of the
> project, and I've been asked to be able to save quotes in some sort of
> database (Major Feature Creep). For each quote, I've got two kinds of
> variables. The first are global for the quote, such as
>
> numQuoteRows
> QuoteFinalCost
> CustomerID
>
> Secondly, I've got the quote rows stored in a structured array, for
> example:
>
> quoteRow(3).wid getHeight is the height of quote row 3 widget.
> quoteRow(6).wid getQuantity is the number of widgets in quote row 6
>
> The question is, how can load & save this info into an Access database. I
> figure I can have one table with the global quote information, and one
> with
> the quote rows, but I'm not sure how to feed the info to & from the
> database.
>
> Please bear in mind I first picked up an intro to VB.NET book a little
> over
> a year ago, and this is my first massive programming project. I've got
> Access
> databases for customer info and all the widget pricing & configuration, so
> descriptions of an Access interface would hopefully work. Many thanks for
> any
> advice that you can provide.
>
> --
> Cary[/color]

Taking this from the very top...
As I understand it.

You have a 1 to many relationship between quote and rows.
So you want 2 tables

Quotes
Quote_Rows

You need to identify what Rows go with a quote, so you need the key to
Quotes as a field in Quote_Rows.
You can then do a join between them with your sql/query.

Quotes
Quote_Id ( Set as primary key )
whatever other fields

Quote_Rows
Quote_Row_Id ( As primary key )
Quote_Id
description
height
width
quantity
whatever other fields

Go into the relationships thing, add both tables and define the relationship
between the two.

I would suggest if you've done any access work then maybe you should stick
to that.

In access.
The simplest way is a subform for the rows in a form for the quotes.
You do that by designing a form for rows, set it to continuous form as
default view.
Stick all the data on one line in the detail section and size everything
else down if you can.
Make the form header and footer visible.
Put any totals fields in the footer as calculated fields, setting the
source to =sum(quote_row_ whatever).
Stick field headings in the header.

Design your Quote screen.
Stick your fields on and get it looking OK ex the rows.
Set it to single form default view.
Then, drag and drop the row screen onto the quote screen.
It'll sort of automagically create the links between them.

--
Regards,
Andy O'Neill