SQL Connection

[VillaLion]

Ok, possibly one of the stupidest questions ever asked here but I've been struggling with this problem for a couple of hours. Though in my defense I've only been a vb.net programmer for about 3 hours so.......

I want to update a SQL table. I have managed to retrieve contents of an SQL query into a DataGridView. I've also managed to get a seperate query to populate a combo box within the DGV.

I am however struggling to update the table... it would appear as though there is no data connection... the query times out after about 30 seconds with some really long message but I have summarised as "The server was not found or was not accessable."

This is the class for my form that contains the DGV:

Imports System.Data.SqlClient


Public Class Form1
    Public query As New Query
    Private con As New SqlConnection("Data Source=SQL-SERVER;Initial Catalog=Expenses;Integrated Security=True")

Followed by this on the form load (SQL in here works okay):

  Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'CREATE CHECKBOX
        Dim chk As New DataGridViewCheckBoxColumn() With {.HeaderText = "SELECT", .Name = "chk", _
                                                          .AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells, _
                                                          .FlatStyle = FlatStyle.Standard, .ThreeState = False}
        'CREATE COMBOBOX
        Dim CMD As New SqlCommand
        Dim cbb As New DataGridViewComboBoxColumn() With {.HeaderText = "CATEGORY"}
        CMD.Connection = con
        CMD.CommandText = "SELECT Category FROM InformationCategory"
        con.Open()


        Dim Reader As SqlDataReader
        Reader = CMD.ExecuteReader


        While Reader.Read
            cbb.Items.Add(Reader.Item("Category"))
        End While


        con.Close()


        'ADD BUTTON
        Dim btnDelete As New DataGridViewButtonColumn() With {.HeaderText = "DELETE", .Text = "DELETE"}


        'DataGridView1.Columns.Insert(0, chk)
        DataGridView1.Columns.Add("", "ID")
        DataGridView1.Columns.Add("", "DATE")
        DataGridView1.Columns.Add("", "CARD NO.")
        DataGridView1.Columns.Add("", "DESCRIPTION")
        DataGridView1.Columns.Add("", "VALUE")
        'DataGridView1.Columns.Add("", "VALUE")
        DataGridView1.Columns.Insert(4, cbb)
        'DataGridView1.Columns.Insert(6, btnDelete)
        'DataGridView1.Columns.Add("", "ID")
        DataGridView1.Columns.Add("", "COMMENTS")


        DataGridView1.Columns(0).Width = 30
        DataGridView1.Columns(1).Width = 70
        DataGridView1.Columns(2).Width = 100
        DataGridView1.Columns(3).Width = 500
        DataGridView1.Columns(4).Width = 205
        DataGridView1.Columns(5).Width = 70
        DataGridView1.Columns(6).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill


        RemoveHandler DataGridView1.CellValueChanged, AddressOf DataGridView1_CellValueChanged
        FillGrid()
        AddHandler DataGridView1.CellValueChanged, AddressOf DataGridView1_CellValueChanged


    End Sub

Though this doesn't work...:

Private Sub DataGridView1_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged


        Dim sql As String


        sql = "UPDATE Database.dbo.Table SET COMMENTS='" + DataGridView1.CurrentRow.Cells(6).Value + "'"


        MessageBox.Show("row index: " & e.RowIndex & Environment.NewLine & "old value: " & cellValue)


        MessageBox.Show(sql)


        con.Open()


        query.ExcuteQuery(sql)


        con.Close()
    End Sub

Apologies in advance for the stupid question.

Thanks,
VL

 

[jmcilhinney]

You're going about things the wrong way. If you're using a grid then you should use the Fill method of a data adapter or table adapter to populate a DataTable and then bind that to the grid. The user makes their edits and then, when you want to save the changes, you use the Update method of the same data adapter or table adapter to save all the changes in a single batch.