Solved

VB.Net Windows App - SQL Server Multiple Users

Posted on 2004-09-08
18
436 Views
Last Modified: 2008-01-09
Here's my issue:
I've designed an app where there 2-3 people using the same program at the same time, accessing the same data.  It works great for the most part. The problem is, I have one person (plant) who runs the program all day - so on the entry screen  the same data sits in the dataset, the user adds records - isn't allowed to change older records. However, I have another user that goes in, checks the data that they enter, then verifies the record.  Once it is verified the plant user cannot change the record. But since they don't close down the program the old (non verified) entry is still in their dataset.  So sometimes they go back and change a record that has been verified already.  Therfore screwing up the data.

1)So is there a way that I can refresh the dataset every half hour without screwing the user up in the plant - meaning, they could have a record they are in the process of adding and if it refreshed while they were doing this it would be traumatic??  The plant users are VERY computer iliterate

2) Would it be better if they see no previous data when they start up, and then they can add and edit the records they added. But how could I do this without changing a whole bunch of code???

3) Any other suggestions???
0
Comment
Question by:Tigger996
  • 9
  • 9
18 Comments
 
LVL 3

Expert Comment

by:RacinRan
ID: 12027384
Well, for item 1:  you can add a timer control and set the interval to 1/2 hour and refresh.  You will probably have to add some code to capture where the user is planted and relocate them to that point after the refresh.  Timer code looks like this:

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        timer.Interval = 1,800,000  'time is in milliseconds so that's a half hour, commas added for readablility
        timer.Start()

    End Sub

    Private Sub timer_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles timBreak.Tick

    ' refresh code here
     
    End Sub


On Item 2:  You could allow you users to do this but you would not want that data to sit in the dataset all day.  If a illegal exit occurred for any reason the data would be lost.  So you will have to send it to the database which means you will need a way to track which records were entered by the plant.  You could use the ID of the records to keep sync'd up but it would probably be easier to add a userID or status type field to the table and query for records entered by the plant and then update the userID/status later after you don't want them altering the record any more.

Hope this helps
0
 

Author Comment

by:Tigger996
ID: 12029025
Well I actually update the database when they save the record or create a new record.  So maybe while it's saving, update the database, then reload the dataset, and if then move to the record they were at, which is typically the last record. I'm going to try this out and see if it works. If it does I'll reward you points for the suggestions.

Tigger996
0
 

Author Comment

by:Tigger996
ID: 12045845
Ok, I tried to clear the dataset and reload, but since there are a lot of records, it is very slow.
What could be an easy way - just for plant users (I have a setting in my app.config file) is to load just the current day's records into the dataset.

But I'm having trouble with that part.  

Just for some info:

There's over 3000 entries in this table. I'm using the same app with a different version setting (either plant or admin) to determine what they can do( edit Add Update...)

The plant guys have the program running all day... They typically just add new records or change stuff that they've added.  The admin person uses the program to check their entries from the days before and makes them verified if everything is ok.  Once it is marked verified, the plant cannot change the record.  However, since they leave their program running all the time, their dataset doesn't have the changed records from when the admin person was working on it.  So they could change verfied records.

This is why I need a quick and easy way to keep the plant version current without causing too much time or trouble.  

HELP! :)

Tigger996
0
 
LVL 3

Expert Comment

by:RacinRan
ID: 12046546

How are you loading the dataset?  A datareader is the fastest way to load a dataset.  I can get 3000 records into a datatable which displays in a datagrid in about 1 second.  Here's how:

            Dim dt As New DataTable

            With dt
                .Columns.Add("col1")
                .Columns.Add("col2")
                .Columns.Add("col3")
                .Columns.Add("col4")
            End With

            Dim cmd As New SqlClient.SqlCommand("Select top 3000 * from myTable")
            Dim conn As New SqlClient.SqlConnection("Data Source=<DataSource>;Integrated Security=True;Initial Catalog=<Database>;")

            conn.Open()

            cmd.Connection = conn
            Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader()

            Do While dr.Read
                With dr
                    Dim row As DataRow = dt.NewRow

                    row("col1") = dr.GetValue(0)
                    row("col2") = dr.GetValue(1)
                    row("col3") = dr.GetValue(2)
                    row("col4") = dr.GetValue(3)

                    dt.Rows.Add(row)

                End With
            Loop

            dgRecords.DataSource = dt


0
 
LVL 3

Expert Comment

by:RacinRan
ID: 12046653

If there are 3000 records total and you only want today's records then limit the query by a timestamp/date value.

Alter the Select string to read something like:  Select * from myTable Where Date = '9/13/2004'

The best way to do this would be in a stored procedure that reads something like this:

     Create Procedure PlantRecords

     AS

     Select * from myTable Where Date = GetDate()

0
 

Author Comment

by:Tigger996
ID: 12047625
I think I've almost got it, but I'm having trouble with refreshing... here's what I was trying to do.

... after update
Dim currentposition As Integer
currentposition = Me.BindingContext(DsEntries, "Entry").Position
DsEntries.Tables("Entry").Clear()
daEntry.Fill(DsEntries, "Entry")
Me.BindingContext(DsEntries, "Entry").Position = currentposition

But I receive this binding error:
Additional information: DataBinding could not find a row in the list that is suitable for all bindings

Any ideas?

Tigger996
0
 
LVL 3

Expert Comment

by:RacinRan
ID: 12048610

Which line causes the error?

0
 

Author Comment

by:Tigger996
ID: 12048615
Me.BindingContext(DsEntries, "Entry").Position = currentposition
0
 

Author Comment

by:Tigger996
ID: 12048660
actually I tried just this in my code:
DsEntries.Tables("Entry").Clear()
daEntry.Fill(DsEntries, "Entry")

and it doesn't work.  
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Tigger996
ID: 12048802
I think what is happening is the binding is lost when I clear the dataset.  Do you know a way to prevent/fix this?

0
 
LVL 3

Expert Comment

by:RacinRan
ID: 12053712
If the daEntry.fill returns no rows then there will be nothing to bind to hence the error.
0
 
LVL 3

Expert Comment

by:RacinRan
ID: 12054269

I tested your scenario and got it to work fine.  I used a clear method on the datatable and refreshed using the binding context and it did not lose the binding context.  I declared the dataset at the form level, added a table to it in the form load, loaded the records, and created a button to refresh with.  I'm binding to a datagrid.

Here's my code:

    'Form level declaration
    Dim oDS As New DataSet


    Private Sub frmDataTest_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim dt As New DataTable("Test")
        oDS.Tables.Add(dt)


   'Get Records

            Dim command As New SqlClient.SqlCommand("Select top 3000 * from tblEquipRequests")
            Dim conn As New SqlClient.SqlConnection("Data Source=<datasource>;Integrated Security=True;Initial Catalog=<database>;")
            Dim oDA As New SqlClient.SqlDataAdapter(command)

            With command
                .CommandType = CommandType.Text
                .Connection = conn
            End With

            'Open Connection and return results
            conn.Open()

            oDA.Fill(oDS, "Test")
            dgRecords.DataSource = oDS.Tables("Test")


    End Sub


    Private Sub btnRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefresh.Click

            Dim i As Integer = Me.BindingContext(dgRecords.DataSource).Position

            oDS.Tables("Test").Clear()

            'Get records using same code as in form load w/o adding the datatable
            GetRecords()

            Me.BindingContext(dgRecords.DataSource).Position = i

            dgRecords.Refresh()

    End Sub
0
 
LVL 3

Expert Comment

by:RacinRan
ID: 12054344
I think I found the problem ... your binding context should be:

 Me.BindingContext(DsEntries.Tables("Entry")).Position

instead of

 Me.BindingContext(DsEntries, "Entry").Position
0
 

Author Comment

by:Tigger996
ID: 12056140
That makes no difference.  I think if it was a straight table - datagrid it's no problem.  However I have bound comboboxes, loading from different data tables ...etc.  I lose about 8 entires (textbox fields) they go to their default value, the rest stays, a couple of my combo boxes also go blank when i clear.  I saw some code somewhere about the datagrid and a resetbinding method.  That's why I thought that their could be something similiar with textboxes.

Here's another way maybe:

How could I get, using the timer, my form to close and reopen every 30 min.  This way the dataset would be with current data?

Tigger996
0
 
LVL 3

Accepted Solution

by:
RacinRan earned 500 total points
ID: 12056316
the timer would have to be declared off this form ie... some other form like a main menu or sub main.  then just start the timer and in the interval event close the form, open the form, reset the time and you're done.

the easiest way to do this is to declare a variable on the main form and late bind like this:

on main form:  dim frm as MyForm

to open window for first time from main form:
 frm = new Myform
 frm.show

on timer interval:

frm.close
frm = new MyForm
frm.show

RacinRan



0
 

Author Comment

by:Tigger996
ID: 12056663
Getting REALLY close to a solution.  I think my last problem is passing or holding the position number of where they are at before it closes, so that when it reopens I can go there so that they wouldn't even know what happened.

0
 

Author Comment

by:Tigger996
ID: 12057702
Thank you for all your help.  I think I finally got it. I added a variable to the form constructor.  

Tigger996
0
 
LVL 3

Expert Comment

by:RacinRan
ID: 12057753
Awesome!  It's funny how something simple in concept can be so difficult in implementation.

Thanks for the points.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now