index > Visual Basic Express Edition > Update Local SQL Database from Unbound DataGridView

Update Local SQL Database from Unbound DataGridView

I want to update a local SQL database from an unbound DataGridView by clicking a button. I have tried to find the code to do this without any luck.

I would welcome any help to do this.

Konker

Konker Friday, March 21, 2008 4:33 PM

Are you clear on what "bound" and"unbound" means? I hope you are not thinking that a 'bound' grid updates the db automatically - binding has nothing to do with the database itself. Binding merely update the in-memorydatasource automatically which would be a datatable, or an array, or a collection. So I'm not sure why you want an "unbound" grid - what advantage is that?

Next you have to decide whether to do update the db one record at a time (use the cmd.ExecuteNonQuery method).

Or, you can update multiple records by using a datatable as your grid.DataSource and calling

dataAadpter.Fill(dt)

to fill the datable and data and then

dataAdapter.Update(dt)

to update the database. (But you have to make sure the datatable has the current values, which takes a bit of work if the grid isn't bound to to the datatable).

jal2 Saturday, March 22, 2008 6:06 AM

Next you have to decide whether to do update the db one record at a time (use the cmd.ExecuteNonQuery method).

Or, you can update multiple records by using a datatable as your grid.DataSource and calling

dataAadpter.Fill(dt)

to fill the datable and data and then

dataAdapter.Update(dt)

to update the database. (But you have to make sure the datatable has the current values, which takes a bit of work if the grid isn't bound to to the datatable).

jal2 Saturday, March 22, 2008 6:06 AM

Hi jal2,

What I want to do is drag a button and DataGridView from the toolbox onto the form and set the DataGridView 'Choose Data Source' to None, which I thought was meant by 'unbound'. Then I want to code the button to select a local SQL Databaseand then update it with the DataGridView data.

Hope you can help.

Konker.

Konker Saturday, March 22, 2008 9:50 AM

If your datagridview has no datasource, how are you going to get data into it? The "datasource" is what puts the data into it, which the user can then modify. This is databinding. That's why I was asking you for a clear explanation as to why you would want an unbound grid. Here's a bound example - but I bind it using code. Then in my next post, I'll try to provide you with some tips on the extra steps necessary to work with an unbound grid.

'Create a new table in Sql Expresss 2005 and call it Employees2 (in case

'Employees already exists). Create three columns FirstName, LastName

'and EmployeeID (autoIncrement as primary key).

'Drop onto the form a btnLoad, btnUpdate, and a DatagridView.

-then replace all the code in the editor with this code.

Imports System.Data.SqlClient

Public Class Form1

Private da As SQLDataAdapter

Private dt As New DataTable

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

AddHandler btnUpdate.Click, AddressOf btnUpdate_Click

AddHandler btnLoad.Click, AddressOf btnLoad_Click

End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

Try

da.Update(dt)

btnLoad.PerformClick()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Sub

Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

dt.Rows.Clear()

dt = New DataTable

Dim connectionString As String = "Server=.\SQLExpress; Trusted_Connection=Yes; Database=Employees;"

da = New SQLDataAdapter("SELECT FirstName, LastName, EmployeeID FROM Employees2", connectionString)

Try

dt.BeginLoadData() 'increase performance

da.Fill(dt)

dt.EndLoadData() '

Catch ex As Exception

MsgBox("Error was: " + ex.Message)

da.Dispose()

Return

End Try

Dim bs As New BindingSource

bs.DataSource = dt

DataGridView1.DataSource = bs

DataGridView1.Columns("EmployeeID").ReadOnly = True

'This code this allows the user to INSERT rows, UPDATE rows, and DELETE rows.

Dim cn As SQLConnection = da.SelectCommand.Connection

da.UpdateCommand = New SQLCommand()

da.UpdateCommand.Connection = cn

da.UpdateCommand.CommandText = _

"UPDATE Employees2 SET Firstname = @FirstName, LastName = @LastName WHERE EmployeeID = @EmployeeID"

da.UpdateCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, "Firstname")

da.UpdateCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, "LastName")

da.UpdateCommand.Parameters.Add("@EmployeeID", SqlDbType.BigInt, 8, "EmployeeID")

da.DeleteCommand = New SQLCommand

da.DeleteCommand.Connection = cn

da.DeleteCommand.CommandText = "Delete From Employees2 Where EmployeeID = @EmployeeID"

da.DeleteCommand.Parameters.Add("@EmployeeID", SqlDbType.BigInt, 5, "EmployeeID")

da.InsertCommand = New SQLCommand

da.InsertCommand.Connection = cn

da.InsertCommand.CommandText = "Insert Into Employees2" _

& "(LastName, Firstname)" & "Values (@LastName, @FirstName)"

da.InsertCommand.Parameters.Add("@Lastname", SqlDbType.NVarChar, 50, "LastName")

da.InsertCommand.Parameters.Add("@Firstname", SqlDbType.NVarChar, 50, "FirstName")

End Sub

End Class

jal2 Saturday, March 22, 2008 12:21 PM

If you don't want to use the grid's DataSource property (and thus leave it unbound), then I suppose you will have to manually create rows and columns in the datagridview. In other words, loop through the (filled) datatable - fill it using the code sample above - and then, for each row of the datatable, construct a like row in the datagridview (again, i don't know why anyone would want to do this). Then, when the user is done modifying the records, you will have to figure out, for each such modified record in the grid, which row of the datatable is the matching row, and then you will have to manually use the grid-row to overwrite the old data resident in the datatable-row. Only then can you call

dataAdapter.Update(datatable)

to udpate the actual database. If you really want to go to all this trouble, let me know, and maybe later on I can provide you a few more hints (not sure I'll have time).

jal2 Saturday, March 22, 2008 12:29 PM

Hi jal2

Thanks for your help.

I can populate the unbound DataGridView1 from the SQL database with the following code.

******************************************************************

Private Sub btnLoad_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs)

Dim Ca As New EmployeesDataSetTableAdapters.Employees2TableAdapter

Dim table As New EmployeesDataSet.Employees2DataTable

Dim Cds As New System.Data.DataSet()

Cds.Tables.Add(table)

Ca.Fill(table)

DataGridView1.DataSource = Cds.Tables(0)

End Sub

******************************************************************

What I want to dois modify the DataGridView1 data then update the SQL database with the changes, in code.

Konker

Konker Sunday, March 23, 2008 12:03 PM

Then in that case I should think the code sample I gave you would work. Given that you have now set the DataSource, any changes made to the dgv data - whether programatically by you, or manually by the user - should carry over into the underlying datatable which is Cds.Tables(0). When the changes are complete, you can call dataAdapter.Update(Cds.Tables(0)) to save them to the db.

I use datadapters, not TableAdapters. There seems to be a good number of programmers who would agree that TableAdapters are (1) tricky to use (2) do not offer any great advantages and (3) are not more performant.

jal2 Sunday, March 23, 2008 12:12 PM

HI jal2

Have tried your code and it works fine except that it won't allow the insertion of new records and doesn'tupdate the actualSQL database.

I would welcome your solution to these points.

Konker

Konker Wednesday, March 26, 2008 4:58 PM

I just tried it again - the code works fine for me. In the DatagridView, I can insert, delete, and modify records - and all changes update to the database.

For example the last row of the dgv is an empty row where I can insert a new record. (No, I cannot insert a record into the middle of the dgv - that would require special coding - but I can put a new record on the last row).

Have you stepped through the code in debug mode to make sure that the btn handler subs are firing properly?

Let us know allthe specifics of what debugging steps you have taken, and what you have gleaned so far from these efforts.

jal2 Wednesday, March 26, 2008 10:04 PM

Hi jal2

When I try to populate the new record, the Employee2 column does not allocate the next number or allow me to enter a number.

In your code is the following statement: 'DataGridView1.Columns("EmployeeID").ReadOnly = True'

When I set this to false, update works and allocates the next number to the Employee2 column. and everthing all works fine

One further thing I need to do is filter the database data before loading it into the DataGridView1.

I would welcome any suggestions you have as to the best way to do that.

Konker

Konker Saturday, March 29, 2008 2:57 PM

An autoincrement column should be ReadOnly. The value is set AFTER the Update (because it's during the update that the db determines this value).

As for filtering the data, this can be done in the WHERE clause of your original query. Your success will be the extent to which you know SQL query languge. If you are having trouble, I imagine there is a forum for such questions (I am not sure if this one is the appropriate one).

Alternatively, you can use a DataView as your datasource

Dim dv as DataView = datatable1.DefaultView

dv.RowFilter = "Counry = 'Germany' "

dgv.DataSource = dv

The above is a WHERE clause, meaning SELECT all rows WHERE Country = Germany. This filter returns a Germany-only result set.

jal2 Sunday, March 30, 2008 7:24 PM

Hi jal2

I tried it agian with DataGridView1.Columns("EmployeeID").ReadOnly set to True and it worked.

I don't know why it didn't work before.

I have found that if I add the following line of code

bs.Filter = "FirstName='Robert'"

after the line of code