how to get both old and new value for a changed text box

[Kathy]

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]

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

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

[Kathy]

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]

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

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

[Kathy]

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.

[Miro]

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.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.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.

[GhostInAK]

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.

[GhostInAK]

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

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.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.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.

[JR]

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:

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.

[Kathy]

JR wrote:

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

Thanks, 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

[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

[Brian Tkatch]

Kathy wrote:

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

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.

[Shane]

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

[BK]

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]

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.

[Kathy]

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:

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]

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

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

[mg]

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]

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

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?

[Izzy]

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:

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.

[mg]

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'.

[Kathy]

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]

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:

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

[Kathy]

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]

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:

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]

Also, does setting IsDirty

as you suggested prevent the automatic database update until you force
it?

What automatic update? Are you talking about the dataview accepting
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:

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]

Izzy,

>How are you currently telling your application that changes to data
have happened and now an update is necessary?

I was using the .HasChanged method.

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.

Those are some great suggestions. Yes I was using OleDb. So you think
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

[BK]

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.

[Izzy]

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:

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.

[BK]

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?

[Izzy]

Ya, thanks for the info.

Izzy

BK wrote:

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?

[Brian Tkatch]

Kathy wrote:

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.

Sorry for the late reply.

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.