geoff_1707

Please help the window looks very inviting at the moment. Being fairly new to VB 2005 I have strugled with this problem for more than a week and read so many articles but still seem to be missing the point

If you take a lokk at the code below the datagrid loads fine from the selected supplier but just can not get a grip on the update part. I know there are lots of articles on the site but I just seem to be going round in circles

Imports System.Data.SqlClient

Public Class Form1

Dim mySqlConnectionStr As String = _

("Data Source=CWE-DTDR;Initial Catalog=UsersTest;Integrated Security=SSPI;")

'Define Progam variables

Dim booSupActive As Boolean

Dim strSupCode As String

Dim strItemSupplier As String

Dim strItemCode As String

Dim strItemDescription As String

Dim intRow As Integer

Dim intCol As Integer

Dim Rows As Integer

Dim message As String

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

LoadSuppliers()

End Sub

Private Sub LoadSuppliers()

comSupplier.Items.Clear()

booSupActive = True

Dim mySqlConnection As New SqlConnection

Dim mysqlCommand As New SqlCommand

Dim mysqlDataReader As SqlDataReader

mySqlConnection.ConnectionString = mySqlConnectionStr

Dim p0 As New SqlParameter("@p0", booSupActive)

mysqlCommand.Parameters.Add(p0)

mysqlCommand.CommandText = _

("SELECT * FROM Suppliers WHERE supActive = @p0 ORDER BY supCode")

mysqlCommand.Connection = mySqlConnection

mySqlConnection.Open()

mysqlDataReader = mysqlCommand.ExecuteReader

While (mysqlDataReader.Read())

strSupCode = mysqlDataReader("supCode").ToString

comSupplier.Items.Add(strSupCode)

End While

mysqlDataReader.Close()

mySqlConnection.Close()

mysqlCommand.Parameters.Clear()

End Sub

Private Sub LoadDataGrid()

Dim mySqlConnection As New SqlConnection

Dim mySqlCommand As New SqlCommand

mySqlConnection.ConnectionString = mySqlConnectionStr

Dim p0 As New SqlParameter("@p0", strItemSupplier)

mySqlCommand.Parameters.Add(p0)

mySqlCommand.Connection = mySqlConnection

mySqlCommand.CommandType = CommandType.Text

mySqlCommand.CommandText = _

"SELECT * FROM Items WHERE itemSupplier = @p0 ORDER BY itemCode"

Dim mySqlDataAdapter As New SqlDataAdapter(mySqlCommand)

Dim myDataset As New DataSet

mySqlDataAdapter.Fill(myDataset)

mySqlConnection.Close()

Dim myDataTable As DataTable = myDataset.Tables(0)

'AddHandler myDataTable.ColumnChanged, New DataColumnChangeEventHandler(AddressOf ColumnChanged)

'I assume the Column changed must be a fuction or sub

DataGridView1.DataSource = myDataTable

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

End Sub

Private Sub comSupplier_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles comSupplier.SelectedIndexChanged

strItemSupplier = comSupplier.Text

LoadDataGrid()

End Sub

End Class

If someone could just explain how to update the database when button_1 is pressed I think I can work the rest out

Thanks




Re: Visual Basic General Updating SQL 2005 Database from datagrid

Bruno Yu - MSFT

geoff_1707,

According to your demand on updating database from the DataGrid control, I would like to provide you the following code snippet that can help you to update and save the column changes on the form, then refresh and show the updated data on the form.

Code Snippet

Public Sub RefreshData()

Dim connection As New SqlConnection(ConnectionString)

connection.Open()

Dim adapter As New SqlDataAdapter(GetAllAuthorsSqlString, connection)

Dim dataset As New DataSet

adapter.Fill(dataset)

adapter.Dispose()

connection.Close()

Dim table As DataTable = dataset.Tables(0)

AddHandler table.ColumnChanged, New DataColumnChangeEventHandler(AddressOf ColumnChanged)

datagridAuthors.DataSource = table

End Sub

Protected Sub ColumnChanged(ByVal sender As Object, ByVal e As DataColumnChangeEventArgs)

menuSaveChanges.Enabled = True

End Sub

Private Sub menuRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles menuRefresh.Click

RefreshData()

End Sub

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

RefreshData()

End Sub

Private Sub menuSaveChanges_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles menuSaveChanges.Click

SaveChanges()

End Sub

Public Sub SaveChanges()

Dim table As DataTable = CType(datagridAuthors.DataSource, DataTable)

Dim changedRows As New ArrayList

For Each row As DataRow In table.Rows

If row.RowState <> DataRowState.Unchanged Then

changedRows.Add(row)

End If

Next

If changedRows.Count = 0 Then

Return

End If

Dim connection As New SqlConnection(ConnectionString)

connection.Open()

Dim adapter As New SqlDataAdapter(GetAllAuthorsSqlString, connection)

Dim builder As New SqlCommandBuilder(adapter)

Dim rows() As DataRow = CType(changedRows.ToArray(GetType(DataRow)), DataRow())

adapter.Update(rows)

adapter.Dispose()

connection.Close()

menuSaveChanges.Enabled = False

End Sub

Hope that can help you.