|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
how to get both old and new value for a changed text boxI would like to get the old and new value for a change made to a text
box so that I can store them in a change history table. What is the best way to do this? I am just learning VB 2005; porting an application from MS Access where I could just use the TextBox.OldValue construct. TIA, Kathy Hello Kathy,
Usually nobody ever cares what the old value was. Most of the time the old value is a non-value; It doesn't exist anymore, so we don't bother doing anything with it. If you really must absolutely have this horrid thing.. subclass a textbox and add the functionality yourself. -Boo Show quoteHide quote > I would like to get the old and new value for a change made to a text > box so that I can store them in a change history table. What is the > best way to do this? I am just learning VB 2005; porting an > application from MS Access where I could just use the TextBox.OldValue > construct. > > TIA, > Kathy GhostInAK wrote:
> Hello Kathy, In this application having the old value is critical for auditing> > Usually nobody ever cares what the old value was. Most of the time the old > value is a non-value; It doesn't exist anymore, so we don't bother doing > anything with it. > > If you really must absolutely have this horrid thing.. subclass a textbox > and add the functionality yourself. > > -Boo > purposes. How would subclassing a textbox help get the old value? Thanks. Kathy Hello Kathy,
*ponders* How indeed. Could it be because then you could add your own property? Something like.. oh I dunno.. .OldValue? Hmm.. I think mebe so. -Boo Show quoteHide quote > GhostInAK wrote: > >> Hello Kathy, >> >> Usually nobody ever cares what the old value was. Most of the time >> the old value is a non-value; It doesn't exist anymore, so we don't >> bother doing anything with it. >> >> If you really must absolutely have this horrid thing.. subclass a >> textbox and add the functionality yourself. >> >> -Boo >> > In this application having the old value is critical for auditing > purposes. How would subclassing a textbox help get the old value? > > Thanks. > Kathy GhostInAK wrote:
> Hello Kathy, Uh, that's real helpful.....I'm asking how and you have a sarcastic> > *ponders* How indeed. Could it be because then you could add your own property? > Something like.. oh I dunno.. .OldValue? Hmm.. I think mebe so. > > -Boo > answer like that. Gee thanks. Are these text boxes connected to a data record?
Perhaps make a "sister" table, and when the user comes into the record "on change" you first make an exact copy of the record in that table. On save, you compare the old record ( in sister ) and new record in normal table and any fields that dont match, you have the old value. Something we do in a different programming language ( and im too new to vb to try it here, ) but we call it "scatter() " and its a sub and what we do is before we go into the change of a record we "scatter()". Scatter is a function that dumps the whole data record to an array. Every element in the array is equivalant to the corresponding field in the datatable. So if in the datatable field 3 is "First Name", then your scatter( 3 ) would be the value of the old first name. My vb is too new however to give u the code for that. I guess you can almost look at it as an ArrayList. Miro Show quoteHide quote "Kathy" <ka***@regardingbooks.com> wrote in message news:1159325800.403449.180220@m7g2000cwm.googlegroups.com... > > GhostInAK wrote: >> Hello Kathy, >> >> *ponders* How indeed. Could it be because then you could add your own >> property? >> Something like.. oh I dunno.. .OldValue? Hmm.. I think mebe so. >> >> -Boo >> > > > Uh, that's real helpful.....I'm asking how and you have a sarcastic > answer like that. > Gee thanks. > Hello Miro,
M-man.. no need for arrays. Array's are evil. Your scatter() functionality is already baked into System.Data.DataRow. Kathy however was not asking about database records, she was asking about textboxes. One a side note.. anytime you have to create a temp (or "sister", "sibling", "inbred cousin", "might be related through marriage") table.. THINK REAL HARD if that's the only way to accomplish your goals. Temp tables are a good indicator of flawed design. -Boo Show quoteHide quote > Are these text boxes connected to a data record? > > Perhaps make a "sister" table, and when the user comes into the record > "on > change" you first make an exact copy of the record in that table. > On save, you compare the old record ( in sister ) and new record in > normal > table and any fields that dont match, you have the old value. > Something we do in a different programming language ( and im too new > to vb > to try it here, ) but we call it "scatter() " > and its a sub and what we do is before we go into the change of a > record we > "scatter()". Scatter is a function that dumps > the whole data record to an array. > Every element in the array is equivalant to the corresponding field in > the > datatable. So if in the datatable field 3 is "First Name", > then your scatter( 3 ) would be the value of the old first name. > My vb is too new however to give u the code for that. I guess you can > almost look at it as an ArrayList. > > Miro > > "Kathy" <ka***@regardingbooks.com> wrote in message > news:1159325800.403449.180220@m7g2000cwm.googlegroups.com... > >> GhostInAK wrote: >> >>> Hello Kathy, >>> >>> *ponders* How indeed. Could it be because then you could add your >>> own >>> property? >>> Something like.. oh I dunno.. .OldValue? Hmm.. I think mebe so. >>> -Boo >>> >> Uh, that's real helpful.....I'm asking how and you have a sarcastic >> answer like that. >> Gee thanks. Hello Kathy,
Help comes to those that help themselves. Why dont you press F1 and do a little reading. -Boo Show quoteHide quote > GhostInAK wrote: > >> Hello Kathy, >> >> *ponders* How indeed. Could it be because then you could add your >> own property? Something like.. oh I dunno.. .OldValue? Hmm.. I think >> mebe so. >> >> -Boo >> > Uh, that's real helpful.....I'm asking how and you have a sarcastic > answer like that. > Gee thanks. Kathy,
I'm relatively new to VB.NET but I would think that you could save the text field when it gets focus and then compare it when it looses focus. If it has changed you would have both fields. I understand what you meant by the audit issue I have had similar issues in the past and without sarcasm from people who don't understand business. Jim GhostInAK wrote: Show quoteHide quote > Hello Kathy, > > Help comes to those that help themselves. Why dont you press F1 and do a > little reading. > > -Boo > > > GhostInAK wrote: > > > >> Hello Kathy, > >> > >> *ponders* How indeed. Could it be because then you could add your > >> own property? Something like.. oh I dunno.. .OldValue? Hmm.. I think > >> mebe so. > >> > >> -Boo > >> > > Uh, that's real helpful.....I'm asking how and you have a sarcastic > > answer like that. > > Gee thanks. JR wrote:
> Kathy, Thanks, Jim.> > I'm relatively new to VB.NET but I would think that you could save the > text field when it gets focus and then compare it when it looses focus. > If it has changed you would have both fields. I understand what you > meant by the audit issue I have had similar issues in the past and > without sarcasm from people who don't understand business. > > Jim > That sounds like a reasonable solution. Now I have to figure out how to do it for 100+ fields without having to do on it on events specific to each field. In MS Access you could use the AfterUpdate event for the record and check for which field you are positioned in. What is the equivalent in VB .Net? I've tried the TextChanged event for the form, but it fires when the form is loaded and it does not seem to fire when a change is made. I've also tried the DataMemberChanged for the binding source, but it also doesn't fire when a field is changed. Any ideas? Kathy This is probably not the best way, but I discovered I can associate the
"Enter" focus event for all fields to that of a specific field to force using the same code to capture the old value and get the field name using the ActiveControl.Name. But when I use ActiveControl.Name in the "Leave" focus event, it has the name of the NEXT control. The ActiveControl.Name is also the next control name when used in the Validated and Validating events as well. So what is the best way to get the current field name after completing a change to that field (as well as get the complete new value for that field)? Thanks. Kathy Before the sarcastic fit from Boo (and please treat people the way that
you like to be treated, if you are annoyed, just don't respond), Boo's idea of subclassing is a good idea, and it is what I do. Here's the "how to". This code was written under 2003, but works with 2005 as well. By the way, I'm giving you a small subset of subclassing the text box. Some tweaking may be needed. The control will raise a "ValueChanged" event, if the text box "text" changed on lost focus, instead of firing an event on every character change, which is annoting! 1) Create a user control 2) Switch to Text editing mode 3) After you put in the "Inherits TextBox" line, you will no longer see the user control on the design screen. The beauty of this method is that any changes that Microsoft does to their Textbox control in the future, your textbox will inherit the look and functionality as well. 4) When you're done, you replace all instances of the standard textbox with your new textbox. Replace text Imports System.ComponentModel Imports System.Text Public Class YourControlName Inherits TextBox Private m_EnterValue As String = "" Public Event ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Overloads Property Text(ByVal FormatData As Boolean) As String Get Text = Me.Text End Get Set(ByVal Value As String) Me.Text = Value m_EnterValue = Me.Text End Set End Property Property EnterValue() As String Get EnterValue= m_EnterValue End Get Set(ByVal Value As String) m_EnterValue = Value End Set End Property Private Sub YourControlName_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Leave If m_EnterValue <> Me.Text Then RaiseEvent ValueChanged(sender, e) End Sub End Class Thanks all, for your contributions.
I have it working now by setting the old value in the Tag property recommended by Brian. Then prior to going to a new record, I loop through the fields checking for old values not matching new values and then insert the appropriate audit records as appropriate. Kathy Shane wrote: Show quoteHide quote > Before the sarcastic fit from Boo (and please treat people the way that > you like to be treated, if you are annoyed, just don't respond), Boo's > idea of subclassing is a good idea, and it is what I do. > > Here's the "how to". > This code was written under 2003, but works with 2005 as well. > By the way, I'm giving you a small subset of subclassing the text box. > Some tweaking may be needed. > > The control will raise a "ValueChanged" event, if the text box "text" > changed on lost focus, instead of firing an event on every character > change, which is annoting! > > 1) Create a user control > 2) Switch to Text editing mode > 3) After you put in the "Inherits TextBox" line, you will no longer see > the user control on the design screen. The beauty of this method is > that any changes that Microsoft does to their Textbox control in the > future, your textbox will inherit the look and functionality as well. > 4) When you're done, you replace all instances of the standard textbox > with your new textbox. > > Replace text > Imports System.ComponentModel > Imports System.Text > Public Class YourControlName > Inherits TextBox > Private m_EnterValue As String = "" > Public Event ValueChanged(ByVal sender As Object, ByVal e As > System.EventArgs) > > Overloads Property Text(ByVal FormatData As Boolean) As String > Get > Text = Me.Text > End Get > Set(ByVal Value As String) > Me.Text = Value > m_EnterValue = Me.Text > End Set > End Property > > Property EnterValue() As String > Get > EnterValue= m_EnterValue > End Get > Set(ByVal Value As String) > m_EnterValue = Value > End Set > End Property > > Private Sub YourControlName_Leave(ByVal sender As Object, ByVal e > As System.EventArgs) Handles MyBase.Leave > If m_EnterValue <> Me.Text Then RaiseEvent ValueChanged(sender, > e) > End Sub > End Class Hello Shane,
Hell with you Shane. I volunteer my time here. I aint gettin paid for this crap. You dont like my attitude, fuk off. -Boo Show quoteHide quote > Before the sarcastic fit from Boo (and please treat people the way > that you like to be treated, if you are annoyed, just don't respond), > Boo's idea of subclassing is a good idea, and it is what I do. > > Here's the "how to". > This code was written under 2003, but works with 2005 as well. > By the way, I'm giving you a small subset of subclassing the text box. > Some tweaking may be needed. > The control will raise a "ValueChanged" event, if the text box "text" > changed on lost focus, instead of firing an event on every character > change, which is annoting! > > 1) Create a user control > 2) Switch to Text editing mode > 3) After you put in the "Inherits TextBox" line, you will no longer > see > the user control on the design screen. The beauty of this method is > that any changes that Microsoft does to their Textbox control in the > future, your textbox will inherit the look and functionality as well. > 4) When you're done, you replace all instances of the standard textbox > with your new textbox. > Replace text > Imports System.ComponentModel > Imports System.Text > Public Class YourControlName > Inherits TextBox > Private m_EnterValue As String = "" > Public Event ValueChanged(ByVal sender As Object, ByVal e As > System.EventArgs) > Overloads Property Text(ByVal FormatData As Boolean) As String > Get > Text = Me.Text > End Get > Set(ByVal Value As String) > Me.Text = Value > m_EnterValue = Me.Text > End Set > End Property > Property EnterValue() As String > Get > EnterValue= m_EnterValue > End Get > Set(ByVal Value As String) > m_EnterValue = Value > End Set > End Property > Private Sub YourControlName_Leave(ByVal sender As Object, ByVal e > As System.EventArgs) Handles MyBase.Leave > If m_EnterValue <> Me.Text Then RaiseEvent > ValueChanged(sender, > e) > End Sub > End Class GhostInAK wrote:
> Hell with you Shane. I volunteer my time here. I aint gettin paid for this Well Ghost, with sarcastic, unhelpful answers like above why bother> crap. You dont like my attitude, fuk off. wasting your valuable time? It seems you have enough free time to waste someone else's time with your non-answer. No offence, but how old are you? Hello mg,
NON-ANSWER!? Fukin retard. It was a perfect answer. It was EXACTLY what the OP should have done. But no.. she used the freakin Tag property instead.. why? Because she's afraid of a lil work. I'll remind you of the answer: If you really must absolutely have this horrid thing.. subclass a textbox and add the functionality yourself. Followed by: *ponders* How indeed. Could it be because then you could add your own property? Something like.. oh I dunno.. .OldValue? Hmm.. I think mebe so. Did she take the advice? No.. All she saw was the sarcasm.. and it wasnt really even sarcasm. She cut her own throat out of spite. I have no pity for any of you. -Boo Show quoteHide quote > GhostInAK wrote: > >> Hell with you Shane. I volunteer my time here. I aint gettin paid >> for this crap. You dont like my attitude, fuk off. >> > Well Ghost, with sarcastic, unhelpful answers like above why bother > wasting your valuable time? It seems you have enough free time to > waste someone else's time with your non-answer. No offence, but how > old are you? > GhostInAK wrote:
> NON-ANSWER!? Fukin retard. It was a perfect answer. LOL. Your reply demonstrates my point far better than I ever could.You have a very low threshold for 'perfect'. Here are some thoughts that might help. Our approach is to have middle
tier classes that handle all data I/O. Each of these classes has public members that represent the fields in the back end database. They also have a uniform set of methods (an interface) to access the typical functionality. As a small example, lets say we have an employee table that only has 3 fields, LastName, FirstName, and EmployeeId. Lets say we write a class named EmployeeClass, it would have 3 public members: Public LastName As String Public FirstName As String Public EmployeeId As String The class would also have Load, Add, Update, and Delete methods. If I had some form to allow editing a record, here's how the code would look to populate the textboxes with values from the table: Outside any methods in the form (so as to make it public to the form) Dim EmployeeObject as New EmployeeClass() Inside some method such as OnLoad: EmployeeObject.Load(EmployeeId) Me.TextBoxLastName.Text = EmployeeObject.LastName Me.TextBoxFirstName.Text = EmployeeObject.FirstName Me.TextBoxEmployeeId.Text = EmployeeObject.EmployeeId To keep an audit trail, I might do something like this (assuming we are in some kind of Save method): If Me.TextBoxLastName.Text <> EmployeeObject.LastName 'Write the old and/or new value to an audit table or whatever you want Endif The same code would apply to the other two textboxes. Since the class retrieved the data, it isn't directly bound to the textbox, therefore I can compare the retrieved value to the value in the textbox. The heavy lifting is done by the EmployeeObject, it takes a bit more work to create, but the payoff is in flexibility. I hope this helps, but if you some clarification, let me know and I'll help as much as I can. Kathy wrote:
> I would like to get the old and new value for a change made to a text There are two basic issues you are trying to address.> box so that I can store them in a change history table. What is the > best way to do this? I am just learning VB 2005; porting an > application from MS Access where I could just use the TextBox.OldValue > construct. > > TIA, > Kathy 1) If it changed. 2) The old value. Before looking into a VB answer to this, if this is being ported from Access, you may now be using an actual database. In which case, you can add a TRIGGER to be fired ON UPDATE, and capture the change at the database level. If the answer is to come from VB, a simple answer would be set the Tag property to the old value as soon as the data is loaded. Then, when storing the new data, compare it with the stored old value. For mutiple textboxes, a loop could be employed. For example, to store the data: For Each Current_Control As Control In Me.Controls If Current_Control.GetType Is GetType(TextBox) Then Current_Control.Tag = Current_Control.Text Next HTH, B. Brian,
I have set this up to work with either a MS Access or SQL Server database. I think I've accomplished both issues now. Inside the LEAVE event I'm comparing the old to the new value as suggested by Jim. Inside the ENTER event I was storing the value in a global variable. But I like your idea of using the Tag property for saving the old value better than what I did. That way I can compare all the text boxes during the binding source's CurrentChanged event. Does that sound reasonable? Thanks ! Kathy Show quoteHide quote > Brian Tkatch wrote: > There are two basic issues you are trying to address. > > 1) If it changed. > 2) The old value. > > Before looking into a VB answer to this, if this is being ported from > Access, you may now be using an actual database. In which case, you can > add a TRIGGER to be fired ON UPDATE, and capture the change at the > database level. > > If the answer is to come from VB, a simple answer would be set the Tag > property to the old value as soon as the data is loaded. Then, when > storing the new data, compare it with the stored old value. > > For mutiple textboxes, a loop could be employed. For example, to store > the data: > > For Each Current_Control As Control In Me.Controls > If Current_Control.GetType Is GetType(TextBox) Then > Current_Control.Tag = Current_Control.Text > Next > > HTH, > B. Something else you might think about Kathy,
I usually read data directly into controls like textbox's using a data reader. I did work on a project in my early days of .NET where we used a Currency Manager and the controls were bound too the DataTable. In this set up, before the Table.AcceptChanges() method is called the table actually contains the original value and proposed value. This way there would be no reason to check every control individually. Do some reading in the BOL about DataTables and Currency Managers. Additionally, you mentioned Sql Server, if this is the database your using then I would put an Update Trigger on the table and when it fires, insert the old record into an identical table. On another note..... Sorry about the sarcastic feedback you got, in our line of work their seems to be a lot if idiots with god syndrome. Izzy Kathy wrote: Show quoteHide quote > Brian, > > I have set this up to work with either a MS Access or SQL Server > database. I think I've accomplished both issues now. Inside the LEAVE > event I'm comparing the old to the new value as suggested by Jim. > Inside the ENTER event I was storing the value in a global variable. > But I like your idea of using the Tag property for saving the old value > better than what I did. That way I can compare all the text boxes > during the binding source's CurrentChanged event. Does that sound > reasonable? > > Thanks ! > Kathy > > > Brian Tkatch wrote: > > There are two basic issues you are trying to address. > > > > 1) If it changed. > > 2) The old value. > > > > Before looking into a VB answer to this, if this is being ported from > > Access, you may now be using an actual database. In which case, you can > > add a TRIGGER to be fired ON UPDATE, and capture the change at the > > database level. > > > > If the answer is to come from VB, a simple answer would be set the Tag > > property to the old value as soon as the data is loaded. Then, when > > storing the new data, compare it with the stored old value. > > > > For mutiple textboxes, a loop could be employed. For example, to store > > the data: > > > > For Each Current_Control As Control In Me.Controls > > If Current_Control.GetType Is GetType(TextBox) Then > > Current_Control.Tag = Current_Control.Text > > Next > > > > HTH, > > B. Hi Izzy,
I was just noticing if I leave a textbox, I can get to the bindingsource's current datarowview which has the old value as you indicated; that is, unless I do an EndEdit. However, if I leave the parent of the textbox (e.g. leave the tab page the textbox resides on), then apparently an EndEdit is automatic because the current datarowview has the new value at that point. I should probably manipulate the data directly which I do for non-UI related tasks. Is there a significant difference in performance between the 2 data manipulation methods? Thanks. Kathy Izzy wrote: Show quoteHide quote > Something else you might think about Kathy, > > I usually read data directly into controls like textbox's using a data > reader. I did work on a project in my early days of .NET where we used > a Currency Manager and the controls were bound too the DataTable. In > this set up, before the Table.AcceptChanges() method is called the > table actually contains the original value and proposed value. > > This way there would be no reason to check every control individually. > > Do some reading in the BOL about DataTables and Currency Managers. > > Additionally, you mentioned Sql Server, if this is the database your > using then I would put an Update Trigger on the table and when it > fires, insert the old record into an identical table. > > On another note..... > > Sorry about the sarcastic feedback you got, in our line of work their > seems to be a lot if idiots with god syndrome. > > Izzy Kathy,
I had a nice long respone written for you....then my stupid computer over heated and turned off. Anyway...here's an idea: If this data is being stored in Access, you could insert the old data into the history table before you run your update. This will create a little extra duty for your database server but a very common task for audit tracking. I would try to avoid comparing each textbox's value with a cached initial value too determine if changes have been made. I usually just define a boolean property called "IsDirty" and on validating of the textbox controls set this property to true. You can then use that to determine if an update and insert needs to take place. If IsDirty Then MoveToHistory(PrimaryKey) UpdateTable(PrimaryKey) IsDirty = False Else 'No Changes Detected End If Just curious...How big is this Access database? AND how much history do you need too store? Izzy Kathy wrote: Show quoteHide quote > Hi Izzy, > I was just noticing if I leave a textbox, I can get to the > bindingsource's current datarowview which has the old value as you > indicated; that is, unless I do an EndEdit. However, if I leave the > parent of the textbox (e.g. leave the tab page the textbox resides on), > then apparently an EndEdit is automatic because the current > datarowview has the new value at that point. > > I should probably manipulate the data directly which I do for non-UI > related tasks. Is there a significant difference in performance > between the 2 data manipulation methods? > Thanks. > Kathy > > Izzy wrote: > > Something else you might think about Kathy, > > > > I usually read data directly into controls like textbox's using a data > > reader. I did work on a project in my early days of .NET where we used > > a Currency Manager and the controls were bound too the DataTable. In > > this set up, before the Table.AcceptChanges() method is called the > > table actually contains the original value and proposed value. > > > > This way there would be no reason to check every control individually. > > > > Do some reading in the BOL about DataTables and Currency Managers. > > > > Additionally, you mentioned Sql Server, if this is the database your > > using then I would put an Update Trigger on the table and when it > > fires, insert the old record into an identical table. > > > > On another note..... > > > > Sorry about the sarcastic feedback you got, in our line of work their > > seems to be a lot if idiots with god syndrome. > > > > Izzy Izzy,
The Access app I am porting from is in use at multiple businesses where there is a mix of databases sizes; some use Access and some use SQL Server as backend db (hence my need to accommodate both). My users require saving unlimited field change history (w/ old & new values, who made the change, date/time change made, field changed, etc), so the history can get rather large. I would prefer to capture the changes at the same time a record gets saved versus as each field changes for synchronization purposes. Just curious, why do you recommend against comparing against the cached (old) values? Also, does setting IsDirty as you suggested prevent the automatic database update until you force it? Another contributor suggested saving the old value in each control's Tag property. That seems workable, too. Another contributor suggested subclassing, but I would lose the control's visibility in the designer which I really don't want to do. Thanks for the help. Kathy Izzy wrote: Show quoteHide quote > Kathy, > > I had a nice long respone written for you....then my stupid computer > over heated and turned off. > > Anyway...here's an idea: > > If this data is being stored in Access, you could insert the old data > into the history table before you run your update. This will create a > little extra duty for your database server but a very common task for > audit tracking. > > I would try to avoid comparing each textbox's value with a cached > initial value too determine if changes have been made. I usually just > define a boolean property called "IsDirty" and on validating of the > textbox controls set this property to true. You can then use that to > determine if an update and insert needs to take place. > > If IsDirty Then > > MoveToHistory(PrimaryKey) > UpdateTable(PrimaryKey) > IsDirty = False > Else > > 'No Changes Detected > End If > > Just curious...How big is this Access database? AND how much history do > you need too store? > > Izzy I see, you need too accommodate both db's. Well....
The reason I would try to avoid comparing a controls value to a stored old value is performance. You mentioned having hundreds of textbox's, so it could turn into a lengthy process and sounds like it would be maintenance intensive. For now you will have to do whatever is timely necessary. Tag property sounds fine. Only do sub classing if you really understand all that's involved. Why make things more complicated then they need too be. Moving forward...IMO, I would develop 2 versions of your app. 1 for Access clients and another for SQL Server clients. For the Sql Server clients I would add update, delete triggers to the database tables and archive history that way. Or you could put this logic in a Stored Procedure and make it a transaction. First adding to history then update live data. This would also resolve any problems with multiple people updating the same info at the same time. For the Access version I would do as I suggested earlier, running an insert too archive history then perform the update. I'll bet since you're accommodating both SQL Server clients and Access clients you're probably using OLEDB objects. This is another reason to create 2 versions, this way your SQL Server clients will benefit from SqlClient objects. Let me know if you need any help. Izzy Kathy wrote: Show quoteHide quote > Izzy, > The Access app I am porting from is in use at multiple businesses where > there is a mix of databases sizes; some use Access and some use SQL > Server as backend db (hence my need to accommodate both). My users > require saving unlimited field change history (w/ old & new values, who > made the change, date/time change made, field changed, etc), so the > history can get rather large. I would prefer to capture the changes at > the same time a record gets saved versus as each field changes for > synchronization purposes. Just curious, why do you recommend against > comparing against the cached (old) values? Also, does setting IsDirty > as you suggested prevent the automatic database update until you force > it? Another contributor suggested saving the old value in each > control's Tag property. That seems workable, too. Another contributor > suggested subclassing, but I would lose the control's visibility in the > designer which I really don't want to do. > Thanks for the help. > Kathy > > Izzy wrote: > > Kathy, > > > > I had a nice long respone written for you....then my stupid computer > > over heated and turned off. > > > > Anyway...here's an idea: > > > > If this data is being stored in Access, you could insert the old data > > into the history table before you run your update. This will create a > > little extra duty for your database server but a very common task for > > audit tracking. > > > > I would try to avoid comparing each textbox's value with a cached > > initial value too determine if changes have been made. I usually just > > define a boolean property called "IsDirty" and on validating of the > > textbox controls set this property to true. You can then use that to > > determine if an update and insert needs to take place. > > > > If IsDirty Then > > > > MoveToHistory(PrimaryKey) > > UpdateTable(PrimaryKey) > > IsDirty = False > > Else > > > > 'No Changes Detected > > End If > > > > Just curious...How big is this Access database? AND how much history do > > you need too store? > > > > Izzy Izzy,
>How are you currently telling your application that changes to data I was using the .HasChanged method.>have happened and now an update is necessary? > For the Sql Server clients I would add update, delete triggers to the Those are some great suggestions. Yes I was using OleDb. So you think> database tables and archive history that way. Or you could put this > logic in a Stored Procedure and make it a transaction. First adding to > history then update live data. This would also resolve any problems > with multiple people updating the same info at the same time. > the benefits of using SqlClients outweighs having to maintain 2 versions? Would it be worth converting my Access customers to use SQL Express (assuming that's free). Is SQL Express robust enough? Thanks again. Kathy > Moving forward...IMO, I would develop 2 versions of your app. 1 for Not to fuel any fires, but.... We have all our db I/O go through a> Access clients and another for SQL Server clients. middle tier class. The front end knows nothing of the backend. The form sends requests for adding and updating records to the middle tier class and that class knows what db to send things to. I can easily accomodate ANY db this way, and in fact do. We have a mixture of DB2 (IBM iSeries), Oracle, and SQL Server. I could just as well accomodate Access if I had to. I also have my audit trail here since all updates come through this class. Just my 2 cents. Hay BK,
How do you tell the middle layer which database it's working with? I've yet to write any Ntier apps and am interested in thier design. Thanks, Izzy BK wrote: Show quoteHide quote > > Moving forward...IMO, I would develop 2 versions of your app. 1 for > > Access clients and another for SQL Server clients. > > Not to fuel any fires, but.... We have all our db I/O go through a > middle tier class. The front end knows nothing of the backend. The > form sends requests for adding and updating records to the middle tier > class and that class knows what db to send things to. I can easily > accomodate ANY db this way, and in fact do. We have a mixture of DB2 > (IBM iSeries), Oracle, and SQL Server. I could just as well accomodate > Access if I had to. I also have my audit trail here since all updates > come through this class. > > Just my 2 cents. We have a User object that is instantiated when the user authenticates
to the system that keeps track of what back end to communicate with. That user object is handed off to every form as forms are opened. Every middle tier business class requires a valid user object in the New constuctor. By using this design, we can and sometimes do give the users the ability to redirect to a different database without logging out. We simply force all the open forms to shutdown and then we update the user object with new server/database information. I have one application where they occasionally want to connect to different databases (sometimes connecting to a local database to work offline, or to a test database to try something without affecting production). They can easily do this on the fly. Does that help? Ya, thanks for the info.
Izzy BK wrote: Show quoteHide quote > We have a User object that is instantiated when the user authenticates > to the system that keeps track of what back end to communicate with. > That user object is handed off to every form as forms are opened. > Every middle tier business class requires a valid user object in the > New constuctor. > > By using this design, we can and sometimes do give the users the > ability to redirect to a different database without logging out. We > simply force all the open forms to shutdown and then we update the user > object with new server/database information. I have one application > where they occasionally want to connect to different databases > (sometimes connecting to a local database to work offline, or to a test > database to try something without affecting production). They can > easily do this on the fly. > > Does that help? Also, does setting IsDirty
> as you suggested prevent the automatic database update until you force What automatic update? Are you talking about the dataview accepting> it? changes and eliminating the old value. If so...No it wouldn't Setting up a boolean "IsDirty" property would simply let you know an update needs to happen, data has been changed in some way. I'm sure your doing something similar to this now? right? How are you currently telling your application that changes to data have happened and now an update is necessary? Izzy Kathy wrote: Show quoteHide quote > Izzy, > The Access app I am porting from is in use at multiple businesses where > there is a mix of databases sizes; some use Access and some use SQL > Server as backend db (hence my need to accommodate both). My users > require saving unlimited field change history (w/ old & new values, who > made the change, date/time change made, field changed, etc), so the > history can get rather large. I would prefer to capture the changes at > the same time a record gets saved versus as each field changes for > synchronization purposes. Just curious, why do you recommend against > comparing against the cached (old) values? Also, does setting IsDirty > as you suggested prevent the automatic database update until you force > it? Another contributor suggested saving the old value in each > control's Tag property. That seems workable, too. Another contributor > suggested subclassing, but I would lose the control's visibility in the > designer which I really don't want to do. > Thanks for the help. > Kathy > > Izzy wrote: > > Kathy, > > > > I had a nice long respone written for you....then my stupid computer > > over heated and turned off. > > > > Anyway...here's an idea: > > > > If this data is being stored in Access, you could insert the old data > > into the history table before you run your update. This will create a > > little extra duty for your database server but a very common task for > > audit tracking. > > > > I would try to avoid comparing each textbox's value with a cached > > initial value too determine if changes have been made. I usually just > > define a boolean property called "IsDirty" and on validating of the > > textbox controls set this property to true. You can then use that to > > determine if an update and insert needs to take place. > > > > If IsDirty Then > > > > MoveToHistory(PrimaryKey) > > UpdateTable(PrimaryKey) > > IsDirty = False > > Else > > > > 'No Changes Detected > > End If > > > > Just curious...How big is this Access database? AND how much history do > > you need too store? > > > > Izzy Kathy wrote:
Show quoteHide quote > Brian, Sorry for the late reply.> > I have set this up to work with either a MS Access or SQL Server > database. I think I've accomplished both issues now. Inside the LEAVE > event I'm comparing the old to the new value as suggested by Jim. > Inside the ENTER event I was storing the value in a global variable. > But I like your idea of using the Tag property for saving the old value > better than what I did. That way I can compare all the text boxes > during the binding source's CurrentChanged event. Does that sound > reasonable? > > Thanks ! > Kathy > > > Brian Tkatch wrote: > > There are two basic issues you are trying to address. > > > > 1) If it changed. > > 2) The old value. > > > > Before looking into a VB answer to this, if this is being ported from > > Access, you may now be using an actual database. In which case, you can > > add a TRIGGER to be fired ON UPDATE, and capture the change at the > > database level. > > > > If the answer is to come from VB, a simple answer would be set the Tag > > property to the old value as soon as the data is loaded. Then, when > > storing the new data, compare it with the stored old value. > > > > For mutiple textboxes, a loop could be employed. For example, to store > > the data: > > > > For Each Current_Control As Control In Me.Controls > > If Current_Control.GetType Is GetType(TextBox) Then > > Current_Control.Tag = Current_Control.Text > > Next > > > > HTH, > > B. Yes, it does sound reasonable. I think the Tag property is actually made for this. (Not sure what else it's for!) As for when to compare it, it should be done anywhere after the user has nothing else to enter, and before any updates to the table happen. *Which* event that happens in is mostly irrelevant, unless you want the UI to trigger (and handle) it at some specific point. I would think to (store and) check all the fields at the same time, however. Should be easier to maintain. B. 
Other interesting topics
check username and password in database
A question of design How to make stacktrace include linenumber for release compiles? REPLACE method: unwanted multiple-replacement Detect right-click in MDI container's client area? word length occurance in a text.. Encrypt a date to use in demo version System.Diagnostics.Process.Start Freezes Finding embedded controls? Visual Studio Proffessional 2005 |
|||||||||||||||||||||||
