Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Concurrency Violation When Updating Or Deleting Datarow


Hello! I'm new in VB so i'm having a problem while updating or deleting datarows.

After adding a new row and saving it, when i close the application and then open it again and modify then same row, it modify.
But when i add a new row and save it and then try to modify it or delete it, it gives me an error. The error when updating is:

System.Data.DBConcurrencyException was unhandled
Message="Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."
RowCount=1
Source="System.Data"
StackTrace:
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at Rainbow_Plastic.Form1.btnUpdateRec_Click(Object sender, EventArgs e) in C:Documents and SettingsaLi.MR-68ECC601BFB7My DocumentsVisual Studio 2005ProjectsRainbow PlasticRainbow PlasticForm1.vb:line 183
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at Rainbow_Plastic.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

and the error when deleting the same datarow is almost the same! I can update or delete other rows after adding a new row but can't modify the newly added datarow. Can anybody please help me in solving the problem?




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Deleting A Datarow In SQL Database Using VB.net
I'm a newbie at VB.net and SQL.
I have written some code to read data from, add data to, and delete data from an SQL database.

I am able to read the data.

I am able to add a record.

When I delete the record, I am deleting the record in the dataset, but when I try to update the database using the data adapter, it is not working. I'm not getting any error, it's just not updating the database.

Any suggestions?

Updating And Deleting
Hi...

got a question about updating records in my database...
i seem to have gotten it wrong because everytime i try to update a record it updates the very first one...

i'm using ADODC to update...

i did an SQLQuery to find the records... and then hoping to update them accordingly using adodc... but that fell trhough...

here's a snippet... of the code...

SQLQuery = "SELECT PatientID FROM PersonalDetails " _
& " WHERE PatientID " _
& " LIKE '" & PatientIDTxt.Text & "'"

rs.Open SQLQuery, DatabaseCon, adOpenKeyset, adLockOptimistic, adCmdText

AdodcCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " & App.Path & "DatabaseOrthoDatabase.mdb"

AdodcCon.RecordSource = "PersonalDetails"
AdodcCon.Refresh
With AdodcCon.Recordset
!IDNumber = ICNumberTxt.Text
!Address = AddressTxt.Text
!DOB = DOBTxt.Text
!Sex = SexCmb.Text
!Ethnic = EthnicCmb.Text
!Religion = ReligionCmb.Text
!Operator = OperatorRB(OperatorInt).Index
!ReferredBy = ReferredByTxt.Text
!FirstVisit = FirstVisitTxt.Text
!Consultation = ConsultationTxt.Text
!StartTreatment = StartTreatmentTxt.Text
!EndTreatment = EndTreatmentTxt.Text
!EndRetention = EndRetentionTxt.Text
!PatientsPicture = cmdOpenPicture.FileName
.Update
End With

ok what have i missed out here?

and as far as delete records go... it tells me to include the tables that i'm deleting from... but... i already have...

here's the SQLQuery

SQLQuery = "DELETE * FROM PersonalDetails, PatientHistory, ExtraOralExamination," & _
" IntraOralExamination, SocialFamilyHistory, Schedule" & _
" WHERE PatientID = '" & PatientIDTxt.Text & "'"
rs.Open SQLQuery, DatabaseCon, adOpenDynamic, adLockOptimistic

what's the problem here?

Thanks in advance...

~JoHn~

Mshflexgrid Updating/deleting/etc.
I have looked everywhere and have found some great tutorials (such as flyguys flexgrid, and some info on vbcity ( http://www.vbcity.com/forums/faq.asp...rids&#TID26683 )

However i have been unable to get it to work with my database.

The vbcity article above has a great example, but for some reason it errors out when i try it with my database (access).

So far i have been able to get the record to delete from the MSHFlexgrid but am unable to get it to delete from the record.

Here is the code i am using to open the database and delete the record:


Code:

Dim sConnect As String
Dim sSQL As String
Dim dfwConn As ADODB.Connection
Dim i As Integer
Dim j As Integer
Dim m_iMaxCol As Integer

pBuildMenus8

' set strings
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Password='';User ID=Admin;Data Source=\BS2c51200001sharedInternet SupportLandsafe TrackerLandsafe Tracker.mdb;Mode=Share Deny None;Extended Properties='';Jet OLEDB:System database='';Jet OLEDB:Registry Path='';Jet OLEDB:Database Password='';Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password='';Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"
sSQL = "select Branch,[Changed By],[Date Changed],ID,Password,UserName from [Landsafe Data Table]"

' open connection
Set dfwConn = New Connection
dfwConn.Open sConnect

' create a recordset using the provided collection
Set datPrimaryRS = New Recordset
datPrimaryRS.CursorLocation = adUseClient
datPrimaryRS.Open sSQL, dfwConn, adOpenForwardOnly, adLockBatchOptimistic

Set MSHFlexGrid2.DataSource = datPrimaryRS

With MSHFlexGrid2

.TextMatrix(0, 0) = "Branch"
.TextMatrix(0, 1) = "Notes"
.TextMatrix(0, 2) = "Date Changed"
.TextMatrix(0, 3) = "ID"
.TextMatrix(0, 4) = "Password"
.TextMatrix(0, 5) = "Username"

.Redraw = False
' place the columns in the right order
.ColData(0) = 0
.ColData(5) = 1
.ColData(4) = 2
.ColData(2) = 3
.ColData(3) = 4
.ColData(1) = 5

' loop to re-order the columns
For i = 0 To .Cols - 1
m_iMaxCol = i ' find the highest value starting from this column
For j = i To .Cols - 1
If .ColData(j) > .ColData(m_iMaxCol) Then m_iMaxCol = j
Next j
.ColPosition(m_iMaxCol) = 0 ' move the column with the max value to the left
Next i

' set grid's column widths
.ColWidth(0) = 825
.ColWidth(1) = 975
.ColWidth(2) = 1120
.ColWidth(3) = -1
.ColWidth(4) = 480
.ColWidth(5) = -1

' set grid's style
.AllowBigSelection = True
.FillStyle = flexFillRepeat

' make header bold
.Row = 0
.Col = 0
.RowSel = .FixedRows - 1
.ColSel = .Cols - 1
.CellFontBold = True
.RowHeight(0) = cmbFilter(0).Height
' grey every other row
For i = .FixedRows + 1 To .Rows - 1 Step 2
.Row = i
.Col = .FixedCols
.ColSel = .Cols() - .FixedCols - 1
.CellBackColor = &HC0C0C0 ' light grey
Next i

.AllowBigSelection = False
.FillStyle = flexFillSingle
.Redraw = True

End With

m_lCellRow = -1
m_lCellCol = -1
txtcell.BorderStyle = 0
txtcell.Visible = False
' set the text background color to the
' backcolor of the tooltip text to make
' the cell being edited having a different color
txtcell.BackColor = vbInfoBackground
MSHFlexGrid2.Cols = 10
MSHFlexGrid2.Rows = 150
Dim lCol As Long, lRow As Long

Randomize

tmrsearch.Enabled = True
tmrsearch.Interval = 500
With MSHFlexGrid2


m_lRowHeight = .RowHeight(0)
End With
I used code there cuz it was messing with smiley face icons..oops

There are a few extra things above, i have a search and filter event setup as well...those work fine.

Here is the delete button code:


Code:
Private Sub cmdDeleteMSHFlexGridRows_Click()
Dim i As Integer, intCount As Integer, intEnd As Integer

With MSHFlexGrid2
intCount = Abs(.RowSel - .Row)

'Set the intEnd variable to bottom row value
If .RowSel > .Row Then intEnd = .RowSel Else intEnd = .Row

'AbsolutePosition is 1-based and equals 1 when the current record is
'the first record in the Recordset.
'This loop always start from the bottom row
Do Until i > intCount
datPrimaryRS.AbsolutePosition = intEnd - i
datPrimaryRS.delete
i = i + 1

Loop

Set .DataSource = Nothing
.Refresh
Set .DataSource = datPrimaryRS

.Refresh
End With

End Sub


It deletes the record fine but doesnt update the actual database.

Im clueless, and have searched and searched before posting with no luck.

If anyone has any suggestions i would greatly appreciate it!

Adding, Deleting, And Updating. [*V-Urgent*]
Hello

I have a problem that l cannot solve, and need you help.

I have done everything on this project. But l am having a lot of trouble, adding, deleting and updating record using the datagrid which has a connection to the data environment.

I have sent my project with this, and hope you can understand. I have tried very hard to get the add, delete and update to work. I would be most grateful if one of you experts can help out with this.

In my project, all you have to solve is adding, deleting and updating the employee details, and save them to the database in the linkmonthEmployee table. under the correct month of the year.

Many many thanks for the person who can do this for me

Steve

Updating And Deleting Records At The Same Time
Hye everyone!
i really need help here. Actually, i want to delete a record from a table. let say it's table A. and at the same time, i want to save that record in another table. Let say it's table B. Please help me! for ur info, i'm using adodc only. pls assist me if u got any idea... thanx!

Updating/Deleting Records/Acess 2000 Database
Hi all,

I hope somebody can help me with this issue I am having. This is basically the last most complicated part of my project.

I have a form with a MSFlexGrid. On the MsFlexGrid, I populate data from an Access 2000 database. The user has the ability to edit,delete, and add more records to the MSFlexGrid.

When a record is updated or added, I am doing an insert or update to the databse; however, when the user deletes the record, since I do not have it on the MSFlexGrid, I do not have a way of referencing the deleted record to the databse to delete it.

Can anybody give me an idea on how to delete the record from the database once is not present on the MSFlexGrid.

I want to delete the records after the user finishes deleting all the records he/she wants. For example, if I had records 1,2,3,4 and records 1 and 2 were deleted, when the user clicks save those records will be deleted from the database.

Thanks in advance.

Jose

I Need Some Codes For Adding, Deleting, Updating A Record In SQL SERVER.
I need some codes for Adding, Deleting, Updating a record in a SQL SERVER as backend database with VB as a Front-End.

I just codes for the above.

Thank you!

Deleting/updating A Line Of Words In A Text File
Hi Fellows,

I'd like to ask for help with my problem. Although I have some idea, I'm not sure if it's the best or if it will work.

I want to have access to a text file. I already learn to create, read, write and delete a file. But want I want to achieve and learn is how to edit/delete a line of word(s) in a text file.

For example, let's say I have a file which contains...

line 1: The quick brown fox
line 2: Jumps over the lazy dog.
line 3: {newline}
line 4: Hello guys!!!

Let's say I want to delete the line 2. How will I do that? After deleting, I will save the changes.
Also, what if I will edit line 4. Instead of "Hello guys!!!", I change it to "How are you guys?".

I really need this feature in my application. I've done my research but I can't seem to find one.

Thank you and have a nice day!








Edited by - luv on 9/21/2004 8:13:26 PM

Writing/Updating/Deleting In A TextFile - A Line Contain Four Fields
Hello Guys,

I want to write from VB to a text file some records with 4 fields each.
Each record is supposed to be the name of a student and 3 exam's notes inserted in one line.

Suppose in the first line i will write:
$StudentName Mathematics Physics Chemistry

and in the other lines i will write, in each line, student name followed by the notes; each note being under the corresponding field above it.

From VB we assume that first we enter the student name, then the note for mathematics, then the second note for physics and the last note for chemistry.

Now when i will access from VB to the text file, if the line don't begin with a $, sometimes i will add a record (which is four fields as mentioned above), sometimes i will update a record for example updating just the physics note of a student; and sometimes i will delete a record. And mind that in lines that don't begin with a $ symbol.

How can i do this ??
Also, how can i FIX the position of each field in a line, so no overwrites will appear and each entering value will be EXACTLY under the predecessor value in the previous line ??
The text must be organized: Four fields at four FIXED positions.

Thanks for any assistance.

Adding, Deleting, Updating Datagrid Connected To Data Environment
Hello

I am using a datagrid, which is connected to the data environment. I would like to know the best way to add, delete and update record in the datagrid.

Some sample project would be most gratefull

Many thanks in advance

Steve

I Need Some Codes For Adding, Deleting, Updating A Record In ORACLE Database
I need some codes for Adding, Deleting, Updating a record in ORACLE database with VB as Front-End.

I just need codes for the above.

Thank you

Need Samply Code For Datagrid With Samples Of Editing, Updating And Deleting
I am trying my first program with a datagrid and I am having a ton of problems I have gotten the data to display in the grid. However, I am having trouble with the sequence of events involving editing the data during a modify and especially during a new record add. Does anyone know where I can find some sample code which involves a datagrid with updating, adding and deleting of records????

Thanks

Geod

 

Current Datarow
i´ve got a question,


i got a form with 4 textboxes on it and a datagrid and a save and exit button (only for testing)

now the code to fill the datagrid

Code:
Dim stSel_stamm As String = "select * from stamm where stammNr like '" & criteria & "%' or StammZeichnung like '" & _
criteria & "%' or StammBezeichnung like '" & criteria & "%'"
obDa_stamm = New SqlDataAdapter(stSel_stamm, mainconn)
obDa_stamm.Fill(obDs_stamm, "stamm")
mainconn.Close()
stliste.DataSource = obDs_stamm.Tables("stamm")


now i click on the datagrid and after i click i bind the textboxes to the data, after that click i
make some changes to the textboxes (changing the data). now if i dont move to another row, how do i
know if the data was changed and which row is affected?

How To Display Success Message After Successfully Inserting, Deleting Or Updating Database
-i have one command button on my form
- when i click this buttons it stores or update data frm textboxes and other controls in the form to the database.
-This i know what to do
-All i want to do is-
-when the record has been inserted or updated correctly then it gives success message in message box

thanks
saracjl

Sabin Kumar Chhetri

Mergings 2 Datarow[] Into A Datatable
datOrgRefIntg is a dataset
datCurRefIntg is a dataset
Anly certain records i want from these 2 datasets
i have used

DataRow[] dtROrgTbls = datOrgRefIntg.Tables[0].Select("TableName not in (" + strCurrDBTbl + ") ");

DataRow[] dtRCurTbls = datCurRefIntg.Tables[0].Select("TableName not in (" + strOrgDBTbl + ") ");

now i want to merge the rows from
dtROrgTbls and dtRCurTbls and put them unto a datatable how is it possible

How To Insert A New Datarow In My Sql Database?
Hey

I got some textboxes in my form which is connected to a table in my DB. Now I want to insert a new person in my database, the datatypes is like this:

ID - Integer
Licensenumber - Integer
Name - Varchar
Address - Varchar
Phone - Integer
Club - Varchar
Licensetaxes paid? - bit
Postnumber - Integer
Mail - Varchar
ClubID - Integer
Seedingnumber - Integer

If you don't understand what I'm trying to do, give me a hint and I try to explain better.

Thx!

Best regards
Shotmaker


 

[UNRESOLVED] Datatable Datarow Question
Please refer to my attached pic.
As you can see, I am using a Datagrid that is databound to a datatable. I need to implement Up and Down buttons to move the selected row up or down.

Please help.

Nachiket.

Edited by - nmehta6 on 10/20/2005 7:28:10 AM

{NOT RESOLVED}Deleting A Record And Updating Problem{NOT RESOLVED}
Hi,

     I have the problem that when I remove a record I cant update my DataRepor(Name:rpo). I have this code with the Command Button (Name:cmdDelete).

Code:Private Sub cmdDelete_Click()
Data1.Recordset.Delete
Data1.Recordset.Update
End Sub

But i get the error 3020: Update or CancelUpdate without AddNew or Edit. I know why this is because my code for a Command Button (Name:Addcmd works:

Code:
Private Sub cmdAdd_Click()
On Error Resume Next
Data1.Recordset.AddNew
Data1.Recordset.Update
End Sub


 The error is that updating a record when you have deleted somthing isn't supported but with you Edit or Add a Reocrd(s) it is. How do i get make it so that my Datareport actully gets updates when somthing is deleted, it works fine when you add somthing.

Thx






------------------------------------------------------------------------------
And when a man gets to heaven,
To saint Paul he'll tell:
1 more soldier reporting sir,
I served My Time in Hell
6 June 1944

2GB DDR400 RAM, 3.4Ghz EM64T P4 Socket 775, 200gb PATA HD 8mb cache, 500GB WD 16mb Cache (Master), 256mb 9950 ATI RADEON AGP ,19" LCD, and a 250W PSU

Running Vs6 Enterprise, running all vs8 products express edition

Edited by - thomas49th on 4/25/2005 10:01:07 AM

Possible Concurrency Problem
OS-Windows XP Pro SP2; VB 6 enterprise Edition

I am totally puzzled and stumped on a possible solution.

Scenario:
I wrote a couple applications for a small company. The two applications are called Part Tracker and Inventory. Part Tracker deals with the tracking of Purchase Orders and the parts ordered within the Purchase Order. Inventory deals with Qty on Hand, Qty on Order, etc. of the parts needed to do a specific job.
Not knowing any other database except for Microsoft Access, I used it to create the tables. I realize I should have used something else but now it's too late without a major re-write. So please on your replies, remember the database I AM using.

I used VisData (Microsoft Access Ver 7.0 mdb) to design (create the initial tables). Within the VB applications, I used ADO with SQL commands.

The system these applications were written and tested on is my personal PC at home. A single source PC, not connected to any other PC in a network. The applications work well.

These apps were installed on the user PC. The user PC's are set up in a network. The applications are installed on each user PC and the Database is on the Network server. THESE APPLICATIONS SHARE THE DATABASE TABLES. IT IS POSSIBLE FOR 2 OR MORE PC's TO BE ACCESSING THE TABLES CONCURRENTLY.

THE PROBLEM:
Once in a while when an update is done via either application, the update does NOT take place within the physical table. The only possible condition that I can think of is a concurrency problem. For example--when Part Tracker is updating a page in the Part Status Table (this is in the Inventory db) and some other user is using the Inventory application and grabbing the same page as the Part Tracker application is using. This is only a guess.

QUESTION:
Is there any code I can put around the SQL update statements(Insert, Delete, Update) that would cause the application to go into a limbo stage until the page is not in use?

Please if you have any other possible solution, let me know. Sorry to say, I don't have the time to re-write the applications using another database environment. I need to correct what I have in the shortest amount of time.

Thanks,
Sam

Concurrency/Multithreading
Hi,

I am using VB with Excel and I would like to know if multi-threading is possible?

I want to open a new workbook, give control to that new workbook, allowing the user to to performs operations on it, and run macros. Then when the user closes that workbook, give control back to the previous workbook, which then deletes the workbook it just opened.

My idea is to launch a new thread for opening the second workbook and suspending the initial thread, then when the second thread finishes, the first one is wakened and it then deletes the sesond workbook once it has been closed.

Can anyone give me any help on this, or give some sample code for running threads?

Also I would be open to any other suggestions as to how to do this.

Thanks
Andy

Concurrency Control
How do you guys normally control the concurrent activities? For example, if two users loaded the same record and both are editing it without knowledge of each other. Then both of them want to save it to the same database.

Logically, we will know that this will neglect data consistency. So how do I lock it, let the first one to save first while the second user waits and then notify the latter that the record has been updated and to refresh it?

Concurrency Control
Hello fellow VB programmers,

I was wondering how one tackled the problem of maintaining data integrity by some sort of concurrency control system. To be more specific, I have a hypothetical vb built forum-like system and want to prevent several or more staff members from altering a thread at the same time (for obvious reasons). All data is stored at the database level.

Any pointers or links to articles is much appreciated.

Application Concurrency
When I distribute my VB app' I want to use a concurrency licensing model.

Does anybody know how do implement this?

Possible Concurrency Problem

OS-Windows XP Pro SP2; VB 6 enterprise Edition

I am totally puzzled and stumped on a possible solution.

Scenario:
I wrote a couple applications for a small company. The two applications are called Part Tracker and Inventory. Part Tracker deals with the tracking of Purchase Orders and the parts ordered within the Purchase Order. Inventory deals with Qty on Hand, Qty on Order, etc. of the parts needed to do a specific job.
Not knowing any other database except for Microsoft Access, I used it to create the tables. I realize I should have used something else but now it's too late without a major re-write. So please on your replies, remember the database I AM using.

I used VisData (Microsoft Access Ver 7.0 mdb) to design (create the initial tables). Within the VB applications, I used ADO with SQL commands.

The system these applications were written and tested on is my personal PC at home. A single source PC, not connected to any other PC in a network. The applications work well.

These apps were installed on the user PC. The user PC's are set up in a network. The applications are installed on each user PC and the Database is on the Network server. THESE APPLICATIONS SHARE THE DATABASE TABLES. IT IS POSSIBLE FOR 2 OR MORE PC's TO BE ACCESSING THE TABLES CONCURRENTLY.

THE PROBLEM:
Once in a while when an update is done via either application, the update does NOT take place within the physical table. The only possible condition that I can think of is a concurrency problem. For example--when Part Tracker is updating a page in the Part Status Table (this is in the Inventory db) and some other user is using the Inventory application and grabbing the same page as the Part Tracker application is using. This is only a guess.

QUESTION:
Is there any code I can put around the SQL update statements(Insert, Delete, Update) that would cause the application to go into a limbo stage until the page is not in use?

Please if you have any other possible solution, let me know. Sorry to say, I don't have the time to re-write the applications using another database environment. I need to correct what I have in the shortest amount of time.

Thanks,
 

Concurrency Issues With VB
Hi, i've got a question about concurrency issues when using a VB program and MS access databases.

will there be any problem if multiple users access a VB program, located on a server, which is linked to a MS access database.

I am also using DataGrid in my VB program, but i disabled the allowadd,edit,delete,update for the datagrid.
i manually coded each step of editing the data.

Also, can multiple users use a Compiled and installed VB created program on a server?
so...from the experience of everyone here.. do you think there will be any problems?

MS Access Concurrency
Hi guys, please check this out and tell me if its a big issue or not..

click here

stumbled on it when looking for my datagrid error message..    

Concurrency Issue
I am designing an access application that allows a workgroup of salespeople to process through a table of leads simultaneously. It doesn’t matter who gets each lead only that they all process them in the same order and they do not get the same lead.

My strategy is to grab a record and immediately update it with information that would prevent it from being included in its own recordset. That is, I grab a lead and immediately update the “LastCalled” datetime field so when the next user runs the same SQL statement it will be excluded. If a record locking error occurs (someone else is getting the same lead) I recall the same function to get a different number in the error handler.

I originally had every user accessing the same .mdb file. Now I separated the .mdb into a client app (forms/queries/reports/modules) linked to a common data .mdb file (data only). Each user has there own .mdb file on their system linked to the common data .mdb.

However, in the fraction of a second that users are pull a new lead they are getting the same one. Hear is my code. Any pointers will be appreciated.

Public Function fGetNextPhoneNumber(CallBack As Boolean)

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSql As String

On Error GoTo err_handler

Set cnn = CurrentProject.Connection

If CallBack = False Then
        sSql = "SELECT TOP 1 * "
        sSql = sSql & "FROM qryDataSource_CallOrder "
        sSql = sSql & "WHERE ((CallStatus) Is Null Or "
        sSql = sSql & "(CallStatus)='NOCON' AND "
        sSql = sSql & "((DateDiff('h',[lastdialed],Now()))>5 Or (DateDiff('h',[lastdialed],Now())) Is Null))"
    Else
        sSql = "SELECT TOP 1 * "
        sSql = sSql & "FROM qryDataSource_CallBackOrder "
        sSql = sSql & "WHERE (((qryDataSource_CallBackOrder.CallStatus)='CALLB') AND ((DateDiff('h',[lastdialed],Now()))>5 Or (DateDiff('h',[lastdialed],Now())) Is Null) AND ((qryDataSource_CallBackOrder.CallBackDate)<=Date() Or (qryDataSource_CallBackOrder.CallBackDate) Is Null))"
End If

Set rs = New ADODB.Recordset
    rs.Open Source:=sSql, _
            ActiveConnection:=cnn, _
            CursorType:=adOpenKeyset, _
            LockType:=adLockPessimistic

If Not rs.EOF Then
    If IsNull(rs.Fields("FirstDialed").Value) Then rs.Fields("firstdialed").Value = Now()
    rs.Fields("lastdialed").Value = Now()
    rs.Fields("timesdialed").Value = rs.Fields("timesdialed").Value + 1
    rs.Fields("dialedby").Value = CurUserID
    rs.Update
    fGetNextPhoneNumber = rs.Fields("NightPhone").Value
Else
    fGetNextPhoneNumber = "NONE"
End If
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Function

err_handler:
    Select Case Err.Number
        Case -2147217864
            If CallBack Then fGetNextPhoneNumber (True) Else fGetNextPhoneNumber (False)
        Case Else
        MsgBox (Err.Number & " " & Err.DESCRIPTION)
    End Select
    
End Function


 

Concurrency Problem
Hello everyone!

I am designing one multiuser inventory system but I am completely confused with simulataneous access to data and other related problems.


Can anyone please help me ???



Thanks in advance.



Regards
Riyaz

MultiUser And Concurrency Problems
I have created a program which accesses the database found on the server.

Initially I have not cater for the concurrency problem and now I am facing a lot of problems with it but I still don't know how to make my system multiuser and concurrent(
i.e allow several user to access the same database but when one user is editing the record, the other users have read only permissions for that record.)


I have used an ACCESS database and my connection is ADODB


Any where in my program where I have inserted or edited the record, I have used the following codes

strSQL="Insert into........."
myconnection.execute(strSQL)

where myconnection is the connection that I have created to access my database.


If I have retrieve a recordset,I have used the following codes:

strSQL="Select * from ...."
set rs_Temp=myconnection.execute(strSQL)


What modifications should I make to these codes to make the system multiuser and concurrent?

Can you also give me sample codes
thanks

Concurrency, Locking Or Trigger?
Hi!

I need to implement the following task: Append a row to a SQL-Server database and immediately after that get the value of the identity autonumber of this newly inserted row. Now the problem is that there are potentially many users using this application at the same time as its interface is web-based. I would like to know how I can get hold of the autonumber which was inserted by the current user. It cannot simply be done by a query selecting max(id) because in the middle of inserting the row and getting the maximum id, there may have been another web-user who inserted another row. I think some form of locking needs to be implemented or a trigger, but I do not know how to go about it. I am using ASP.NET with Visual Basic.NET. I would greatly appreciate any help!

Thanks!
Timothy

How To Do Concurrency Control Using VB 6 And SQL-Server
Hi All

Please anyone tell me How to do Concurrency Control Using VB 6 and SQL-Server. please tell me all the steps....how to lock the table, how will I know someone is inserting some records or deleting...that time i have to lock the table.....

regards
Varun

Concurrency Error Code
Hi, I am trying to fix a concurrency issue. I am using VB6, and I want to handle the concurrency issue differently. Does anyone know what is the error code or description for concurrency issues?

Concurrency And Multiuser Problems
I have created a program which accesses the database found on the server.

Initially I have not cater for the concurrency problem and now I am facing a lot of problems with it but I still don't know how to make my system multiuser and concurrent(
i.e allow several user to access the same database but when one user is editing the record, the other users have read only permissions for that record.)


I have used an ACCESS database and my connection is ADODB


Any where in my program where I have inserted or edited the record, I have used the following codes

strSQL="Insert into........."
myconnection.execute(strSQL)

where myconnection is the connection that I have created to access my database.


If I have retrieve a recordset,I have used the following codes:

strSQL="Select * from ...."
set rs_Temp=myconnection.execute(strSQL)


What modifications should I make to these codes to make the system multiuser and concurrent?

Can you also give me sample codes
thanks

High Concurrency And AdLockPessimistic
I have VB application on client side and SQLServer on server side.

I have one table COUNTER with 3 columns:
Id (autonumber)
Counter (long)
DeskId (long)


What is the problem? I have many users (300) and they need counter in the same time (high concurrency). So one user takes counter 1 and update counter to 2, another user takes counter 2 and update to 3, and so on. I wander is my solution good, is it possible that 2 users take the same counter?

I have function Counter like this.

Public Function Counter(ByVal Desk As Long) As long

Dim rs As ADODB.Recordset
Dim Query As String
Dim ccon As New ccon

    Query = "SELECT * FROM Counter Where DeskId = " & Desk
    ccon.MakeConnection
    
    Set rs = New ADODB.Recordset
    
    With rs
        .CursorLocation = adUseServer
        .Source = query
        Set .ActiveConnection = ccon.cnn
        .CursorType = adOpenDynamic
        .LockType = adLockPessimistic
        .Open
    End With
    
    rs! Counter = rs! Counter + 1
    rs.Update
    
    Counter = rs!Counter

    Set rs.ActiveConnection = Nothing
    ccon.Conn_Terminate
    Set rs = Nothing
    Set ccon = Nothing
    Exit Function
    
        


How To Handle Concurrency Testing?
Hi,

I am creating an application in which VB is the front end & MS - SQL Server is the back end. I have a situation where I update the same field and the specific record by more than one user simultaneously.

I do use Begin Tran & End Tran. The lock used for update on VB side is AD Open Dynamic, AD Lock Optimistic & NT 2000.

No lock is used on the SQL end. Will these be enough or do I have to use any other lock on SQL side also? If so can you give me a sample code?

How do you take care of concurrency errros if it occurs?

Thank you. Would be of great help if you could direct me in this situation.

Regards,
Vickram



Edited by - avivic13 on 3/21/2005 8:31:43 AM

Concurrency In Accessing A Database
Given a situation where in two users are accessing the same record, can anybody please help me solve this problem of concurrency? For example, 2 users are accesing the Persons table. One is updating his address and the other is deleting the same record, how do i solve this?

any ideas? i'd greatly appreciate it thanks!

How To Handle Concurrency On Access Using ADO
How do i handle concurrent update,modifications,additions on a multi user system using ADO with Microsoft Access 97 database. Also suggest me some solution if possible by which i can know the status of the records if other users are currently modifying/adding/deleting a record, using ADO without firing any additional queries on the database with some ADO flags?

Dealing With Concurrency Using Web Form Insert
I want to learn how i can deal concurrency when creating
a web form to insert data to sql server db.Thanks

BUG? Optimistic Concurrency Check Don't Work When BLOB Field Read After Row Modificat
Hi, here is the problem.



SYSTEMS INVOLVED:

Sql Server 2000, VB 6, ADODB

SYMPTOM:

Recordset opend with settings:

tb.LockType = adLockOptimistic
tb.CursorLocation = adUseServer
tb.CursorType = adOpenKeyset

When I open recordset using above settings (just 1 record), then change that record in other application(eg. using Management Studio), then get back to vb and just read any blob field (text, ntext) and modify any field in the same record then at the very end execute tb.Update operation is succesful which is wrong, because we should get an error: "Optimistic concurrency check failed. The row was modified outside of this cursor." If you pass over the "read any blob field" step it works as it should (operation fails with above error).



CODE TO REPRODUCE:



Sub Test()

    Dim SqlConn As New ADODB.Connection
    Dim tb As New ADODB.Recordset
    tb.LockType = adLockOptimistic
    tb.CursorLocation = adUseServer
    tb.CursorType = adOpenKeyset
    
On Error GoTo Error
    SqlConn.ConnectionString = "Provider=SQLOLEDB.1;User Id=sa;Password=<password>;Initial Catalog=Northwind;Data Source=<server>"
    SqlConn.Open
    tb.Open "SELECT * FROM Categories WHERE CategoryId = 1", SqlConn
        
    tb.Fields("CategoryName") = "A"
    
    MsgBox "Now modify and commit changes to this record in separate application, when done click OK."
    
    res = MsgBox("Read blob field (Yes, concurency check doesn't work), No (does work)", vbYesNo)
    If res = vbYes Then
        tmp = tb.Fields("Description")
    End If
        
    tb.Update
    
    MsgBox "Update successful!"
    SqlConn.Close
    Exit Sub
Error:
    MsgBox Err.Description + vbCrLf + Err.Source, 16

End Sub



I'm aware that blob fileld is handled in a special way, it is not stored in record's data page it belongs to (unles we specify in row option).

If anyone had this problem or found confirmation that it is the bug, please let me know or maybe it's not the bug and there is some justification of such behaviore ....

Thank you for any reply !



Tomek

BUG? Optimistic Concurrency Check Don't Work When BLOB Field Read After Row Modificat
Hi, here is the problem.



SYSTEMS INVOLVED:

Sql Server 2000, VB 6, ADODB

SYMPTOM:

Recordset opend with settings:

tb.LockType = adLockOptimistic
tb.CursorLocation = adUseServer
tb.CursorType = adOpenKeyset

When I open recordset using above settings (just 1 record), then change that record in other application(eg. using Management Studio), then get back to vb and just read any blob field (text, ntext) and modify any field in the same record then at the very end execute tb.Update operation is succesful which is wrong, because we should get an error: "Optimistic concurrency check failed. The row was modified outside of this cursor." If you pass over the "read any blob field" step it works as it should (operation fails with above error).



CODE TO REPRODUCE:



Sub Test()

Dim SqlConn As New ADODB.Connection
Dim tb As New ADODB.Recordset
tb.LockType = adLockOptimistic
tb.CursorLocation = adUseServer
tb.CursorType = adOpenKeyset

On Error GoTo Error
SqlConn.ConnectionString = "Provider=SQLOLEDB.1;User Id=sa;Password=<password>;Initial Catalog=Northwind;Data Source=<server>"
SqlConn.Open
tb.Open "SELECT * FROM Categories WHERE CategoryId = 1", SqlConn

tb.Fields("CategoryName") = "A"

MsgBox "Now modify and commit changes to this record in separate application, when done click OK."

res = MsgBox("Read blob field (Yes, concurency check doesn't work), No (does work)", vbYesNo)
If res = vbYes Then
tmp = tb.Fields("Description")
End If

tb.Update

MsgBox "Update successful!"
SqlConn.Close
Exit Sub
Error:
MsgBox Err.Description + vbCrLf + Err.Source, 16

End Sub





I'm aware that blob fileld is handled in a special way, it is not stored in record's data page it belongs to (unles we specify in row option).

If anyone had this problem or found confirmation that it is the bug, please let me know or maybe it's not the bug and there is some justification of such behaviore ....

Thank you for any reply !



Tomek

Access Violation
I have made a little app that opens word and then saves it as a .html file. It works fine on the machine i created it on, but if i install it onto another, it get a "Access Violation" Error at this line.

mywrd.Documents.Open App.Path & "my_files est.htm"

Exe Sharing Violation
I have an exe I created using VB 6. The exe resides on our network. I have made some modifications and now I am trying to update the copy on the network. I know nobody is using the program; however, I am getting a sharing violation error whenever I try to copy over the version on the network or delete the version on the network. Why would I be getting this error and how do I resolve it so I can copy my file. Does the server need to be rebooted? Is there any other way to release my exe? Thanks for your help.

Weste

Access Violation
hi

When i use a my own custom control (which is developed in Delphi) during design time, i am getting error
"Access Violation at address xxxx19EA in module "xyz.OCX".
Read of address 000002CC".

Same, error message is coming during loading the form also, which has this control.

Help: About Access Violation
I have a DLL to be used under Visual Basic, and the DLL was done in C(using Visual C++). When I compile the project in Visual Basic, it works. But after I made a .EXE file in VB and ran it. The system said: "Unhandled exception in *.exe(*.Dll): 0xC0000005: Access Violation. " And it was the same when I debugged it in Visual C++(6.0) environment. Do you have any idea?
Thank you.

Key Violation In Delete
Hi,

in my program user is allowed to delete some records from a table that is in relationship to others. Of course, the records which have references in other tables as foreign keys, can't be deleted and I don't want users to delete them.
But I want to give an information to user why he can't delete these records. So I tried to create a stored procedure like this:

create procedure delData

as

delete from vec where id=5

if @@Error=547
Return -1
else
Return 0


But, the problem is, that in my VB6 program I cannot read the return value because an exception is throwed.
So, does anybody know how to disable throwing an error in the stored procedure (something like SET @@Error=0) ?

Thanks

Dir$() - Sharing Violation
Ok, this is wierd (or maybe I'm a dunce)

Objective: decide whether or not to delete a folder.
I test for files in a Folder, if (at least) one file exists, I don't delete the folder; whereas if there are no files I delete it.

I use the following:

VB Code:
Private Sub Command1_Click()    If Dir$("C:TestBruce*.*", vbNormal) = vbNullString Then        Debug.Print "Folder Empty"    Else        Debug.Print "File(s) Exist"    End IfEnd Sub


If there is a file, I get the Debug (which is expected). But, if I try to (manualy) delete the Folder, I get a 'Sharing Violation'! - Contradicory to my Objective, but I has me wondering whay I can't delete a Folder once I run this code.......

Here is the funny thing, If I rem out the else, It can delete the Folder???


VB Code:
Private Sub Command1_Click()    If Dir$("C:TestBruce*.*", vbNormal) = vbNullString Then        Debug.Print "Folder Empty"'    Else'        Debug.Print "File(s) Exist"    End IfEnd Sub


There must be an instance of the Folder open somehow post Dir$().

I noticed this has been asked before, but no answer has been given.

(Please note, I'm not after a workaround/alternate method, just wondering what VB is doing in this regared)

Cheers,

Sharing Violation
Hi

Is it possible to kill Sharing file (open by other app...) ??


Thanks!

Share Violation
How can you check if a directory has a Share Violation within VB or VB script.

Acces Violation
I'm installing my project, but I recibe the following error message:

C:windowssystemmsvcrt.dll

"An access violation occurred while copying the file"

How can I fix it?

I really need help Thank

Copyright 2005-08 www.BigResource.com, All rights reserved