?
Solved

How do you update a database table from a datagridview that is based on a database query

Posted on 2007-08-09
40
Medium Priority
?
476 Views
Last Modified: 2010-10-07
How do you update a database table from a datagridview that is based on a database query. If I make changes to the datagridview I need to update the table to reflect the changes.
0
Comment
Question by:wiswalld
  • 22
  • 17
40 Comments
 
LVL 27

Expert Comment

by:VBRocks
ID: 19662543
One of the things that makes working with a DataGridView easy, using a DataTable as it's datasource.

Here's a simplified example of how to use a DataTable to load a DataGridView, and Update your
database.

   Private da As System.Data.SqlClient.SqlDataAdapter
    Private dt As New System.Data.DataTable()
    Private Sub LoadDataGridView()

        Dim connectionString As String = "your connection string"

        Dim SQL As String = "SELECT * FROM <tableName>"

        da = New System.Data.SqlClient.SqlDataAdapter(SQL, connectionString)

        da.Fill(dt)

        'Set DataSource for DataGridView
        Me.DataGridView1.DataSource = dt


    End Sub

    Private Sub UpdateDatabase()
        Dim db As New SqlClient.SqlCommandBuilder(da)
        da.Update(dt)

    End Sub



Call the LoadDataGridView sub when you want to load your grid, and the UpdateDatabase sub when
you want to update your database.

0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19662556
One additional note:  if you are using a database other than an SQL database, then you will want
to use the OLEDB namespace instead of the SqlClient namespace.

0
 
LVL 13

Author Comment

by:wiswalld
ID: 19663375
What if I have already created the datagridview by dragging from the data source?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 13

Author Comment

by:wiswalld
ID: 19663381
And yes I am using access.
0
 
LVL 13

Author Comment

by:wiswalld
ID: 19663502
Maybe some more explanation.

I have a form with a datagridview on it which shows units available. It is based on a query from my database which is based on two tables. There are times I copy a unit from there to another datagridview and make changes to one of the columns. I want the change to be saved to the database table because I refresh the first datagridview every few minutes to show if any units have been added or taken away. When I do the refresh it changes the column back to the original value (which is what I don't want).
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19663530
Ok, if you created your DataGridView by just dragging and dropping a table from you DataSource, then
you should be able to take the following approach:

First, look in the Component tray on the bottom of your form, see if you see a DataSet, and TableAdapter,
and a BindingSource, these should have been named after the table that you dragged onto the form.
You will want to use these instead of the ones I have used below:
    'Call this sub when the Form loads
    Private Sub LoadData()
        'Replace the name of the TableAdapter with the name of your TableAdapter
        '    Replace the name of the DataSet and table with the name of your DataSet and table
        Me.CompaniesTableAdapter.Fill(Me.DataSet1.Companies)
    End Sub

    Private Sub SaveData()
        Me.Validate()

        'Replace the name of the binding source with the name of yours
        Me.CompaniesBindingSource.EndEdit()

        'Replace the name of the TableAdapter with the name of yours.
        Me.CompaniesTableAdapter.Update(Me.DataSet1.Companies)
        MsgBox("done")

    End Sub


That should do it for you.



0
 
LVL 13

Author Comment

by:wiswalld
ID: 19663578
I have tried this

Me.Validate()
        Me.queryshiftstatusBindingSource.EndEdit()
        Me.DataGridView1.EndEdit()
        Me.queryshiftstatusTableAdapter.Update(Me._CAD_050107DataSet.queryshiftstatus)

but I get update is not a member. I think because it is a query not a table. The reason for the design is I can put in a shift 1, 2 or 3 and the query will no by the time of day what shift should be on duty.
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19663659
If you have a query selecting from multiple tables, then your Update won't work.

I think what may be a better approach is to create a Strongly-Typed DataSet that accurately represents
your database. (and you may have already done that).  If not, then Add a New Item to your project, Select DataSet...  Then you can add tables accordingly.  

Then, instead of using a query to display data from multiple tables, break your data from each table out
to it's own control, then it will be editable, and you can update the Database.  Does that make sense?

Give me the name of both of your tables, and (a few) of their columns, and I'll try to put together a
sensible example for you.

0
 
LVL 13

Author Comment

by:wiswalld
ID: 19663712
Everything I have done has pretty much been done with wizards. So I will give you the table names and columns.

Table1 - tbl_employee
Fields:
1. Autonumber
2. Date1
3. Name1
4. Name2
5. Shift
6. Unit
7. Assignment
8. Available

Table2 - tbl_ShiftTime
Fields:
1. Shift_Time
2. Shift


The sql behind the query based on the tables is:


SELECT tbl_employee.autonumber, tbl_employee.Unit, tbl_employee.Date1, tbl_employee.Name1, tbl_employee.Name2, tbl_employee.shift, tbl_employee.Assignment, tbl_employee.Available, tbl_shifttime.Shift_time, tbl_shifttime.Shift
FROM tbl_employee, tbl_shifttime
WHERE (((tbl_employee.Date1)=Date()) AND ((tbl_shifttime.Shift_time)=Format(Now(),"h")));

0
 
LVL 13

Author Comment

by:wiswalld
ID: 19663723
This way at the beginning or in the middle of a shift I can input names along with the shift and if they are on duty they will show in the datagridview. If it is shift 2 (7-3) and there is someone in for shift 1 they will not show.
0
 
LVL 13

Author Comment

by:wiswalld
ID: 19664246
Trying to convert and remake an access database.

The original microsoft access form I have has two fields on it.

One shows the time by hour: =Format(Now(),"h")

The other shows the current shift based on the time: =DLookUp("[Shift]","tbl_ShiftTime","[Shift_time]=" & [TimeNow])

Then a subform where I enter the names, date and shift, ect.

What if I based my datagridview on that? How would I do that?
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19664280
Are you programming your interface in Access or Visual Basic  .NET?

I'm putting together an example for you in VB.NET, so let me know if I'm going down the wrong track.

0
 
LVL 13

Author Comment

by:wiswalld
ID: 19664305
VB.Net

Just giving an example of what I had in access.
0
 
LVL 27

Accepted Solution

by:
VBRocks earned 2000 total points
ID: 19664931
Ok, I put together an example for you.  It's a bit of code, so I hope it doesn't overwhelm you.

Add a new form to your project, and set it as the startup form.  Copy and paste all of this code into
the form class.

You will need to change your Connection String at the top.  I think everything else should work ok.


    Private connectionString As String = AppConfig.ConnectionString


#Region " Database Transactions"

    Private daEmployee As System.Data.OleDb.OleDbDataAdapter
    Private daShift As System.Data.OleDb.OleDbDataAdapter
    Private Sub LoadDataSet()

        daEmployee = New System.Data.OleDb.OleDbDataAdapter()
        daEmployee.SelectCommand = New OleDb.OleDbCommand()
        daEmployee.SelectCommand.Connection = New OleDb.OleDbConnection(connectionString)

        daShift = New System.Data.OleDb.OleDbDataAdapter()
        daShift.SelectCommand = New OleDb.OleDbCommand()
        daShift.SelectCommand.Connection = New OleDb.OleDbConnection(connectionString)

        'Fill tbl_employee
        daEmployee.SelectCommand.CommandText = "SELECT * FROM tbl_employee"
        daEmployee.Fill(m_DataSet.Tables("tbl_employee"))

        'Fill tbl_ShiftTime
        daShift.SelectCommand.CommandText = "SELECT * FROM tbl_ShiftTime"
        daShift.Fill(m_DataSet.Tables("tbl_ShiftTime"))

    End Sub


    Private Sub UpdateDataBase()
        Dim db As OleDb.OleDbCommandBuilder

        db = New OleDb.OleDbCommandBuilder(daEmployee)
        daEmployee.Update(m_DataSet.Tables("tbl_employee"))

        'This shift table data is only used in a combobox,
        '    so we don't need to update it.  But if we did,
        '    just uncomment this code.
        'db = New OleDb.OleDbCommandBuilder(daShift)
        'daShift.Update(m_DataSet.Tables("tbl_ShiftTime"))

        MsgBox("Database Updated")

    End Sub

#End Region    'Database Transactions


#Region " Private Methods"


#Region " Create DataSet"
    Private m_DataSet As New DataSet()
    Private Sub CreateDataSet()
        'Create Employee Table
        Dim tblEmployee As New DataTable("tbl_employee")

        '*** Create Columns for table
        Dim colAutonumber As New DataColumn("AutoNumber")
        colAutonumber.Unique = True
        colAutonumber.AutoIncrement = True
        colAutonumber.AutoIncrementSeed = 1
        colAutonumber.AutoIncrementStep = 1

        Dim colDate1 As New DataColumn("Date1")
        colDate1.DataType = System.Type.GetType("System.DateTime")

        Dim colName1 As New DataColumn("Name1")
        Dim colName2 As New DataColumn("Name2")
        Dim colShift As New DataColumn("Shift")
        Dim colUnit As New DataColumn("Unit")
        Dim colAssignment As New DataColumn("Assignment")
        Dim colAvailable As New DataColumn("Available")
        '*** End Create Columns for table

        'Add columns to table
        tblEmployee.Columns.AddRange(New DataColumn() _
            {colAutonumber, colDate1, colName1, colName2, _
            colShift, colUnit, colAssignment, colAvailable})


        'Create Shift table
        Dim tblShift As New DataTable("tbl_ShiftTime")

        '*** Create Columns for table
        Dim colShift_Time As New DataColumn("Shift_Time")
        Dim colShift2 As New DataColumn("Shift")
        '*** End Create Columns for table

        'Add columns to table
        tblShift.Columns.AddRange(New DataColumn() {colShift_Time, colShift2})


        'Add both tables to dataset
        m_DataSet.Tables.AddRange(New DataTable() {tblEmployee, tblShift})

    End Sub

#End Region    'Create DataSet


#Region " Create Controls"

    Private Sub CreateComboBox()
        'Create a label for the combobox
        Dim lbl As New Label()
        lbl.Text = "Select a Shift:  "
        lbl.Name = "lblShift"
        lbl.AutoSize = True
        lbl.Location = New Point(22, 21)


        'Location 115, 18
        Dim combo As New ComboBox()
        combo.Name = "comboShift"
        combo.Location = New Point(115, 18)

        AddHandler combo.SelectedIndexChanged, AddressOf comboShift_SelectedIndexChanged

        'Add new controls to form
        Me.Controls.AddRange(New Control() {lbl, combo})

    End Sub


    Private Sub CreateGrid()
        'Location 25, 70
        Dim grid As New DataGridView()
        grid.Name = "EmployeeGrid"
        grid.Width = Me.Width - 60
        grid.Anchor = AnchorStyles.Top Or AnchorStyles.Left Or _
                AnchorStyles.Right Or AnchorStyles.Bottom
        grid.Location = New Point(25, 70)

        Me.Controls.Add(grid)

    End Sub

    Private Sub CreateButton()
        Dim btn As New Button()
        btn.Name = "btnSave"
        btn.Text = "Save"
        btn.Location = New Point(25, 225)

        AddHandler btn.Click, AddressOf btnSave_Click

        Me.Controls.Add(btn)

    End Sub

#End Region    'Create Controls


#Region " Load Controls"

    Private Sub LoadComboBox()
        Dim combo As ComboBox = DirectCast(Me.Controls("comboShift"), ComboBox)
        combo.DisplayMember = "Shift"
        combo.ValueMember = "Shift_Time"
        combo.DataSource = m_DataSet.Tables("tbl_ShiftTime")

    End Sub

    Private Sub LoadDataGrid()
        Dim dv As DataView = m_DataSet.Tables("tbl_employee").DefaultView
        dv.RowFilter = "Shift='" & DirectCast(Me.Controls("comboShift"), ComboBox).Text & "'"

        DirectCast(Me.Controls("EmployeeGrid"), DataGridView).DataSource = dv

    End Sub

#End Region    'Load Controls


#End Region    'Private Methods


#Region " Control Event Handlers"

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

        CreateComboBox()
        CreateGrid()
        CreateButton()
        CreateDataSet()
        LoadDataSet()
        LoadComboBox()

    End Sub

    Private Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs)
        UpdateDataBase()

    End Sub

    Private Sub comboShift_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
        If DirectCast(sender, ComboBox).SelectedValue.ToString() <> "" Then
            LoadDataGrid()
        End If
    End Sub

#End Region    'Control Event Handlers


Hopefully this example will help you!

0
 
LVL 34

Expert Comment

by:Sancler
ID: 19668434
There is another approach which MIGHT work.  I've never used it with autogenerated tableadapters and datasets but I can see no reason, in principle, why it should not work.

As I've used it, it requires two dataadapters.  One - say da1 - is configured with a SELECT statement that only includes the fields/columns from the one table that I want to be updateable.

The second dataadapter - da2 - is configured with a SELECT statement based on the joined query.  

It is da2 that is used to .Fill the datatable but, when an update is necessary, it is da1 (with a commandbuilder) that is used to do it.  As that is based on the single table (presuming a Primary Key) the commandbuilder works OK.  And, as it only looks at those fields/columns that it "knows" are in the datatable - even though other, joined fields/columns have been introduced into it by da2 - it ignores the extra data.

It won't work for all joined queries.  It requires the relevant field/column names to be those from the updateable table.  The records in the query from the updateable table have to be unique.  But this looks to me to be a scenario in which it would be a viable approach.

Although I haven't tested, VBRocks' approach also looks to me to be viable.  So I'm only offering this as an alternative to give you some choice.

Roger
0
 
LVL 13

Author Comment

by:wiswalld
ID: 19669259
VBRocks

I get what you are talking about looks godd so far but need a few modifications. I want the current date to only appear and the current shift. I don't want to have to select the shift. Can that be done?
0
 
LVL 13

Author Comment

by:wiswalld
ID: 19669267
Maybe a stupid question, but will this work on another form. My main form has is a form with four datagridview on it, one being this one.
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19670564
Hi Sancler!  Thanks for you comments.  I haven't thought of that before, so it might be an idea I can implement in the future.

wiswalld:
    If you don't want the combobox on the form, just comment out the following in the Form_Load event:
        CreateComboBox()
        LoadComboBox()

    Move the following into the FormLoad event:
        LoadDataGrid()

    In the LoadDataGrid sub, comment out the following line:
        dv.RowFilter = "Shift='" & DirectCast(Me.Controls("comboShift"), ComboBox).Text & "'"

    And, in the LoadDataSet sub, update your SQL syntax:
        daEmployee.SelectCommand.CommandText = _
              "SELECT * FROM tbl_employee WHERE Date1=Date()" & _
                  " AND Shift='" stringYourShift & "'"


Again, this is just an example for you.  You can tweek the code and use it anywhere you like, for
different situations.

And, yes, the code for loading the DataSet / DataTable will work on any form, but of course, you don't
want to include the code for creating controls on other forms, just use the designer to include what
you want.

Good luck!



0
 
LVL 13

Author Comment

by:wiswalld
ID: 19671282
OK seems to work fine. But along the lines of putting this onto an existing form and replacing the datagridview I already have how will I place it in the same place. And if I have code that is run from my existing datagridview will this still work:

Dim dr As DataRow = Me._CAD_050107DataSet.Tables("assignedvehicles").NewRow
        'Put values into row
        dr(1) = EventNumber.Text
        dr(2) = DataGridView1.CurrentRow.Cells(1).Value.ToString
        dr(3) = DataGridView1.CurrentRow.Cells(3).Value.ToString
        dr(4) = DataGridView1.CurrentRow.Cells(4).Value.ToString
        Me.DataGridView1.CurrentRow.Cells(7).Value = "No"
        Me.DataGridView1.CurrentRow.Cells(7).Style.BackColor() = Color.Red
        'and so on, making sure you put values in any ...
        '... fields/columns that do not allow null
        Me._CAD_050107DataSet.Tables("assignedvehicles").Rows.Add(dr)
        Me.DataGridView1.CurrentRow.Cells(7).Value = "No"
        Me.DataGridView1.CurrentRow.Cells(7).Style.BackColor() = Color.Red
        Me.Validate()
        Me.AssignedVehiclesBindingSource.EndEdit()
        Me.DataGridView2.EndEdit()
        Me.AssignedVehiclesTableAdapter.Update(Me._CAD_050107DataSet.AssignedVehicles)
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19671423
If you already have your existing DataGridView1 in place, then just use that.

In addition, if you take the approach of setting the DataSource of DataGridView1, then the following
code is obselete, unless you just want to set cell values specifically:
        'Put values into row
        dr(1) = EventNumber.Text
        dr(2) = DataGridView1.CurrentRow.Cells(1).Value.ToString
        dr(3) = DataGridView1.CurrentRow.Cells(3).Value.ToString
        dr(4) = DataGridView1.CurrentRow.Cells(4).Value.ToString
        Me.DataGridView1.CurrentRow.Cells(7).Value = "No"
        Me.DataGridView1.CurrentRow.Cells(7).Style.BackColor() = Color.Red
        'and so on, making sure you put values in any ...
        '... fields/columns that do not allow null
        Me._CAD_050107DataSet.Tables("assignedvehicles").Rows.Add(dr)

The reason the above code is obselete is because any changes made to the DataGridView will auto-
matically update the DataTable that is used as it's source.

In the LoadDataGrid sub above, just change it to the name of your DataGridView:
    Private Sub LoadDataGrid()
        Dim dv As DataView = m_DataSet.Tables("tbl_employee").DefaultView

        DataGridView1.DataSource = dv

    End Sub


So all together, the code you need to add to your new form is:
    Private connectionString As String = AppConfig.ConnectionString
    #Region " Database Transactions" (whole region)
    #Region " Create DataSet" (whole region)
    Private Sub LoadDataGrid() (in #Region " Load Controls")
    And in the Form_Load event, call the following:
        CreateDataSet()
        LoadDataSet()
        LoadDataGrid()

Just call the UpdateDataBase() method when you want to save the changes to the database.

And I think that should do it for you.


0
 
LVL 13

Author Comment

by:wiswalld
ID: 19671437
>And, yes, the code for loading the DataSet / DataTable will work on any form, but of course, you don't
want to include the code for creating controls on other forms, just use the designer to include what
you want.<

OK a little confused. If I create a blank datagridview on a form how do I load it with this data? And the same question from earlier, if I add a textbox with the date I only want to show records for that date. And along the same line how can I automatically populate a textbox with the proper shift depending on the time.

I realize you have spent so much time with this, but I have already created the program pretty much, with the exception of this proplem it is done. Don't want to reinvent the wheel:)
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19671542
Oh, absolutely don't do any re-inventing!  You've already done a great job on what you have.  I just did
the work to give you an example of how to make what you already have work.  You may have to study
it a bit to try to figure out how to adapt it to different scenarios, but that's the fun part!

Let's say you have a DataGridView that you added to your form, and it is named "DataGridView1".
If you Form_Load event, call the code from my example above (you'll have to copy and paste it into
your existing form):

        CreateDataSet()
        LoadDataSet()
        LoadDataGrid()

In the LoadDataGrid sub, just enter the name of your DataGridView:

    Private Sub LoadDataGrid()
        Dim dv As DataView = m_DataSet.Tables("tbl_employee").DefaultView

        DataGridView1.DataSource = dv

    End Sub

When you want to update your database with changes, call the following method (from a button,
or when the form closes, or whatever...):
    UpdateDataBase()


See if you can get that working, and then we'll look at the TextBox.
0
 
LVL 13

Author Comment

by:wiswalld
ID: 19671986
What am I missing. Datagridview empty

Private connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CADDVAD\CAD-050107.mdb"


#Region " Database Transactions"

    Private daEmployee As System.Data.OleDb.OleDbDataAdapter
    Private daShift As System.Data.OleDb.OleDbDataAdapter
    Private Sub LoadDataSet()

        daEmployee = New System.Data.OleDb.OleDbDataAdapter()
        daEmployee.SelectCommand = New OleDb.OleDbCommand()
        daEmployee.SelectCommand.Connection = New OleDb.OleDbConnection(connectionString)

        daShift = New System.Data.OleDb.OleDbDataAdapter()
        daShift.SelectCommand = New OleDb.OleDbCommand()
        daShift.SelectCommand.Connection = New OleDb.OleDbConnection(connectionString)

        'Fill tbl_employee
        daEmployee.SelectCommand.CommandText = "SELECT * FROM tbl_employee"
        daEmployee.Fill(m_DataSet.Tables("tbl_employee"))

        'Fill tbl_ShiftTime
        daShift.SelectCommand.CommandText = "SELECT * FROM tbl_ShiftTime"
        daShift.Fill(m_DataSet.Tables("tbl_ShiftTime"))

    End Sub


    Private Sub UpdateDataBase()
        Dim db As OleDb.OleDbCommandBuilder

        db = New OleDb.OleDbCommandBuilder(daEmployee)
        daEmployee.Update(m_DataSet.Tables("tbl_employee"))

        'This shift table data is only used in a combobox,
        '    so we don't need to update it.  But if we did,
        '    just uncomment this code.
        'db = New OleDb.OleDbCommandBuilder(daShift)
        'daShift.Update(m_DataSet.Tables("tbl_ShiftTime"))

        MsgBox("Database Updated")

    End Sub

#End Region    'Database Transactions





#Region " Create DataSet"
    Private m_DataSet As New DataSet()
    Private Sub CreateDataSet()
        'Create Employee Table
        Dim tblEmployee As New DataTable("tbl_employee")

        '*** Create Columns for table
        Dim colAutonumber As New DataColumn("AutoNumber")
        colAutonumber.Unique = True
        colAutonumber.AutoIncrement = True
        colAutonumber.AutoIncrementSeed = 1
        colAutonumber.AutoIncrementStep = 1

        Dim colDate1 As New DataColumn("Date1")
        colDate1.DataType = System.Type.GetType("System.DateTime")

        Dim colName1 As New DataColumn("Name1")
        Dim colName2 As New DataColumn("Name2")
        Dim colShift As New DataColumn("Shift")
        Dim colUnit As New DataColumn("Unit")
        Dim colAssignment As New DataColumn("Assignment")
        Dim colAvailable As New DataColumn("Available")
        '*** End Create Columns for table

        'Add columns to table
        tblEmployee.Columns.AddRange(New DataColumn() _
            {colAutonumber, colDate1, colName1, colName2, _
            colShift, colUnit, colAssignment, colAvailable})


        'Create Shift table
        Dim tblShift As New DataTable("tbl_ShiftTime")

        '*** Create Columns for table
        Dim colShift_Time As New DataColumn("Shift_Time")
        Dim colShift2 As New DataColumn("Shift")
        '*** End Create Columns for table

        'Add columns to table
        tblShift.Columns.AddRange(New DataColumn() {colShift_Time, colShift2})


        'Add both tables to dataset
        m_DataSet.Tables.AddRange(New DataTable() {tblEmployee, tblShift})

    End Sub

#End Region    'Create DataSet
#Region " Load Controls"
    Private Sub LoadDataGrid()
        Dim dv As DataView = m_DataSet.Tables("tbl_employee").DefaultView
        DataGridView1.DataSource = dv


    End Sub

#End Region    'Load Controls
0
 
LVL 13

Author Comment

by:wiswalld
ID: 19672073
OK got the code right. Probably can get rid of some more code though:

Private connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CADDVAD\CAD-050107.mdb"


#Region " Database Transactions"

    Private daEmployee As System.Data.OleDb.OleDbDataAdapter
    Private daShift As System.Data.OleDb.OleDbDataAdapter
    Private Sub LoadDataSet()

        daEmployee = New System.Data.OleDb.OleDbDataAdapter()
        daEmployee.SelectCommand = New OleDb.OleDbCommand()
        daEmployee.SelectCommand.Connection = New OleDb.OleDbConnection(connectionString)

        daShift = New System.Data.OleDb.OleDbDataAdapter()
        daShift.SelectCommand = New OleDb.OleDbCommand()
        daShift.SelectCommand.Connection = New OleDb.OleDbConnection(connectionString)

        'Fill tbl_employee
        daEmployee.SelectCommand.CommandText = "SELECT * FROM tbl_employee"
        daEmployee.Fill(m_DataSet.Tables("tbl_employee"))

        'Fill tbl_ShiftTime
        daShift.SelectCommand.CommandText = "SELECT * FROM tbl_ShiftTime"
        daShift.Fill(m_DataSet.Tables("tbl_ShiftTime"))

    End Sub


    Private Sub UpdateDataBase()
        Dim db As OleDb.OleDbCommandBuilder

        db = New OleDb.OleDbCommandBuilder(daEmployee)
        daEmployee.Update(m_DataSet.Tables("tbl_employee"))

        'This shift table data is only used in a combobox,
        '    so we don't need to update it.  But if we did,
        '    just uncomment this code.
        'db = New OleDb.OleDbCommandBuilder(daShift)
        'daShift.Update(m_DataSet.Tables("tbl_ShiftTime"))

        MsgBox("Database Updated")

    End Sub

#End Region    'Database Transactions


#Region " Private Methods"


#Region " Create DataSet"
    Private m_DataSet As New DataSet()
    Private Sub CreateDataSet()
        'Create Employee Table
        Dim tblEmployee As New DataTable("tbl_employee")

        '*** Create Columns for table
        Dim colAutonumber As New DataColumn("AutoNumber")
        colAutonumber.Unique = True
        colAutonumber.AutoIncrement = True
        colAutonumber.AutoIncrementSeed = 1
        colAutonumber.AutoIncrementStep = 1

        Dim colDate1 As New DataColumn("Date1")
        colDate1.DataType = System.Type.GetType("System.DateTime")

        Dim colName1 As New DataColumn("Name1")
        Dim colName2 As New DataColumn("Name2")
        Dim colShift As New DataColumn("Shift")
        Dim colUnit As New DataColumn("Unit")
        Dim colAssignment As New DataColumn("Assignment")
        Dim colAvailable As New DataColumn("Available")
        '*** End Create Columns for table

        'Add columns to table
        tblEmployee.Columns.AddRange(New DataColumn() _
            {colAutonumber, colDate1, colName1, colName2, _
            colShift, colUnit, colAssignment, colAvailable})


        'Create Shift table
        Dim tblShift As New DataTable("tbl_ShiftTime")

        '*** Create Columns for table
        Dim colShift_Time As New DataColumn("Shift_Time")
        Dim colShift2 As New DataColumn("Shift")
        '*** End Create Columns for table

        'Add columns to table
        tblShift.Columns.AddRange(New DataColumn() {colShift_Time, colShift2})


        'Add both tables to dataset
        m_DataSet.Tables.AddRange(New DataTable() {tblEmployee, tblShift})

    End Sub

#End Region    'Create DataSet


#Region " Create Controls"

    Private Sub CreateComboBox()
        'Create a label for the combobox
        Dim lbl As New Label()
        lbl.Text = "Select a Shift:  "
        lbl.Name = "lblShift"
        lbl.AutoSize = True
        lbl.Location = New Point(22, 21)


        'Location 115, 18
        Dim combo As New ComboBox()
        combo.Name = "comboShift"
        combo.Location = New Point(115, 18)

        AddHandler combo.SelectedIndexChanged, AddressOf comboShift_SelectedIndexChanged

        'Add new controls to form
        Me.Controls.AddRange(New Control() {lbl, combo})

    End Sub


   

    Private Sub CreateButton()
        Dim btn As New Button()
        btn.Name = "btnSave"
        btn.Text = "Save"
        btn.Location = New Point(25, 225)

        AddHandler btn.Click, AddressOf btnSave_Click

        Me.Controls.Add(btn)

    End Sub

#End Region    'Create Controls


#Region " Load Controls"

   

    Private Sub LoadDataGrid()
        Dim dv As DataView = m_DataSet.Tables("tbl_employee").DefaultView

        DataGridView1.DataSource = dv

    End Sub

#End Region    'Load Controls


#End Region    'Private Methods


#Region " Control Event Handlers"

    Private Sub frmEmployee_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
        CreateDataSet()
        LoadDataSet()
        LoadDataGrid()

    End Sub

    Private Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs)
        UpdateDataBase()

    End Sub

    Private Sub comboShift_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
        If DirectCast(sender, ComboBox).SelectedValue.ToString() <> "" Then
            LoadDataGrid()
        End If
    End Sub

#End Region    'Control Event Handlers
0
 
LVL 13

Author Comment

by:wiswalld
ID: 19672119
What I think I need is two textboxes (hidden) one with time:

Me.textbox1.text = DateTime.Now.ToString("HH")
Me.textbox2.text = ? (in access I used =DLookUp("[Shift]","tbl_ShiftTime","[Shift_time]=" & [TimeNow])

Somehow filter results based on them.
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19673434
What are the TextBoxes for?  

So the user can enter a criteria, and then retrieve the data and fille the
datagridview based off of the results?  Or are the TextBoxes just to display the time that was used
when retrieving the data?  Or Something else?

0
 
LVL 13

Author Comment

by:wiswalld
ID: 19673481
Slap me if I sound stupid. I was thinking of showing the time by hour and the shift through some type of dlookup function then have

Me.datagridview.shift = textbox2  (something like that)


Unless you have a better idea.
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19673612
I'm not completely sure I understand.  tbl_employee has  "Shift" field, and that should already be displayed in the DataGridView.

Are you saying you would like to enter the Shift in the TextBox, then maybe click a button, and load the
DataGridView with employees that have that shift?  Or perhaps, filter the DataGridView so it shows
the employes that have that shift?

0
 
LVL 13

Author Comment

by:wiswalld
ID: 19674298
Yes a filter. But I don't want to enter anything, I want the textbox to automatically show what shift it is. My shifts are as follows.

1 - 2300
1 - 2400
1 - 0100
1 - 0200
1 - 0300
1 - 0400
1 - 0500
1 - 0600
2 - 0700
2 - 0800



3 - 1500
3 - 1600

I want the first textbox to show the time using:

Me.textbox1.text = DateTime.Now.ToString("HH")



and the second to show the corresponding shift:

Me.textbox2 = ?

Then the datagridview you designed would filter and only show records for this date and this current shift
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19686407
Ok well, however you are filtering then (programmatically), you'll have to show it:

For example:
    Dim dv as dataview = DirectCast(DataGridView1.DataSource, DataView)
    dv.RowFilter = "Shift=#" & DateTime.Now & "#"

'Show your filter
Me.textbox1.text = DateTime.Now.ToString("HH")

'Show your shift
Me.textbox2.Text = dv(0).Item("Shift")

0
 
LVL 13

Author Comment

by:wiswalld
ID: 19692165
Sorry been away for a few days. Everything is great. Only thing stuck on is how to refresh the datagridview.
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19692180
What do you mean by "Refresh it"?

0
 
LVL 13

Author Comment

by:wiswalld
ID: 19692444
If I am using this program as a multiple use front end and someone add or subtracts something from the datagridview I would like to show on all computers.
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19692482
You'll have to drop a Timer on your form, set the Interval to something like 15000 (15 seconds), Enable it,
and then in the Timer_Tick event, you'll have to Update your database, then reload the data.

Be sure to include Me.Validate() as the first line of code.

0
 
LVL 13

Author Comment

by:wiswalld
ID: 19692692
I only want to reload the data for this datagridview. Just not sure how to reload since we populated it this way.
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19692885
Not a problem.  Just perform the same load we did before the same exact way, for example:

    Private Sub Timer1_Tick(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Timer1.Tick
         
          'Reload the dataset
          LoadDataSet()

          'Reload the DataGridView
          LoadDataGrid()

    End Sub


That should do it for you.

0
 
LVL 13

Author Comment

by:wiswalld
ID: 19693875
Didn't work

I have this working behind a button, but I don't want this on a timer because if they are working they will lose all their work

Dim frm As New Form1
        frm.Show() ' show new instance of form
        Me.Close() ' close current form
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19700044
Can you put it behind your Save button then, so that when they click it, it will update the Database and
refresh?  The only other option is when the Form closes.  One issue you can come across is that you
don't really know when the user has finished their edits.

Also, this is getting a bit of track of your original question.  Have I helped you solve your original question
or are there still some questions about it?

Thanks.
0
 
LVL 13

Author Comment

by:wiswalld
ID: 19702374
Yes you have just trying to solve another problem I see.
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19702415
Yes, there are a few other people I'm trying to help, in addition to writing a new program.  :)

Thanks!

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month14 days, 6 hours left to enroll

809 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