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

dpatfield66

Well-known member
Joined
Apr 6, 2006
Messages
136
Programming Experience
5-10
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!!!!!
 
ps; become really clear on the differences between a dataset and a datatable - you appear to use the terms interchangeably and its a bit confusing
 
My replies to you below:

You wrote:

"you have one dataset, though you could make another, it's not the right idea.. you should consider another route:"
==================================================
I HAVE ONE FORMSDATASET.XSD, YES. BUT I HAVE TWO TABLES.
THESE TWO TABLES ARE IN THIS DATASET, I'M ASSUMING. SO I HAVE TWO TABLE ADAPTERS:

obixTriageTableAdapter & obixAdmitTableAdapter (variables, obviously) that I use fills and updates and inserts to go back and forth from each SQL table respectively.
=====================================================
"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."

I'M A BIT LOST on this one. My clients need the search menu, I can't undo that. I'm not sure how what you say above works. I'd love to find out, though. Exactly what you say is what I want to do. Simply populate my admit datatable with some info from my triage datatable, and vice versa when necessary. Also, I sometimes want to populate my admit table from a record in my admit table (a new encounter so to speak). But I don't know how to do this, other than hard-coding. In other words, I select the record I want info to flow over from, and open up the form. When the form opens, only select data is captured in the fill statement, from the record I selected. Then when the user hits save, after fillling in more data, a new record is created with a new ID.
====================================================

"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"

I'm not sure how to do this...this is my dilemma. Once I figure this out, I've got it made in the shade! And believe me, I'm still researching it.

====================================================

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.

If I can get the datetime picker to allow for NULLS, I would use them instead, but the one in VS 2005 does not, and I can't develop one yet, becuase I'm not yet versed on creating my own controls.
===================================================

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_date:)txtRecordDate, 'YYYYMMDD'))

I do this now in code with my masked textbox.
(Me.txtAAdmitDate.Text = Format(objRow("AAdmitDate"),"MM-dd-yy")
Where "objRow" = obixAdmitTable.Rows(0)
Where "obixAdmitTable" = FormsDataSet.OBAdmitTable

Believe me, this is murder on the way out, because when I have NULL dates/times, I get 1/1/1900 in the SQL table. That's ANOTHER fish to fry.
But for now, I've manipulated the code to account for this.

Also, the client has used input masking before and will require it again. You don't know Nurses and hospital clientele!!!
=====================================================
 
dpatfield66 said:
My replies to you below:

You wrote:

"you have one dataset, though you could make another, it's not the right idea.. you should consider another route:"
==================================================
I HAVE ONE FORMSDATASET.XSD, YES. BUT I HAVE TWO TABLES.
THESE TWO TABLES ARE IN THIS DATASET, I'M ASSUMING. SO I HAVE TWO TABLE ADAPTERS:

obixTriageTableAdapter & obixAdmitTableAdapter (variables, obviously) that I use fills and updates and inserts to go back and forth from each SQL table respectively.

Yes indeed! THis is how it is, and the table adapters roles are to make the sql table an accessible repository of data. you add methods to table adapters, maybe hundreds of methods, that facilitate pumping data back and forth between the repository and the temporary client side containe4r (the datatable)

im currently wondering at what part is it best managed in terms of joins - noone seems able to answer my question, so i might just guess and let you know how it works out. my wonderings are accessory to your main question though..

=====================================================
"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."

I'M A BIT LOST on this one. My clients need the search menu, I can't undo that. I'm not sure how what you say above works. I'd love to find out, though. Exactly what you say is what I want to do. Simply populate my admit datatable with some info from my triage datatable, and vice versa when necessary. Also, I sometimes want to populate my admit table from a record in my admit table (a new encounter so to speak). But I don't know how to do this, other than hard-coding. In other words, I select the record I want info to flow over from, and open up the form. When the form opens, only select data is captured in the fill statement, from the record I selected. Then when the user hits save, after fillling in more data, a new record is created with a new ID.
====================================================
well, yeah.. kinda. see as i mentioned before, you can consider the datatables as local tanks and the sql database a remote reservoir

the tableadapters pump data back and forth but they only do it when you tell them to. in your app at the moment, a pump occurs when the form is opened - a piece of data supplied to the fill method (admitID) is used to runa select. some data comes from the db.

however what im saying is that if you have a triage tank that contains some amount of info just typed, then you can bring your admit form on screen and instruct the admit form that is MUST NOT request the tableadapter to fill the admit tank

i.e. instead of your form load you have a method like:

public sub showAdmission(optional byval admitID as long = -1)

if admitID <> -1 then
me.dataset1.admittableadapter.fill(me.dataset1.admitdatatable)
endif

me.show

end sub



remember that all the control dont care what they show, they just go andf look into the admit datatable.. they show whatever data they find there whether the FILL method put it there or whether you put it there manually (your triage form or maybe a deliberate component that knows what to copy over)

so, you can have both.. are you seeing where im going? you have triage data, and some of it will do as admit data, so clear out the admit dataset, then bnag a new row into admit data (datatable, NOT the sql database) then show the admit form, but the admit form MUST NOT perform a fill!
then, the controls will just show whatever data is already there. the user can add to this and hit the SAVE button, which your code will then go and perform whatever method saves the data to the ADMIT table in the sql repository..


"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"

I'm not sure how to do this...this is my dilemma. Once I figure this out, I've got it made in the shade! And believe me, I'm still researching it.
look up how to insert a new row into a datatable :)

====================================================

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.

If I can get the datetime picker to allow for NULLS, I would use them instead, but the one in VS 2005 does not, and I can't develop one yet, becuase I'm not yet versed on creating my own controls.
===================================================
i thought a dtpicker could have no value, but again, its simple to solve - write a query like this (oracle syntax, NVL means nullvalue.. if the field is null it gets repalces with the other argument, so NVL(null, 'hello') produces 'hello':

SELECT nvl(date_column, '01/01/1901') as record_date FROM DUAL

your data has become able to be put into a dt picker.. i havent time to research an alternate solution to this, but i will when im back on the project that is driving me bonkers :)

(update: just had a quick look at it and you CAN put a checkbox on the dtpicker which can be used to do the null value thing. the other option you have is hiding a textbox off screen and using the dtpicker to update its value, but the textbox is what is bound to the data)



Believe me, this is murder on the way out, because when I have NULL dates/times, I get 1/1/1900 in the SQL table. That's ANOTHER fish to fry.
But for now, I've manipulated the code to account for this.

it needs manipulating in both directions.. your sql statement can format the data out to something compatible, and check if it's that value again on the way back in, and if it is, then insert a null instead...

Also, the client has used input masking before and will require it again. You don't know Nurses and hospital clientele!!!
=====================================================
oh, i know... youre tied to the arse of a dead elephant replicating some legacy system that was 80s unix and dumb terminals, i know how it is...
 
I remember creating a new row in my AdmitTable (dataTable), and then running a msgBox to see the count on the AdmitTable and I got 0.

What I'd like to do is have a method similar to update.

UPDATE (or SET) Here would go the fields from AdmitTable Values(in here would go the paramaters for the Triage data)

So you'd see something like:

OBAdmitTable.Update(TriageLastName,TriageFirstName, etc....)

But not to the SQL table, just to the AdmitTable (dataTable)

I'm looking this up as we speak...(write??)
 
except its not an update.. update is for modifying existing data.. youre actually inserting into the triage datatable, and taking some chosen bits of data from the admit table
 
How do I DO this. I know about TableAdapter.Insert.
Is it just a simple matter of saying OBAdmitTable.Insert to bypass an SQL insert and just enter a row into my temporary dataTable.

Can I do this with the TriageTable going into the AdmitTable?

I've been using TableAdapters, so I create the query at the design view of the FormsDataSet.xsd, but how would I create my OWN query function (or sub) to do the above?
-------------------------------------------------------------
Public Sub TriageInsert(?????)

?????-how do I create the SQL statement to be executed here
I want the SQL statement to be:

INSERT INTO obAdmitTable (obviously not my dbo_OBAdmit!!) etc...etc....

I'm not too worried about the SQL statement syntax...I'll figure that out. I've done it in Access, just can't remember it right at this moment.

But I can't just write the SQL query, without adding something else, correct?? In Access, I did a DB.execute(SQL), where SQL was a string variable. I also obviously had to declare DB as blah blah blah, and SQL as String, etc... Sometimes RS as RecordSet, but what am i doing here in .NET??
--------------------------------------------------------------

I'm VERY new with all this dataTable stuff. I can do what I do now, because of the design view of the FormsDataSet.xsd where I create .insert and .fill methods with the TableAdapter.
 
yeah, there's a method, something like:

OBAdmitTable.AddOBAdmitTableRow(string, string, string, tring ...)

use that to add a new row to your (cleared) datatable. you dont use a query because a query is for the purposes of interacting witha database.. youre not going near the database right now, and you dont have to enter all the values into your admit table either.. you just enter what you know from triage, then prompt the user for more.. and when you actually insert into the database, thats when you calculate the id number etc..

this bit of hard work is done in code, on whatever class you feel is most appropriate - you could even write another class TriageToAdmit and have it read the relevant values out of triage table X and insert them into admit table Y
 
THIS is what I've got when someone presses the Begin Admit button...
More data will flow over, but I'm simplifying it here:
VB.NET:
strFillType = [COLOR=#800000]"BeginAdmitSame"
[/COLOR][COLOR=#0000ff]Dim[/COLOR] workRow [COLOR=#0000ff]As[/COLOR] FormsDataSet.OBAdmitRow
workRow = obixAdmitTable.NewOBAdmitRow()
intTriageID = [COLOR=#0000ff]Me[/COLOR].lstViewTARecords.SelectedItems(0).Text
obixTriageTableAdapter.FillByTriageID(obixTriageTable, intTriageID)
intAdmitID = -2
workRow([COLOR=#800000]"AdmitID"[/COLOR]) = intAdmitID
workRow([COLOR=#800000]"FName"[/COLOR]) = obixTriageTable.Rows(0)([COLOR=#800000]"FName"[/COLOR])
workRow([COLOR=#800000]"LName"[/COLOR]) = obixTriageTable.Rows(0)([COLOR=#800000]"LName"[/COLOR])
workRow([COLOR=#800000]"ADoctor"[/COLOR]) = obixTriageTable.Rows(0)([COLOR=#800000]"ADoctor"[/COLOR])
[COLOR=#0000ff]Me[/COLOR].obixAdmitTable.AddOBAdmitRow(workRow)
frmAdmit.Show()
[COLOR=#0000ff]Me[/COLOR].Close()

But when I open my Admit Form, how do I get the controls to display the data I just appended to my OBAdmit DataTable above? Or DID I append this data? When I do a obixAdmitTable.Select(0), I get nothing.

I'm almost there, just need the form to populate with this newly created row...
 
Last edited by a moderator:
I just realized that .AddOBAdmitRow() has arguments that it takes instead of me just saying:

OBAdmitTable.AddOBAdmitRow(workRow), I can say
OBAdmitTable.AddOBAdmitRow("blah", "blah", "blah", ...) like you said.

But how do I overload this one, or modify it like I can do to the .fill or .insert methods?
 
a random bug in my app now has caused me to look at the designer code for two forms and i notice that they each make their own dataset.. you might want to look at the InitializeComponent methods of the triage and admit forms t0o see if they too are using different instances.. in which case you either have to populate the other dataset or make them share the same one
 
Believe it or not, I've actuall done some coding and have this figured out.
It can't be the CORRECT way to do it, but I'm sure you know as a fellow programmer, that to get the job done, sometimes we have to do a little hack here, a little hack there...

I never needed to do the Add Row, because I just use variables and conditionals, and fill my controls from either the Triage or Admit Data Tables depending on what kind of action it is (new encounter, begin admit from triage, begin triage from admit, etc...)

Now, for a completely NEW record where there is no data flow, and just a few basic fields get filled in...I'm going to use an OBAdmit.AddOBAdmitRow here. I figured out what happens...

When I did my AdmitTableAdapter.fillbySearch() method, it's a UNION query that adds Triage records and Admit records together in a search menu. If I press New Record, my code will add a row to this same group of records that got queried. So I use a OBAdmit.Rows.Count - 1 to get the newly created record and it works! I then populate my form with this new record.

Ex:

Me.txtAFName.Text = OBAdmitTable.Rows((OBAdmitTable.Rows.Count-1)("FNAME")

Hacky, yes, but it works! Thanks for all your help on this one.
If you can still find a quicker and cleaner way to transfer data from one DataTable to another DataTable, that would be cool for me to know! Let me know if there's anything I can help you with...
 
i was working under the assumption that all that one would need is:

a load of controls that are databound to some datatable x
a databinding navigator attached to x

ability to clear x of all data
ability to add a row to x

tell the binding navigator to go to that new row

all bound controls will hence show the new data...



afaics, you currently dont clear X, but just add a row to it, and then manually fill the controls from the (count-1)th row.. i can see how it would work, its just a bit more labour intensive than i had envisaged?
 
I'm with ya!

I don't have my controls bound to any datatables, so I'm hacking as mentioned before, but it's clean code for the most part, documented and defined, so I'm comfortable.

Like I said, though, I'd love to revisit all this ADO.NET stuff and do it right.
Plus the OOP aspect of it as well, where I can take preexisting methods and override them appropriately.
 
Back
Top