May 28, 2011 at 1:34 am
Aoa. Im new to sql server 2005 and Visual Basic 2008. Actually i have developed an application. On every form in vb program i have put formclosed event to update a field value which is bound with dataset while form is closed. This is working perfect, but problem is that if my application crashes or system restarts while electricity shutdown, formclosed event doesn't execute. so what to do to cope this problem......
is there any way in vb 2008 to solve the problem???
or any script in SQL Server 2005 that detects that particular illegally closed application and update that value which was the duty of vb application to update.
Is there any way in Sql Server 2005 that I can check the status of client connection whether the connected vb application is running or crashed. if crashed then update a particular table's field value.
May 28, 2011 at 10:05 pm
There is no way sql will be able to tell if your application crashes or not. I have to say it sounds like a poor application design the requires a sql statement when each form closes. Maybe if you tell us what you are trying to accomplish we can come up with a better way of handling whatever you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2011 at 11:48 pm
Actually i need to use Pessimistic Locking with strongly typed dataset...
so i have designed my own record locking mechanism that works when a user click edit button. Means when a user clicks the edit button on particular record, other users wont be able to update the record.
I have used a "flag" string type field in table. when a user clicks on edit button. flag is set to "USE" and updated in database. means edit button disables for other users. and when the particular user done his works and click on save or cancel button. flag is set to "FREE" and updated in database. so now that particular record is free for other users.
My mechanism is working perfect. For Example. A user is working on particular record and clicked Edit button. that means flag is set to "USE". so that particular record is locked for other users. now the particular user's application crashes. what happened. now the Flag is set to "USE" forever.
that the deadlock. because i have set Flag to "Free" on Cancel and Save button and on FormClosed event. but unfortunately all three options will not execute because the user has crashed......
i have discussed all my views however if ur goodself require any further explanation plz ask me to explain. plz help me to solve it... or suggest me some other best method for pessimistic locking in Strongly Typed dataset.
Thanks is advance
May 29, 2011 at 10:14 am
If you were to also update a "LastModifiedOn" column for the row when you set the flag to "USE", you could mod your program to check for the time difference between that column and the current date/time. If it's been something more than x minutes (for example) but the flag is still set to "USE", you could have the front end ask if you want to override or not.
That would only take care of things if someone tried to update the row in question. If you want to "clean up" the whole table for such problems, you could run a schedule job every couple of minutes to reset the "use flag" after x minutes have passed since the "LastModifiedOn" date/time.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2011 at 7:24 am
Another thing you might consider is to use NULL and [UserID] instead of using "free" and "Use". This will provide you the same information about IF it the record is locked but additionally would tell you WHO has it locked. I agree with Jeff that you might consider adding a lock time so you have an idea of how long something has been locked. I have written applications like this in the past and they are full of management problems because stuff gets locked and you will have to build a utility to unlock records. More often than not I have found that while it is possible it is rarely worth the huge amount of headaches to keep everything working smoothly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2011 at 10:40 pm
Thanks Sean Lange
If this solution is not worthy to implement then what can i do?
plz suggest me good idea to implement on my application.
Actually i want Pessimistic Locking with Strongly Typed Dataset
Plz help me. I shall be very thankful to you......
thanking in advance
June 1, 2011 at 7:03 am
I certainly wouldn't say that it is not worthy of being implemented. I was really just warning of the challenges you may face. My wife's company built a system with this type of locking and they are constantly wrestling with unlocking records.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply