My friend and I are trying to populate a datagrid with a database, and then save it back to the table when the user clicks save. The problem is that we cannot update the table unless we call the primary key in the query. What we want to do is hide the primary key column in the datagrid. So how do you hide a column in the datagrid? Here is the relevant code in case you need to know anything about what we are using:
The Form:
and our database class:
Thank you for your time!
The Form:
VB.NET:
Public Class frmCategories
Inherits System.Windows.Forms.Form
[Windows Form Designer generated code ]
Dim ds As DataSet
Dim db As clsDB
Private Sub frmCategories_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
db = New clsDB("DB\Capstone.mdb")
Dim sql As String
sql = "SELECT * FROM tblCategory"
ds = db.getDataSet(sql)
dgCategories.DataSource = ds.Tables(0)
' dgCategories.TableStyles(0).GridColumnStyles.Clear()
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
db.UpdateDataSet(ds)
End Sub
End Classand our database class:
VB.NET:
Imports System.Data.OleDb
Public Class clsDB
Private connectionString As String
Private conn As OleDbConnection
Dim cmd As OleDbDataAdapter
Dim objCommandBuilder As New OleDbCommandBuilder(cmd)
Public Sub New(ByVal strConnect As String)
'Takes File path location of database
connectionString = "Provider=Microsoft.Jet.OlEDB.4.0; Data Source=" & strConnect
End Sub
Public Function getDataSet(ByVal sql As String) As DataSet
' Takes the SQL statement and returns a DataSet
Try
Dim ds As DataSet
conn = New OleDbConnection(connectionString)
conn.Open()
cmd = New OleDbDataAdapter(sql, conn)
ds = New DataSet
cmd.Fill(ds)
Return ds
Catch ex As Exception
MsgBox("Error Message: " & ex.Message & vbCrLf & "Stack Trace: " & ex.StackTrace, _
MsgBoxStyle.Critical, "Database Error getDataSet")
End Try
End Function
Public Function UpdateDataSet(ByRef ds As DataSet)
Try
cmd.Update(ds, "tblCategory")
Catch ex As Exception
MsgBox("Error Message: " & ex.Message & vbCrLf & "Stack Trace: " & ex.StackTrace, _
MsgBoxStyle.Critical, "Database Error UpdateDataSet")
End Try
End Function
Public Function Execute(ByVal sql As String) As Boolean
' Executes a Non Query Such as an Update
Try
conn = New OleDbConnection(connectionString)
conn.Open()
Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
cmd.ExecuteNonQuery()
cmd.Dispose()
conn.Close()
conn = Nothing
Return True
Catch ex As Exception
MsgBox("Error Message: " & ex.Message & vbCrLf & "Stack Trace: " & ex.StackTrace, _
MsgBoxStyle.Critical, "Database Error Execute")
Return False
End Try
End Function
Public Function getDataReader(ByVal sql As String) As Object
' Takes the SQL statement and returns a DataReader
Try
conn = New OleDbConnection(connectionString)
conn.Open()
Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
Dim dr As OleDbDataReader
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
cmd.Dispose()
cmd = Nothing
Return dr
Catch ex As Exception
MsgBox("Error Message: " & ex.Message & vbCrLf & "Stack Trace: " & ex.StackTrace & _
vbCrLf & "Target Site: " & ex.TargetSite.Name & vbCrLf & "Source: " & ex.Source.ToString, _
MsgBoxStyle.Critical, "Database Error getDataReader")
End Try
End Function
Public Function getRecordset(ByVal sql As String) As Object
'Must close connection after closing recordset
Try
conn = New OleDbConnection(connectionString)
conn.Open()
Dim rs As ADODB.Recordset = New ADODB.Recordset
rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
Return rs
Catch ex As Exception
MsgBox("Error Message: " & ex.Message & vbCrLf & "Stack Trace: " & ex.StackTrace, _
MsgBoxStyle.Critical, "Database Error getRecordset")
End Try
End Function
Public Function close()
' Destroys the connection if it still exists
If Not conn Is Nothing Then
conn.Close()
conn = Nothing
End If
End Function
End Class
