SQl before edit

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
SQl before edit - RESOLVED in code

Is there a way to set up a SQL backend stored procedure that will not allow a record to be edited if the date is before a specified date?

It is an accounting type issue.
I want to have a table with one date in it (accounting date) that represents the date the books are officially closed for the month.
I want a procedure for the INVOICE table that will not allow any VB.net windows forms to edit records with a date < accounting date. Kind of a Before-Edit function that checks the date of the record.

I can do it in the VB.net program but I am having trouble with the grid forms and allowing some records to be edited and others to not be edited.

I will need to use the same functionality on the JOURNAL, TIME, and PARTIALS tables also. Doing it at the server level will help by not having to duplicate code over 8 similar forms.

Thanks for reading,
 
Last edited:

TechGnome

Well-known member
Joined
May 23, 2005
Messages
896
Programming Experience
10+
Well, you really should do it in the VB app.... that's a business rule and not a data rule...
If you do it in the SP, then people will still be able to edit the info on the screen, and then come looking for you when their data "disappears."

That said, yes you could do it in the SP, the first line of the SP (or rather ALL of your Insert/Update SPs) could easily query out a date from a table and dependign on its value compared to GETDATE() (the current system time on the SQL Server) stop the insert/update (by simply not doing it). You could also adda BEFORE UPDATE trigger on all your tables that cancel out the update based on the same logic.

Personaly, I'd put the lock down in the app.... it makes it easier to change later, and you never know when you'll need to directly manipulate the back end. If the data cannot change, then the user should be prevented from even trying to do so in the fron end.

Tg
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
Before Update trigger

Could you explain the concept of the Before Update trigger on a grid. Where and how do you accomplish this? What about multiple updates (several rows) before update? I'm not sure how I can individually check each row updated and allow some and not others.

Thanks for the help...
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
Locking cells of a dataGrid

I set a global variable to the AccountingDate and check it at the MouseUp of the grid...

VB.NET:
			If InvoicesGrid.AllowUpdate = True Then
			 If InvoicesGrid.Columns("Invoice Date").Value <= AccountingClosingDate Then
				 InvoicesGrid.Splits(0).DisplayColumns(0).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(1).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(2).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(3).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(4).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(5).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(7).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(8).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(10).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(11).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(12).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(13).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(16).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(17).Locked = True
				 InvoicesGrid.Splits(0).DisplayColumns(18).Locked = True
				    btnDelete.Visible = False
				Else
				    Dim n As Integer = 0
				 For n = 0 To 20 ' number of columns
					 InvoicesGrid.Splits(0).DisplayColumns(n).Locked = False
					Next
				    btnDelete.Visible = True
				End If
			End If
 
Top Bottom