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:
Followed by this on the form load (SQL in here works okay):
Though this doesn't work...:
Apologies in advance for the stupid question.
Thanks,
VL
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:
VB.NET:
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):
VB.NET:
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...:
VB.NET:
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