VB + Databases, best method [Resolved]

I have only ever used Access to make databases with forms that interact with it and am facing a dilemma here!

I have made a notes program, well the forms and all the interactions with it, all im trying to figure out now is, how do i store these notes to my database?

Would i create the note and the database record at the same time? If so how would i update the database with any changes i made because i wouldnt know the unique ID?

Or would i create the database record when I have finished creating the note and get the unique ID then?

I am so confused as to what to do in regards to databases in .NET?

Can anyone give me any pointers for storing records or creating records in .NET with Access?

Docs or tips would be great please!

What do you mean by a "notes program"? What is the program going to do? How are you going to use it?

There is a systray icon and when you click it its creates a form on the desktop that you can write notes on, the notes start and show when the system starts.

Thats all the program does.

The database stores, time created, time modified etc etc

[QUOTE=ChrisL]Would i create the note and the database record at the same time? If so how would i update the database with any changes i made because i wouldnt know the unique ID?QUOTE]
Well if you mean at the same time as in the second you type a letter it gets added, then no. If you mean once you click the submit button (or what ever you plan on doing to "save"), then I don't see why not.

When you submit the note and save it to the db, if you would like you could display a message that displays the primary key. But that's not a very good way to keep track of your db enrties. You'll probably want to have a search function in the program that will search through the database. THat way you could type in "custom control" and a list of all the entries that have that phrase in it will be displayed in a list.

Just thinking quickly this is how I might set up the db
entryNum (Primary Key)
subject
dateEntered
dateLastUpdated
content

sorry, i should have made myself a little clearer, the note remains on the desktop until it is scrapped.

All i am wondering about it when i create the record and when i am updating the record how would i go about changing just that one?

Would I create the record when i create the note, then move to the last record and input the ID on a hidden control on the form and then when i update it use an SQL query to filter the records against the unique id and update all the fields?

I have already created the database and have the code to insert the data.

One other thing i was wondering about is how does the database know this is a new record? Would i just check to see if the Unique ID field is not null and update if the field is not null?

I am just confused because i have never controlled a database this way, as you prob know access handles all this for you!

I'm still not clear on what you're doing with the program.

So are you saying this record gets deleted from the DB once you remove it from the desktop?

Are there multiple records in this db?

If so, How do you plan on selecting a specific not (i.e. clicking on a button, doing a search)?

How are you displaying the note on the desktop?

What do you plan on using the program for?

The notes remain on the desktop, yes there can be multiple records in the database.

So you want to create a note to remind you to clean your car, you click on the systray icon, it opens up and you create the note, add and font changes you want to make and change the note properties.

You also want to create another so you open up antoher one, that remains on the desktop as well.

When you have finished you delete the note and it is removed from the database.

Usage is for reminders, i know there is a outlook version like this with tasks but there is other uses for this program that i do not want to disclose but is irrelivant anyway.

The only problem i am having trouble getting my head around is the intial adding to the database and how the program determines a new note to an updated note??

Well to get the easiest way to let the program know wether the note is new of being updated is how you get to it.

Say you want to add the note, well maybe you'll right click on the sys tray icon and select add. Then you type your note and click the button that says add. But unless you have told the db to automatically generate a primary key your going to have to do that yourself. If you want to update a note that is already displayed on the desktop it depends. You didn't specify how these notes show up on the desktop. If there is one window with all the notes, then you click on the line or a button next to the line. It would have the pk either as the id for the button, or the line. That's how it would search for the record and make it updateable for you. If you have a window for each note then that window's id would be the key and when you click the update button it would up date the record. If you are still unclear please give me more detail about the program. Not so much what you're going to use it for. Just how you've created. How the records are displayed on the desktop. How you plan on search for the record, meaning how do you plan on makign that record active.

It really is very simplistic the program.

There is a systray icon that you click creates a form on the desktop that you type your note in.

When you click it again another form is created and you can create another form.

So each form will have its own ID and the form can update from that.

There will be a properties form for each note if someone wishes to change it where you can add an alarm, change the note title and anything else i want to put as a property.

The database creates the Unique ID when a new record is created, like i say all this is new to me as Access used to handle all this for me, programming just seems to get harder!

I just need to know which is the best method to get the uniques ID.

The way i would do it is:-

Create the note
Create the record
Open an ADO connection
Move to the last record
Get the ID
Return the ID to the form in a hidden field
Any updates i update against the ID


I thought this seemed a very complex way to create a new record in .NET and seemed there must be an easier way as this seems very sloppy

That's exactly what you do. There's really no simpler way. I beleive with access you don't need to move to the last record though. Since you have access creating the primary key you just insert a new record and it will automatically go to the end.

OK thanks a lot for your help, ill get right to it!