A step up from txt files.

[Nemesis09]

Hi all,

I'm writing a program which is in two parts, firstly.

Sales:
The "Sales" side of the software is just one form with several textboxes, a few checkboxes, a combobox and one button. The idea is that each person in sales has this running on their screen while with the customer, then as their talking to them they fill out this form and when their done they hit the "Submit" button and (at the moment) the program opens a text document and adds a line to it containing the data from the form.

Ordering:
The staff who order stock, send it out to customers and stuff like that have another program which (at the moment) consists of a form which is filled with one DataGridView which displays basic info about each order request (each row in the text file/each set of data submitted from the "Sales" app. When they double click on a row in the datagridview it opens a second form (which is actually identical to the "Sales" form except its read only) which displays all the details for that order request ie. all the data on that line in the text file.

This all works fine except that now I need to add the ability (probably via a checkbox on the details form of the "Ordering" side of the program) to mark each request as "Done" and have it saved in the data file as such, ie. another (Boolean) value in each row indicating whether its done or not.
I cant find a way to do this using the text file method curently in place. It seems that the best thing for me to do is to start using a .mdb database or excel file or something like that so I can hopefully have the Ordering side of the software just change that one value in the file, like in a datagridview rather than reading the whole file and writeing it back which just wont work in this situaion.

All this seems ok except that the datagridview in the "Ordering" software must be up to date all the time (at present it re-reads the txt file every 5 seconds), and the "Sales" software needs to be able to add a row of data to this file at any time. This was ok with the text file but using a database/excel file means that the "Sales" software wont be able to access it as the "Ordering" software will always be accessing it.

I know this was painfully long winded, but any help or suggestions will be greatly appriciated.

Thanks.

 

[Nemesis09]

Nobody knows...?

I guess what would work is being able to have 2 simultaneous Microsoft Jet Engine connections from two different programs on two different computers accessing the one single database, both at the same time.

Or ANYTHING that will let me get a simmilar end result.

PS. The "Sales" app never needs to read from the data file, and it dosent need a "constant" connection, just open, ad a row/line, and close. Only the "Ordering" app needs to read from it, and needs to be up to date all the time.

 

[DavidT_macktool]

I'll throw in my 2 cents worth here.

I don't know how complicated you system is going to be, or how big it is going to grow, but you should be aware of several options.
I would start by changing your thought process from a single file/table to multiple tables.
Sales would load up an "Order Request" table.
ordering would utilize that table and write a record to a "Order Processed" table.
Typically you would also have an address file included in the system.
"Order Processed" would be used to print invoices, receipts, and shippers.
Once an order is processed, you can flag a field in the "Order Request" table or simply delete that record, I like the flag option using a boolean field. That gives you the option of rolling back a transaction if an order is cancelled.

I didn't really address you instantanious order processing needs, because who really needs to check for an order every 5 seconds? I would allow the Order application a button to go 'get' any pending orders at a time they decide. Usually 2 or 3 times a day is enough to get orders processed in a timely manner.

A central database (microsoft Access will work), located on a dedicated computer, with proper security will provide you with the most flexability and versatility for your application.

 

[Nemesis09]

Ok...

But...

I dont need that second table your talking about, but the rest of what you say is exactly what I need. I need a table which allows the Sales team to add a row containing the info from the form via clicking the Submit button, and the same table (or actually only certain columns of it) to be viewed in a datagrid view by the Ordering team. And as you say a boolean of whether its "done" or not.

There is no need for the second table.

Appart from that what your saying is exactly what I need, and what I planned, except the Ordering app connects less often.

So your suggesting that I connect/load/disconnect with the Ordering app every 10 minutes for example (it needs to be fast), and connect/add row/disconnect with the Sales app when the Submit button is clicked?

I can just give them a MsgBox saying "try again later" if they hit Submit while its readonly because of the Ordering side.

Thanks for the input.

 

[DavidT_macktool]

I am trying to avoid 'suggesting' anything.
The sales app form should only connect when the submit button is pushed. If you are adding one record at a time, the dataset will be open for a brief second. You should not have to worry about the message saying the dataset is locked. I envision a blank form of textboxes and such, a salesman enters data on the form and hits a submit button. The submit button opens the database connection, adds the record, closes the connection, blanks out the form and confirms the transaction (prints a reciept?).

Are you trying to get the Ordering totally automated? Will it be attended by someone or just running all the time?

If you are worried about polling the sales table constantly to see if there are orders added, there are other options. Let's see what other members in the forum come up with. What about a seperate 'flag' outside of the table saying there are orders pending. It could even be just a text file that is only there if there are orders pending. the sales app adds the 'flag'. The order app constantly checks for the 'flag' ,accesses the database only when needed, then removes the flag.


Keep me posted on your progress..

 

[cjard]

Dave (above) suggested a l,ong time ago, a simple process that I now use as a very simple database.. I found a way to make it simpler (well, MS made it simpler, i just call the methods)

Anyways.. Heres how it works:

Use your VS2005 to design a typed dataset that looks like a set of access MDB tables
Use these tables for your data storage. The advantage is they can be bound directly to datagrids etc.

I have an app that downlaods info from FreeDB, a music database. I show it, manipulate it etc, and i save it on disk when the app is closed wioth this code:

deniZenDS.WriteXml("freedb_cache.xml")


the dataset instance is called denizenDS and the file it is saved to accompanies the program file, called freedb_cache.xml


when i laod my app i read it in:


if(System.IO.File.Exists("freedb_cache.xml") then deniZenDS.ReadXml("freedb_cache.xml")




the simplest database ever, and I thank Dave who pointed me to it (he posted a more in depth serializer a while ago, but i didnt need the extra functionality)
be advised that your datatables can only contain data types that have a default constructor. if this doesnt mean anything to you, dont worry - it relates to a problem you will probably not encounter in your current use of serialization in vb

 

[Nemesis09]

Interesting,

I think the external "flag" is a good idea, the Ordering software wont be "unattanded" but it needs to display new orders automatically.

I think I've got what I need now... which is actually what I started with... but now it makes sense, so, heres the plan:

The Sales program opens the database (I think I've decided on a .mdb) and adds a row to the only table in the database, Table1, then closes it and either creates a flag file which the Ordering software will constantly check for, or if I feel really creative, sends some data directly to the Ordering software via WinSock telling it there are new orders (I borrowed this method from an old app I made in VB6).Then the Ordering app updates its DataGridView from the .mdb.

Also, the DataGridView only displays selected columns of Table1, as there are 20 data fields. From there, the user can double click on a row in the DataGridView which will show a form with several text boxes displaying the full details of the order as entered by the sales team.

From this "Details" form, the user can tick a checkbox that the order is done, which sets the value of the "Done" column in the datagridview.

So...

My question now is why cant I connect to the .mdb?

I'm using the "Drag DataAddapter on to Form and click Configure DataAddapter" method but when I click "Test Connection" I get an error message saying "Invalid Authorization Specification", this was with all database passwords set as blank and the Details in VB as Username:Admin Password:*Blank*.

So I tried setting the Admin password for the database to "RPS" and the details in VB to Username:Admin Password:RPS and now I get an error saying "Invalid Authorization Specification. Invalid Connection String Attribute."

Any ideas?

 

[Nemesis09]

Well, thats what I was getting using "Microsoft Access Database File (OLE DB)" but when I changed the provider to "Microsoft.Jet.OLEDB.4.0" it went through straight away.

...Strange

 

[Nemesis09]

...ok

It worked for a little while but now the "Configure Data Addapter" wizard wont generate the Update or Delete commands. The delete one dosent matter, but the update one is rather essential.

I dont know why, it just started doing it. When I ran the new app it threw an error saying it needed an update string, and when I checked, sure enough the update string was blank...

So I ran the wizard again and it finnished fine but theres still no update string, even on the last page of the wizard it says everything is fine:
___________________________________________________________

The Data Adapter "DBAdapter" was configured successfully.

Details:

Generated SELECT statement
Generated INSERT statement
Generated table mappings

To apply these settings to your adapter, click finnish.
___________________________________________________________

(Note the absence of the "Generated UPDATE statement" line)

This seemed to start at random, so I went back to the app I was using to test all this, which was working perfectly, with the intent of copying and pasting the UPDATE command text into my new app, but now it has the same problem. The UPDATE string is gone.

...Help...

 

[Nemesis09]

Ok, So I just added a Primary Key to the table in the database and everything works now.

Sorry about the crazy posts, and answering my own questions. Its kind of thinking out loud I guess. All ideas are greatly appriciated though.

Thanks for your input so far.

 

[cjard]

in general, there are better ways of achieving inter-process coommunication than writing and reading flags in databases

 

[Nemesis09]

I dont doubt that, as I said I considered using winsock, but as these programs are running on multiple computers each with a Dynamic DHCP IP I decided the file flag was the easiest way.

As I said, all input is welcome, so if you wish to shed some light on these better ways please feel free.

In any event, for now, the Ordering software just refreshes the database every 30 seconds.

By the way, although I cant use it in this case the XML option you suggested will definately come in handy for future apps.

 

[cjard]

Understood about the XML; its mainly for use in quickly persisting a dataset to disk and back; if you dont need complex database functionality and your app is only on a single system then it makes kinda sense..

In your case I actually feel that a server/client archiecture would be better, possibly using .NET Remoting (calling methods on another computer as if they were in your program on this computer). While covering it in sufficient depth is beyond the scope of this thread, maybe even the forum, it is simple enough to say you could conceive the service to be like a chat service. When the sales client inserts items into the database (server) the server can notify interested Ordering clients that a change has occurred. In its simplest, a UDP network announce that the ordering clients listen to would suffice as notification that more orders are waiting.

If it is to be assumed it is like a fast food restaurant, where you order at the front, and someone in the back makes the food and brings it out, there are advantages to having a server control the work; it can drive a progress board and archive the orders for accounting purposes

It depends how heavy you want to get, but as a light intro to remoting, it could be quite good!