Creating a New Record Using Datasets, tables, etc...

[dpatfield66]

Whew, I tried this with a loooong drawn out explanation, so I'm changing my strategy:

Here's the question.

How do you update just the dataSet or dataTable, and not the actual sql table? I want the user to create a new record, that will populate a form, but I don't want it to go into sql until they hit the save button on the form.

When it goes into SQL, i want the computer to look for the highest AdmitID and then assign the next number up. This I can do, but I don't want to do it until the user hits save.

I want to assignt this ID last because if ANOTHER user creates a new record, while the ORIGINAL user is still working in the form, they will both get the same AdmitID. Unless both hit save on their computers at exactly the same moment, the first person to hit save will get an AdmitID one higher, and then the second person will get one higher than the first.

My explanation is still long! Oh well...

Here's the issue...I can have a bogus AdmitID populate the form until the user hits save, then the real admitID will be assigned, but how can I get the computer to create a new row with this bogus ID in the dataTable or dataSet, rather than the SQL. When I do the tableAdapter.Update(), it goes to sql.

Is this making sense? Help!!!!!

 

[TechGnome]

Don't use the Update. As changes are made to the datatable as you set them. Datasets and datatables by design are inherently disconnected, so anything you do to them will only be in memory until you tell it to send the changes back to the DB -using the .Update command. So, as long as you don't use the .Update command you should be OK.

-tg

 

[dpatfield66]

I found out quick that Update() sends data back to the sql table, as well as Insert.

so what DO I use??

Let's say I add a row to my datatable, and set some of the values of this row. How do I get my form to open up using this row for my control values?

I can't use Fill methods, because this seems to pull from the sql table.

And I don't want to bind anything (unless I have no choice)

Right now, when I open up a form based on a record I've selected from a menu, the form populatates with the record I get when I do a fill method on only that ID.

But this new record is NOT in my sql table yet, only in the FormsDataSet.obAdmitTable (whatever the *!$ that is!!) I still can't seem to get a grasp on datasets versus datatables, etc... I'm learning as I go...don't use update,fill,or insert or you're working with the actual sql table.

 

[TechGnome]

Let me see if I can put this in a simple way.

DataSets are the top structure. A dataset can be made up of one or more datatables. The datatables can contain data from text files, Excel files, XML files, databases... anything.

Each datatable is then made up of one or more dataRows.... this maybe what you need.

Dim dr as DataRow

dr = yourDs.Tables(0).NewRow
dr.item(0) = set values
.
.
.
yourDS.Tables(0).AppendRow(dr)

I'm not 100% the syntax it totaly accurate (running from memory here)... but it should be close enough to get you going in the right direction.

-tg

 

[dpatfield66]

Thanks.

Well, I've got a formsDataSet.xsd that has 2 items.

formsDataSet.TableAdapter
formsDataSet.OBAdmitTable

My SQL table is called OBAdmit, and I've got the TableAdapter.Fill and TableAdapter.Insert methods working fine when I want to get the data from the actual table, or put data into the sql table.

But sometimes I just want to add a row to the DataSet (right?), and open up a form that is populated from the row (PRIOR to even touching the sql table) and THEN later on, eventuall go to the sql when a user hits a save button.

I was able to use a kind of OBAdmitTable.AddRow code, and a workRow = New Row, or something like that (I'm not in my code right now), but once I do that, how do I get my form, when opened to be populated with this newly created row (or FALSE row I should say, because it's not in my sql table yet)?

Right now, I populate my form from a search menu. The user selects the ID from a list of records, and the form is populated. But I'm using fill methods here that specifically pulll from the sql table, and query on the ID selected from the search menu.

But if I hit the NEW record key instead of searching from the menu, I want to create a temp row that populates data into the form, and then eventually will go to sql when the user hits save.

I'll also want to have the user sometimes select an existing record and create a new record from data off of that record. The data will flow obviously, but this new record will still not be in sql yet. It will be from the dataset, right? Or is it datatable? And whatever it is, once again, how can I simply open the form and populate the controls with the data from this newly created row?

 

[dpatfield66]

Sorry, in my prev post, I said NEW record key, when I actually meant New Record Button.

 

[DavidT_macktool]

I must admit it is hard to follow what you are doing. What I have surmised is that you are adding a record but not really adding it until you have done something more to it. Not to sure on the logic here, but perhaps you could acheive the same effect with some different logic.

Go ahead and add the record to SQL as soon as you find your ID - that locks the ID out from others.
Load the other form with the new data.
Have a button that says - Commit Changes - the user doesn't need to know if it has already been added or not. Save all the changes and exit.
Have another button that says - Exit - this button will not save any changes and will delete the newly added record before the form closes. Add a message box to this code that tells the user all data will be lost. Deleting the record will free up that ID for future use also.
Reload the 'calling form' and its table with the updated database either showing the added record or not.

My advise, stop and think about the steps that you want to accomplish. You may be able to reorder them and make it work differently than you first thought.

 

[dpatfield66]

What you are suggesting makes perfect sense. Unfortunately, I'm in a bit of a dilemma.

My boss has instructed that he doesn't ever want to update the record in SQL, but rather create a new one, with a timestamp that will show up in filtered search menus. In other words the user will only ever see the most recent record with that particular ID. Obviously this means there will be no unique primary key as the ID in question.

Whether or not his idea is feasible, or right or wrong, I've got to go with it.
So, if I create a new record, and immediately assign it to the SQL table, what's going to happen is that the user will open the form based on this newly created record,and enter data for the first time and when they hit save, they'll get a SECOND record. My boss considers the first record created really as a garbage record, and would rather have me create the new record with a NULL ID, and then the user can enter their data, and hit save and THEN the ID will get assigned and the record goes to SQL.

I HAVE toyed with the idea of assigning the new record to SQL, and then ONLY in this circumstance, having the record updated when the user hits save. And then after every other time, a new record will get inserted when the user updates data.

Like I said, it may not be the pretties, but it's what the boss wants.

So, what I need to do is (if I don't decide to try my strategy above) SIMPLY have the new record in the dataset, and populate the form from this record (row). How do I add a new row to a dataset and not sql, and then be able to use that new row to populate my form???

I've used New Row and addNewRow, etc...but everytime I do a search afterwards, I have to use the fill and that pulls from the sql.

Currently I'm just taking the minimal fields that populate the new record and flowing them over to the form manually (yuck). That's not going to work when I do New Encounters off of existing records and flow a large amount of data from the previous record. I should be able to put all that data into the temporary fake dataset and then populate my form with that data.

The trick is not ALL data will flow over from a previous record, or I could've just opened up the form with the previous record, and reassigned a new ID when the user hit save.

 

[cjard]

primary keys in this situation MUST be composite

the notion of a primary key is a combination of columns that can be used to uniquely identify any record in a table

I am doing the same thing for an addresses table right now, and i have:

AddressID, ValidFromDate, ValidToDate, AddressLine1 ...


there are more columns but we dont really care about them.
AddressID contains an ID that is fixed per customer (Customer ID 123, John Smith always has address ID 456)
ValidFrom and To define the dates the guy was at that address.

ValidTo is also special in that it is used to determine the current address by being Null

So we might have records like:

456, 01 jan 2005, 01 feb 2005, 1 the road
456, 01 feb 2005, null, 27 the street



now our primary key is AddressID + ValidFromDate (PK cannot be null, and ValidTo is sometimes null) so when we insert a new record, two things must be done:

BEGIN TRANSACTION
UPDATE addresses SET validToDate = ValidToDate WHERE validToDate IS NULL;
INSERT INTO addresses VALUESpAddressID, ValidFrom, null, AddressLine1)
COMMIT TRANSACTION

so the computer of the user will get the current time (or you can use the DB engine time, i use oracle so it would be SYSDATE, if you use MS Jet or SQLServer its Now()) and submit it twice = once to age the old record and once to create the new record


so your PK must change to be unique per record, but part of it must always link to the customer so part must be fixed. this is why we need a compound PK.. comprends?

 

[dpatfield66]

Thanks for the reply, but I'm not sure this answers any of my issues.
I am a guru in MS Access, and I think I've pretty much nailed the PK issue down in many relational db's over a ten year period...

But here I'm trying to figure out how I can populate a dataset and use this data to populate a form, without it going to SQL, or coming from SQL.

There, THAT'S how I should have put it before! How do I work with data that doesn't necessarily come from SQL? DataSets and DataTables, for instance. But bear in mind, I will eventually put the record in SQL.

 

[cjard]

Oh, you can add a new record to a datatable within a dataset if you like. it wont be committed to the database until you tell the dataset to do so.

datasets/tables are disconnected architecture.. think of them like a temporary access database - the dataset is the whole access db and it contains datatables, which are analogous to access tables.. suppose you have a SQL server and the local dataset is like a mini access database that maps automatically to the sql server

you can write into these local tables and modify them, and later save the data. given that the datatables know where they came from, and you will ultimately define how they map back, it willbe able to auto update. my life as an oracle user is made much harder because the tableadapters dont link properly to oracle yet, but if youre using SQLS or access, things should be fine

have a look at the walkthroughs here:

http://msdn2.microsoft.com/en-US/library/wzabh8c4(VS.80).aspx



i thought my post was most relevant to the quandary you found yourself in with your boss wanting a history trail.. you must create a table adapter for whatever table is to store these incremental records. associated with this table adapter comes an insert query that will insert a new record, but it will be intertwined with an update as shown in my example. when the user clicks the save button, you will open a transaction, update the records, insert the new ones.. and those walkthroughs i posted on the above link contain information on how to ask a dataset for those rows with are inserted only (a datatable keeps track of which rows are updated and which are new, so you can choose between them come update time)

have a read and see if any of the walkthroughs answer your question?

 

[dpatfield66]

I see by your reply that we are indeed on the same page, and that makes me feel better! Your description on what needs to be done, also got me excited (as excited as I can get, considering we're talking about programming, and not fun-in-the-sun!)

But the link took me to the Evil MS Support pages, where I've trekked a million times! Yes, sometimes I actually GET something from these pages, but more often than not, it's a maze in Hell! I'm a "Programming for Dummies" kind of guy, if you haven't figured out yet.

I've got all this info on DataSets, DataTables, and TableAdapters, and I'm making them work fantastic when it comes to getting info from SQL and putting info BACK in SQL, whether by Updating or Inserting.

But what I want to do now is simply go to my search menu (which is populated by a special tableadapter.fill() method -works great:

Then select a record, and populate my form with SOME of the data from this record. Kind of like a new encounter with data flow. THEN when the user enters in the rest of the data, he can save it and it's inserted into SQL.

I've actually got that working (I'm doing it HACK-style, but it works!!)

But here's the catch:

What if I have a record that comes from another table? Creating the search menu that merges the two tables' records was easy using a fill method with a UNION SELECT. So I get all records from both tables.

But if I select one record from one table, and want it to flow to the DataSet of the OTHER table, and only certain data, not all. How do I do this cleanly. Oh, I can HACK it, but there's got to be a simple way.

Remember, I don't want it going to SQL just yet, so no updates and no inserts. I just want to select the record that comes from table 1, and have some of this data populate controls from a form that's based on table 2.

I think this is all making sense, but I can clear it up if need be. Just ask me the right questions. But I'll try another summary below:

I've got a triage table and an admit table.
I can get records from both tables to show up in a search menu by using a tableAdapter.fillbySearch() method that has a UNION SELECT in it. It works great!

Ok, now I select a Triage Record, and I want some of the data from this record to flow over into my Admit Form, where the user will later save it into the SQL table as an admit record.

I also want to select admit records and flow data into a new admit record
or triage into triage, or just create new triage or admit records where the basic information is populated.

I had toyed with letting the record go into SQL and then opening the form, and letting the user add more info, and THEN updating it only once, but afterwards, anytime anyone went back into this record and did updates the newly updated record would get inserted per my boss' instructions.

I'm hurting my fingers now, so I'll stop. See if any of this makes sense. I'd really like to use DataSets the way they're supposed to be used.

 

[cjard]

if it helps, im currently asking a question about inserting into 2 tables and Best Practice, so i'm not 100% clear on this issue either, but:

your sql tables for triage and admit are broadly similar? howcome they are separate tables then?

however.. you'd create a form that was bound to admit data, and then, upon opening it and only if youre coming from the triage screen "admit" button, you can pre-populate (manually) the new screen's data. other data will be filled in later but dont forget that not all the data has to be on screen! if you have some sort of unique ref for a patient that must be calculated as part of the admit process, have the database engine do it, not the dataset engine.

reember that a form control may be bound to one data source but the contents can be set by something else.. so set the contents for the user (as though they typed them.. txtFirstName.Text = triageResultSet["FirstName"] ) and leave the rest blank..

only when they press save, will this data find its way into the db and at that time you can calculate other data. for things like unique refs, this must be done by the DB engine, and the insert query contains the logic to do that. if you wantto report the unique ref to the user, you might well need an additional query if the database tech youre using doesnt support returning values from inserts

 

[dpatfield66]

I appreciate the reply.

A couple things:

Unfortunately my triage and admit tables are different because the boss wants it that way. Because these are treated as "modules" for sales purposes, Triage is one table, and Admit in another. Yes, I would've loved to have them all one table and just a tag for Triage, let's say, but I can see the boss' point as well.

So that's why they're separate. Another case is that I don't have my Admit form bound to the dataset for the Admit Table (or AdmitDataTable).

I have coded all my population. I'd like to just bind them to the dataset, but my masked textboxes are giving me issues when accepting the dates and times that come over. I've tried all kinds of formatting but it doesn't seem to work when they're bound. However I can code the format and it works just fine.

So I need a solution for unbound controls.

So far, I have to use a conditional statement.

For just a simple record (ex: I select an Admit record in the search menu and just open the form for updating/viewing), I have the TableAdapter working for me.

When I select the ID from the search menu, I open the Admit Form and onLoad, I use AdmitTableAdapter.FillbyAdmitID(obixAdmitTable,intAdmitID), so my DataSet (dataTable??) only has one record in it. The one I previously selected. I set the variable intAdmitID to the ListView field that I selected (AdmitID)-basic stuff!

Then I take a control for example, and populate it.

Me.txtLName = obixAdmitTable.Rows(0)("LName")

But if I have selected a Triage record and want the flow to go over to a new admit record, then I'd want:

If blnTriagetoAdmit = True then
Me.txtLName = obixTriageTable.Rows(0)("LName")
End if

However, with so many Triage Fields flowing over, I'd PREFER to populate the obixAdmitTable DataSet with the obixTriageTable DataSet, and then I can just go back to my original:

Me.txtLName = obixAdmitTable.Rows(0)("LName")

Also, bear in mind that my Triage fields aren't ALL the same name as my Admit Fields.

PHAsthma = ARFAsthma (in Triage, for example).

I can't change the sql field names, becuase I'm importing data from Access into the sql tables and It's MUCH easier to leave the fields the same name!

So, anyway...I like your idea about binding the controls to the AdmitTable data set and then hardcoding the Triage data set flow-over, but I can't get these darned masked textboxes to work correctly.

My mask is 00/00/00, but the data is 1/15/2006, for example, so it will go over as 11/52/00, or some variation thereof. And I've tried to format them, but it's driving me crazy. So, through my hard coding, I've got it all under control.

So, is there a way to take one DataSet for one table, and flow the data over to another DataSet for another table?

 

[cjard]

i wrote a long response to this but then msn messenger sent this browser to hotmail before i had posted the reply, so i've lost the response. im time bound now so this response will be shorter:

you have one dataset, though you could make another, it's not the right idea.. you should consider another route:

make it so your admit form doesnt always search, e.g. by supplying admitID -1

when admitID is -1 just get the data from the datatable, no search, no fill

then, when moving from triage to admit, you clear the admit datatable and populate it with data from the triage datatable.

no data needs be sent to the sql database for this, just partially fill the admit DT with data and show it on the admit form



on the note of using masked edit boxes for dates - dont. a masked edit 00/00/00 will allow the user to enter a date of 99/99/99. use a date aware control instead like a date time picker.

format your data on the way out of the database (into the datatable) so it is usable by the control, and on the way back into the database format it back. for example i use oracle, and suppose i wanted the date as a text YYYYMMDD:

select to_char(some_date_column, 'YYYYMMDD') as record_date from table
insert into table(some_date_column) values(to_datetxtRecordDate, 'YYYYMMDD'))


so im manipulating the data on the way out and in, and its the queries that perform the conversion