• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

ASP.net adding items to a DropDownList VB code

In ASP.net I have a backend Access database.
On my WebForm, I have several DropDownLists.
I want to populate each of these with the unique items from
ecah column of Table1 in my backend database called GC.accdb.
What VB.net code would I use to do this?
I have learnt so far that the SQL code is "Select Distinct MyColumn From MyTable"
0
Murray Brown
Asked:
Murray Brown
  • 3
  • 2
1 Solution
 
DhaestCommented:
I would suggest to retrieve all the data at once (the complete table myTable)

And then use a select distinc for each column that you want to bind to a combobox
http://www.thescarms.com/dotnet/BindCombobox.aspx
Dim OleDbConnection As System.Data.OleDb.OleDbConnection
Dim OleDbSelectCommand As System.Data.OleDb.OleDbCommand

OleDbSelectCommand.CommandText = "SELECT * FROM myTable"
OleDbSelectCommand.Connection = OleDbConnection

       
Dim myDataAdapter As System.Data.OleDb.OleDbDataAdapter
myDataAdapter = New System.Data.OleDb.OleDbDataAdapter()

myDataAdapter .SelectCommand = OleDbSelectCommand

Dim myDataSet As System.Data.DataSet
 
myDataAdapter.Fill(myDataSet )

DataTable dt=SelectDistinct("myTable", myDataSet .tables("myTable"),"MyColumn ")








private bool ColumnEqual(object A, object B)
{

if ( A == DBNull.Value && B == DBNull.Value ) 
return true; 
if ( A == DBNull.Value || B == DBNull.Value ) 
return false; 
return ( A.Equals(B) ); 

}


public DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName)
{ 
DataTable dt = new DataTable(TableName);
dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

object LastValue = null; 
foreach (DataRow dr in SourceTable.Select("", FieldName))
{
if ( LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])) ) 
{
LastValue = dr[FieldName]; 
dt.Rows.Add(new object[]{LastValue});
}
}
if (ds != null) 
ds.Tables.Add(dt);
return dt;
}

Open in new window

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi.  Thanks. Where do I put that code. It is not VB code
0
 
DhaestCommented:
Just in your class
(i just converted i through http://www.developerfusion.com/tools/convert/csharp-to-vb/, it's possible that there are some syntax errors)
Private Function ColumnEqual(A As Object, B As Object) As Boolean

	If A = DBNull.Value AndAlso B = DBNull.Value Then
		Return True
	End If
	If A = DBNull.Value OrElse B = DBNull.Value Then
		Return False
	End If
	Return (A.Equals(B))

End Function


Public Function SelectDistinct(TableName As String, SourceTable As DataTable, FieldName As String) As DataTable
	Dim dt As New DataTable(TableName)
	dt.Columns.Add(FieldName, SourceTable.Columns(FieldName).DataType)

	Dim LastValue As Object = Nothing
	For Each dr As DataRow In SourceTable.[Select]("", FieldName)
		If LastValue Is Nothing OrElse Not (ColumnEqual(LastValue, dr(FieldName))) Then
			LastValue = dr(FieldName)
			dt.Rows.Add(New Object() {LastValue})
		End If
	Next
	If ds IsNot Nothing Then
		ds.Tables.Add(dt)
	End If
	Return dt
End Function

Open in new window

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
I am lost...don't understand this
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now