Databinding

macca

Member
Joined
Mar 3, 2005
Messages
7
Programming Experience
Beginner
I have a sqlserver table with values stored in it. I am trying to write the values to a dropdownlist.
Someone told me that to do this properly in VB.net that I should consider using complex Databinding.

Has anyone any useful resources on databinding or any code snippets demonstrating code being pulled from a table and put into a dropdownlist using complex databinding.

Thanks in advance,

Macca
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
This may help

I have done something like that two different ways:
Both ways loop through a grid bound to the SQL dataset,
that way I can filter the grid to get only the records needed.

The first way stores a record ID in a temporary Table(TempGage) and binds a Grid to the table.

Public TempGage As DataTable

'Loop through Grid and store ID Value
GageGrid.Row = 0
Dim c As Integer = GageGrid.VisibleRows
Dim i As Integer
TempGage = New DataTable("TempGage")
Dim TestRow1 As DataRow
Dim GageId As DataColumn = New DataColumn("GageId")
GageId.DataType = System.Type.GetType("System.String")
TempGage.Columns.Add(GageId)
For i = 1 To c
Try
TestRow1 = TempGage.NewRow()
TestRow1.Item("GageId") = GageGrid.Columns("Gage ID").Value
TempGage.Rows.Add(TestRow1)
Catch ex As Exception
'do nothing
End Try
GageGrid.Row += 1
Next
TempGrid.DataSource = TempGage


The second way adds lines to a checklistbox(clbOperations) control from a Table(tblSF) combining 2 fields into one string.

Dim counter As Integer
Dim OpNumberandName As String
clbOperations.Items.Clear()
OpCounter = 0
For counter = 0 To Me.BindingContext(DsInspFormsShopFloor1, "Shop Floor").Count - 1
Dim tblSF As DataTable
tblSF = DsInspFormsShopFloor1.Tables("Shop Floor")
Dim drCurrentSF As DataRow
drCurrentSF = tblSF.Rows(counter)
OpNumberandName = drCurrentSF("Operation Number") & " " & drCurrentSF("Description")
clbOperations.Items.Add(OpNumberandName)
OpCounter += 1
Next

Hope one of these examples will help.
 

macca

Member
Joined
Mar 3, 2005
Messages
7
Programming Experience
Beginner
David,


Thanks for your reply. I think that I might try using the code with the tempgage datatable in it.

i want to combine it with the following HTML on a page I have:
'<td>
'<select id="state" runat="server" NAME="state">
'<option selected>CA</option>
'<option>IN</option>
'<option>KS</option>
'<option>MD</option>
'<option>MI</option>
'<option>OR</option>
'<option>TN</option>
'<option>UT</option>
'</select>
'</td>

I want to replace the list of options and call the list from the database using the code you supplied.

Would the best place to put your code be in the page_load?

Only thing with that is that the public is not allowed in subroutine.

also could you comment your code as I am very much a beginner in this field.

Thanks,

Macca
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
Public statements

The public statement would go at the very top of the code so the whole form can use it, under

Public Class Form1
Inherits System.Windows.Forms.Form
Public TempGage As DataTable

I would put the code under a button push event, that way you can control when it happens.
 

macca

Member
Joined
Mar 3, 2005
Messages
7
Programming Experience
Beginner
David,


How does the code you supplied me read the data from the database using the datatable?

Macca
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
'Loop through Grid and store ID Value
GageGrid.Row = 0 'set the current row to the first row
Dim c As Integer = GageGrid.VisibleRows 'c is the number of rows in the grid
Dim i As Integer ' i is a counter used in the loop
TempGage = New DataTable("TempGage") ' establish a blank datatable
Dim TestRow1 As DataRow ' establish a data row
Dim GageId As DataColumn = New DataColumn("GageId") ' establish a column name
GageId.DataType = System.Type.GetType("System.String") ' establish a column name data type
TempGage.Columns.Add(GageId) 'add the column
For i = 1 To c ' loop through each row of the supplying grid
Try
TestRow1 = TempGage.NewRow() ' Add a new row to the blank table
TestRow1.Item("GageId") = GageGrid.Columns("Gage ID").Value ' set the column value to the newly added row
TempGage.Rows.Add(TestRow1) ' update the blank Table with the new row
Catch ex As Exception
'do nothing
End Try
GageGrid.Row += 1 ' move to the next row of the supplying grid
Next ' do it all again until the end of the supplying grid
TempGrid.DataSource = TempGage 'Set the datasource to the new Table
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
The second example reads from a datatable
Dim tblSF As DataTable 'this is the table to read from
tblSF = DsInspFormsShopFloor1.Tables("Shop Floor") 'this sets the table to a database dataset
Dim drCurrentSF As DataRow 'this is the row of the table to read from
drCurrentSF = tblSF.Rows(counter) 'this sets the row number - starts at 0


Sounds like you need to combine the two examples - use part of each one - you'll get it!

 
Top Bottom