Two computers updating sql dbase

[Johalak08]

Please help.

I have a VB application using sql dbase. The application is installed in two computers. I am having problems when the 2nd computer is updating the dbase and/or both computer accessing the dbase. Do i need to lock the sql dbase? Please help on how to do this.

Currently, I have this..
Form_Load()
Set adoCOnnection = New ADODB.Connection
Set adors = New ADODB.Recordset
connectstring = "Provider=SQLOLEDB.1......."
adoCOnnection.Open connectstring


cmdSave_Click()

adors.CursorType = adOpenDynamic
adors.CursorLocation = adUseClient
adors.LockType = adLockOptimistic
adors.open "SELECT * FROM.....", adoCOnnection
Dim query as String
query = "INSERT INTO .......... VALUES...."
adoConnection.Excute query
adors.Close

Any help is greatly appreciated. Thank you

 

[gmmastros]

How many rows are you inserting in your Save click event? Just one?

Also... Why are you using OpenDynamic and LockOptimistic ? Those setting would make sense if you were going to be updating (using ADO), but you are building an insert string.

cmdSave_Click()

   adors.CursorType = adOpenForwardOnly
   adors.CursorLocation = adUseClient
   adors.LockType = adLockReadOnly
   adors.open "SELECT * FROM.....", adoCOnnection
   Dim query as String
   query = "INSERT INTO .......... VALUES...."
    adoConnection.Execute query
   adors.Close

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Johalak08]

I can insert approx. 1-10 records everytime i click the Save button.

Thanks for the reminder regarding when using INSERT. Since i'm using INSERT, is there a way to lock the sql dbase while the other pc is inserting/adding records? Or at least a inform the other user (from PC2) that the dbase is being update?

Thanks.

 

[Andrzejek]

Why do you want to "lock the sql dbase"? You are not updating any records, you are Selecting and Inserting records.

I am having problems when the 2nd computer is updating the dbase and/or both computer accessing the dbase

What kind of problem? Any errors?

Have fun.

---- Andy

 

[gmmastros]

You are not updating any records, you are Selecting and Inserting records.

Even with selects and inserts, you can end up locking the table(s). Since he was using an updateable cursor, I believe locks are placed on the select. Even with the inserts, SQL will lock the data page (8k chunk of data) for the page that is getting inserted to.

Johalak08, when this happens, I would encourage you to open SQL Server Management Studio and a new query window and run the following:

sp_who2

When you run this, you will see a list of all active connections. Many of the connections (with spid < 50) are sql server connections that you can safely ignore. However, there is going to be a BlkBy column. For most of the connections, there will not be anything in this column. But, you may notice a number in one (or more) of the rows. If this happens, I'd like you to copy/paste the output of sp_who2 here. I'll help you interpret the results and hopefully narrow down the problem.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Johalak08]

Error: Cannot insert duplicate key row in object 'table1' with unique index 'description_index'


My dbase is indexed. I encounter this error everytime i use two computers. I want to get rid of this error maybe just inform the PC2 that the dbase is being used and PC2 can save records after PC1 is finished.

Btw, table1 fields include system date (date_in) and system time (time_in). I also have..
adors.Open "SELECT * FROM table1 order by date_in, time_in, description", adoCOnnection

Thanks again.

 

[gmmastros]

Run this in a query window and post the results:

SELECT i.Name, C.Name, T.name
FROM   sysindexes i
       join sysindexkeys k 
         ON i.id = k.id
         and k.indid = i.indid
       join syscolumns c 
         ON k.id = c.id
         and k.colid = c.colid
       join systypes T
         On c.xtype = T.xtype
WHERE  k.id = Object_ID('table1') --change this to your table name(s)
       And i.Name = 'description_index'
ORDER BY i.name,k.keyno

It's not the index that is causing the problem. It's the fact that your index is unique. Now... if the index does not need to be unique, then the quick fix is to change the index. However, I suspect there is more going on here than meets the eye.

Also, how long does it take to insert these 1-10 rows? Ideally, it would be faster than you can blink. But, I suspect you may be doing this in a loop, and the VB code is slowing you down. Without seeing more of the VB6 code (and the information I posted above), it's hard to give advice.

Please, post the results of the query above, and more of the code from the cmdSave_Click event.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Johalak08]

Hi gmmastros:

First of all thank you so much for the help. I really need this to be solved. You are right, inserting and then printing 10 rows is very slow which is the main problem right now besides the indexing.

The original code cmdSave_click:

Dim num, lastnum As Integer
Let num = 0
Do While num < Val(txtNumPieces.Text)
adors.Open "SELECT * FROM location order by location_no", adoCOnnection
adors.Find "location = '" & False & "'"

If adors.EOF = True Then MsgBox "No more location spot open!", vbInformation, "Delivery Driver"
Else
Text2.Text = adors.Fields("location_no").Value
Set adors = adoCOnnection.Execute("update location SET location.location = 1 WHERE location.Location_no = '" & Text2.Text & "'")
End If

Let num = num + 1
adors.CursorType = adOpenDynamic
adors.CursorLocation = adUseClient
adors.LockType = adLockOptimistic
adors.Open "SELECT * FROM package_label order by date_in, time_in, description", adoCOnnection
adors.MoveLast
lastnum = adors.Fields("det_description")

adors.AddNew
adors.Fields("account_no") = txtclientaccountno.Text
adors.Fields("oaraname") = txtclientname.Text
adors.Fields("driver_no") = txtDeldriverno.Text
adors.Fields("date_in") = Format(Now(), "short date")
adors.Fields("time_in") = Format(Now(), "long time")
adors.Fields("status") = "true"
adors.Fields("print_status") = 0
adors.Fields("det_description") = Val(lastnum) + 1
adors.Fields("storage_locationID") = Text2.Text
adors.Fields("description") = "PZ" & Format(Now(), "mmddyy") & "00" & Format(adors.Fields("det_description").Value, "00000000")
adors.Fields("waybill_no") = "WB" & Format(Now(), "mmddyy") & "00" & Format(adors.Fields("det_description").Value, "00000000")

'Get the Login User Name
GetUserName lpBuff, Len(lpBuff)
sUser = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
lpBuff = ""
'Get the Computer Name
GetComputerName lpBuff, Len(lpBuff)
sComputer = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
lpBuff = ""
adors.Fields("user_name") = sUser
adors.Fields("computer_name") = sComputer

adors.Update
adors.Close
Loop
frmPrintPackageLabel.Show
End Sub


THe result of the query:

Name
description_index description nvarchar
description_index description sysname

(2 row(s) affected)

 

[gmmastros]

There are several potential problems I see here.

1. Integer

Stop using integer for your data types. Instead, use Long. An integer maxes out somewhere near 32,000, but a Long allows you many more values. Strongm pointed out in another thread that Long's actually perform better, too.

2. When looping, it's best to do as much OUTSIDE the loop as possible. For example, you get the user name and computer name inside the loop. This is not likely to change during the loop, so only get this information once.

3. This code does not scale well at all. Not even a little. You see, you create multiple recordsets that returns ALL of the data from a couple different tables. This is likely to perform well when these tables are empty (or nearly empty), but the performance will kill you when there is more data in the tables. In particular, look at the way you use the package_label table. You are returning ALL of the data from this table, and you even order it based on three different columns, but you don't use this data anywhere (except the det_description column). What a waste.

Mostly.... I think a few minor changes will have a big impact on performance.

Try this:

    Dim num [!]As Long[/!], lastnum As Long
    [!]Dim sUser As String
    Dim lpBuff As String[/!]
    
    Let num = 0
    
[!]    'Get the Login User Name
    GetUserName lpBuff, Len(lpBuff)
    sUser = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
    lpBuff = ""
    'Get the Computer Name
    GetComputerName lpBuff, Len(lpBuff)
    sComputer = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
    lpBuff = ""
[/!]    
    Do While num < Val(txtNumPieces.Text)
        adors.Open "SELECT * FROM location [!]Where location = 0 [/!]order by location_no", adoCOnnection
      
        If adors.EOF = True Then MsgBox "No more location spot open!", vbInformation, "Delivery Driver"
        Else
            Text2.Text = adors.Fields("location_no").Value
            Set adors = adoCOnnection.Execute("update location SET location.location = 1 WHERE location.Location_no = '" & Text2.Text & "'")
        End If
        
        Let num = num + 1

        adors.CursorType = adOpenDynamic
        adors.CursorLocation = adUseClient
        adors.LockType = adLockOptimistic
        adors.Open "SELECT [!]Top 1 [/!]* FROM package_label [!]order by det_description DESC[/!]", adoCOnnection
        adors.MoveLast
        lastnum = adors.Fields("det_description")
        
        adors.AddNew
        adors.Fields("account_no") = txtclientaccountno.Text
        adors.Fields("oaraname") = txtclientname.Text
        adors.Fields("driver_no") = txtDeldriverno.Text
        adors.Fields("date_in") = Format(Now(), "short date")
        adors.Fields("time_in") = Format(Now(), "long time")
        adors.Fields("status") = "true"
        adors.Fields("print_status") = 0
        adors.Fields("det_description") = Val(lastnum) + 1
        adors.Fields("storage_locationID") = Text2.Text
        adors.Fields("description") = "PZ" & Format(Now(), "mmddyy") & "00" & Format(adors.Fields("det_description").Value, "00000000")
        adors.Fields("waybill_no") = "WB" & Format(Now(), "mmddyy") & "00" & Format(adors.Fields("det_description").Value, "00000000")
        adors.Fields("user_name") = sUser
        adors.Fields("computer_name") = sComputer
   
        adors.Update
        adors.Close
    Loop
    
    frmPrintPackageLabel.Show

Also, notice this line:

Dim [!]num[/!], lastnum As Integer

Written this way, num will be a variant (not an integer like you think).

There are other efficiencies to be had here, both with the database and the VB code. For now, I encourage you to implement these suggestions.

Can you please post back with the time it takes now (with your code) to run, and then the time it takes with the suggestions I've made. I'm curious.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Johalak08]

ok..i'll try it and i'll update you
Thanks again

 

[Johalak08]

i added one record and the resulting det_description = 10000 but when i checked all records in the dbase the very last record has det_desciption = 24414. Thus, the record i added should be 24415

Btw, it was faster now with the modifications you gave me. Thanks

 

[Johalak08]

Hi gmmastros:

I changed the det_description data type from nvarchar into numeric (sql). Is it right?

It is working and faster. I still have to test the program in the site and i'll update you asap.

Thanks.

 

[gmmastros]

There are many different data types available in SQL Server. nvarchar is used to store unicode aware strings. Numeric is used to store numbers (usually with fractional components). In this case, sounds like you really want to use an INT.

In fact, you could change the det_description to be an identity column because it seems like you are using it that way, anyhow.

You see, there is still a problem with this code. Making the code execute faster will minimize the chances for a problem, but not necessarily eliminate it.

Let's this about what happens here...

You get the max det_description from the table. Add 1, and then use this for the det_description for the newly added row. The problem is.... each statement takes a certain amount of time to execute. So, it is technically possible for your app to get the last det_description, and then (from another application) a new row could be added that you don't know about. You then try to insert a new row based on old, outdated information.

Instead, you could make the det_description column be an Integer Identity column. With an identity column, you usually specify the seed and the increment. The seed value is the first number that is used (usually 1). The increment determines what the next number is (also, usually 1). When you have an identity column, you do not insert data in to this column. Instead, the database determines this for you. Done this way, you will not need to worry about multiple applications inserting data simultaneously.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Johalak08]

Yes. It makes sense.

Forgive me for being so ignorant but isn't it INT data type has max length of 4. Dont you think my application will exceed the maximum value allowable for an INT? Right now, the very last value of det_description is 24480.

 

[gmmastros]

When you look at the column properties in SQL Server Management Studio, it shows Size = 4. This is true.

What this really means is that it takes 4 bytes to store this data. The range of values you can store in an int is:

-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

This brings me to another point I made earlier. Originally you had:

[tt][blue]Dim num, lastnum As Integer[/blue][/tt]

I advised you to change this to Long instead of Integer. I really hope you took my advice because a VB6 Integer is the same as a SQL Server SmallInt. They both use 2 bytes to store the data and have a range of:

-2^15 ([!]-32,768[/!]) to 2^15-1 ([!]32,767[/!])

Right now you are at 24,480. It won't be long before you exceed the acceptable range of a VB6 Integer. It's really important that you change the data type now, before it causes an unexpected error in your application.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Johalak08]

I religiously followed your advise earlier. Thanks again.
I'll keep you posted when i test the application in the site.

 

[Johalak08]

Hi gmmastros:

I tested the updated program and yes it is very fast now. Thanks again.

I'm just curious will the "Error: Cannot insert duplicate key row in object 'package_label' with unique index 'description_index" still exist if two computers will be running the program and updating the dbase?

Sorry but i haven't tested this scenario yet.

Thank you.

 

[gmmastros]

Yes. It's still possible. What we've done here is to reduce the likelihood of this error, but it can still happen.

The problem is when the app, running in two separate processes, click the same button at the same time. Imagine this.... Suppose it takes a minute to insert all the rows. 2 people would need to click the same button within the same minute in order for the error to occur. Now suppose the whole process takes 0.05 seconds. Now, in order for the error to occur, both people would need to click the same button within the same 50 millisecond interval. This makes the problem less likely to occur.

The real problem will be more difficult to correct. The problem is really based on the way you are handling your data. Doing this properly would have been relatively easy if you had designed it the correct way from the beginning. Now.... the cat's already out of the bag, so it will take a bit more effort to fix.

You see, you have a column in your table that is behaving like an identity column, without the benefit of actually being an identity column. Most (if not all) databases have an identity column (but probably named differently). In Access, it's called an AutoNumber. In SQL Server, it's called an Identity column. Basically, this is a property of a column in a table. Each table in your database can have one (and only one) identity column. When you insert data in to a table with an identity column, you do NOT insert data in to the identity column. It will get it's value automatically.

For example (copy/paste this to a query window):

Create Table TestIdentity(Id Int Identity(1,1), Value VarChar(20))

Insert Into TestIdentity(Value) Values('Blue')
Insert Into TestIdentity(Value) Values('Green')

Select * From TestIdentity

Drop Table TestIdentity

When you run the above code, you will see that the ID column has values in it even though you did not specifically put any number in it. In fact, the values will be 1 and 2. This occurs because of Identity(1,1) The first 1 in the parameter identifies the starting value, and the second number identifies the increment.

I've read your code several times. I'm pretty sure that you could probably write a stored procedure that handles all of this in one shot. The stored procedure will certainly run faster still, but also allow you to prevent this problem for ever occurring. But... it's all based on changing the det_description column to an identity.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Johalak08]

Thanks again. You really helped and i learned a lot from you.

As of now, i'll try to write a stored procedure that will prevent the error from happening again. I haven't done it before but i'll try it.

Btw, do you recommend any good reading materials this?

Thank you.