By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,216 Members | 1,477 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,216 IT Pros & Developers. It's quick & easy.

VB.NET/ADO.NET populating a dataset

JordanMartz
P: 2
''' <summary>
''' Populates the DataGridView
''' </summary>
Private Sub PopulateDataGridView()
' Set the column header names.
userAccessGrid.ColumnCount = 5
userAccessGrid.RowsDefaultCellStyle.BackColor = Color.White
userAccessGrid.RowHeadersVisible = True
userAccessGrid.Name = "User Access Grid"
userAccessGrid.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Raised
userAccessGrid.CellBorderStyle = DataGridViewCellBorderStyle.Raised
userAccessGrid.ColumnHeadersHeight = 20
' Initialize basic DataGridView properties.
userAccessGrid.Dock = DockStyle.Fill
userAccessGrid.BackgroundColor = Color.LightGray
userAccessGrid.BorderStyle = BorderStyle.Fixed3D
userAccessGrid.Columns(0).Name = "AccessLevelDescription"
userAccessGrid.Columns(0).DataPropertyName = "AccessLevelDescription"
userAccessGrid.Columns(0).Width = 40
userAccessGrid.Columns(0).HeaderText = "Access Level Description"
userAccessGrid.Columns(0).DefaultCellStyle.Font = New Font("Arial", 7, FontStyle.Regular)
userAccessGrid.Columns(1).Name = "NoAccess"
userAccessGrid.Columns(1).DataPropertyName = "NoAccess"
userAccessGrid.Columns(1).Width = 40
userAccessGrid.Columns(1).HeaderText = "No Access"
userAccessGrid.Columns(1).DefaultCellStyle.Font = New Font("Arial", 7, FontStyle.Regular)
userAccessGrid.Columns(2).Name = "ReadOnly"
userAccessGrid.Columns(2).DataPropertyName = "ReadOnly"
userAccessGrid.Columns(2).Width = 40
userAccessGrid.Columns(2).HeaderText = "Read Only"
userAccessGrid.Columns(2).DefaultCellStyle.Font = New Font("Arial", 7, FontStyle.Regular)
userAccessGrid.Columns(3).Name = "NormalAccess"
userAccessGrid.Columns(3).DataPropertyName = "NormalAccess"
userAccessGrid.Columns(3).Width = 40
userAccessGrid.Columns(3).HeaderText = "Normal Access"
userAccessGrid.Columns(3).DefaultCellStyle.Font = New Font("Arial", 7, FontStyle.Regular)
userAccessGrid.Columns(4).Name = "TotalAccess"
userAccessGrid.Columns(4).DataPropertyName = "TotalAccess"
userAccessGrid.Columns(4).Width = 40
userAccessGrid.Columns(4).HeaderText = "Total Access"
userAccessGrid.Columns(4).DefaultCellStyle.Font = New Font("Arial", 7, FontStyle.Regular)
userAccessGrid.SelectionMode = DataGridViewSelectionMode.CellSelect
userAccessGrid.MultiSelect = False
userAccessGrid.BackgroundColor = Color.LightBlue
userAccessGrid.ColumnHeadersDefaultCellStyle.Font = New Font("Arial", 8, FontStyle.Regular)
'--------------------------------------------------------------------------------------------
Dim appdesc_selectStatement As String = "Select access.level, pas_appitem FROM(access)Where access.uname = '' ORDER BY appitem"
Dim level_selectStatement As String = "Select accitem.appdesc, acclevels.level, accitem.appitem FROM(accitem, acclevels, access)Where access.uname = '' And accitem.appitem = access.appitem And access.level = acclevels.level Order By accitem.appitem"

Dim dt_Appdesc As New DataSet()
v = clsDataAnalyzerJetOleDb.MSAccessOleDb.PopulateData Set(appdesc_selectStatement)
Dim dt_Level As New DataSet()
dt_Level = clsDataAnalyzerJetOleDb.MSAccessOleDb.PopulateData Set(level_selectStatement)

Dim ds As DataSet = New DataSet()

Dim Dt_UserAccessDataSet As New DataSet()

Dim DescriptionColumns() As DataColumn = New DataColumn() {ds.Tables(0).Columns("appitem"), ds.Tables(0).Columns("appdesc")}
Dim AccessLevelColumns() As DataColumn = New DataColumn() {ds.Tables(1).Columns("level"), ds.Tables(1).Columns("desc")}
Dim Tran_Detail As New DataRelation("DescriptionColumns", ds.Tables(0).Columns("AccessLevelDescription"), AccessLevelColumns(1).Columns("No Access", "ReadOnly", "NormalAccess", "TotalAccess"))


'Dim tablearray() As DataTable = New DataTable(3) {}
'tablearray(0) = dt_Appdesc.Tables("appitem")
'tablearray(1) = mySecondDataSet.Tables("AnotherTable")
'tablearray(2) = myThirdDataSet.Tables(0)

'get a DataTableReader over these tables
'myDataTableReader = myFirstDataSet.GetDataReader(tablearray)

' Section for filling the Fields after the Data has been looped and read into the Grid
Dim OleDBConn As OleDbConnection = New OleDbConnection(MSAccessOleDb.ConnectionString())
OleDBConn.Open()

Dim cmd As OleDbCommand = New OleDbCommand("appdesc_selectStatement", dt_Appdesc)
Dim daTransactions As OleDbDataAdapter = New OleDbDataAdapter(cmd)
daTransactions.Fill(ds, "dt_Appdesc")

Dim cmdDetails As OleDbCommand = New OleDbCommand("level_selectStatement", dt_Level)
Dim daTransactionDetails As OleDbDataAdapter = New OleDbDataAdapter(cmdDetails)
daTransactionDetails.Fill(ds, "dt_Level")

DataRelations(Of String, DescriptionColumns(), AccessLevelColumns(), Boolean)()

Dim dx As New DataGridViewTextBoxColumn() = New DataGridViewTextBoxColumn()

'Pass in ds as DataSet, and "Transactions as TblName

Dim userAccessDataRow As DataRow = dt_Appdesc.NewRow()
Dim userAccessDataAdapter As New OleDbDataAdapter()
For Each userAccessDataRow As DataRow
Dim i As Integer
For i = 0 To dt_Appdesc.Rows.Count - 1
'Loop through Query B
'For each Row in DataTableB
For Each userAccessDataRow As DataRow In userAccessTable.Rows
'The level from the query associates to the proper checkbox in the row
accessQuery = accessQuery + 1
'Loop Through Query A's DataTable
i = 0
For Each userAccessDataRow In userAccessTable.Rows
userAccessTable.Columns(1)(0) = userAccessDataRow(0)(1).value
'i = i + 1 'So, that we continue looping
'Level2 = Row(0).value
'If Level2 = Level1 Then 'Set the level
CheckBox in Column(1)
Exit For
Exit For
Next
Next
Next
Next
Try
' Section for filling the Fields after the Data has been looped and read into the Grid
If dt_Appdesc.Rows.Count = 0 Then
Dim OleDBConn As OleDbConnection = New OleDbConnection(GetOleDBConn())
OleDBConn.Open()
userAccessDataAdapter.SelectCommand = New OleDbCommand(appdesc_selectStatement, OleDBConn)
userAccessDataAdapter.Fill(dt_Appdesc)
userAccessDataAdapter.InsertCommand = New OleDbCommandBuilder(userAccessDataAdapter).GetInse rtCommand()
userAccessDataRow("uname").Trim()
dt_Appdesc.Rows.Add(userAccessDataRow)
OleDBConn.Close()
OleDBConn.Dispose()
Else
Throw (New Exception("Error attempting to insert duplicate data."))
End If
Catch messerr As Exception
Call ShowErrorMessage(messerr, "listboxUserGroup_SelectedIndexChanged", "frm_security")
End Try
' Add the rows to the DataGridView.
Dim rows() As Object = {}
Dim rowArray As Object()
For Each rowArray In rows
userAccessGrid.Rows.Add(rowArray)
Next rowArray
' Adjust the row heights so that all content is visible.
userAccessGrid.AutoResizeRows(DataGridViewAutoSize RowsMode.AllCellsExceptHeaders)
End Sub


Here's my nasty 150 line DataSet, DataRelation, DataObject, DataBullshit problem. As you can tell, this isn't the first day that I've been working on this function. If you have any bones to through in my general direction, I'd be incredibly grateful. My goal is to get a two Datasets and combine them after I take the user access level and turn it sideways. Then populate the gridview, then I'm going to write back to the database from the selected values.

Gridview looks like
Appdesc NoAccess Read-Only Normal Total Access
blah blah X X X X
blah blah X X X X
Aug 21 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.