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

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.

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.

What if I have already created the datagridview by dragging from the data source?

And yes I am using access.

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).

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.

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.

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.

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")));

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.

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?

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.

VB.Net

Just giving an example of what I had in access.

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

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?

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.

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!

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)

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.

>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:)

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.

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

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

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.

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?

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.

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?

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

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")

Sorry been away for a few days. Everything is great. Only thing stuck on is how to refresh the datagridview.

What do you mean by "Refresh it"?

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.

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.

I only want to reload the data for this datagridview. Just not sure how to reload since we populated it this way.

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.

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

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.

Yes you have just trying to solve another problem I see.

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

Thanks!