For the life of me I can't figure out why this is happening. i've copied this for a working site and made some changes, but on this database, I'm getting wierd results.
Here's the code below.
I've added some stuff for debugging. Basically when I run this, some wierd things are being returned.
list.Add("fieldCount= " + objDataReader.FieldCount.ToString) returnes 1
list.Add(objDataReader.Item(0).ToString) returnes State:
list.Add(objDataReader.GetValue(0).ToString()) also returnes State:
Which tells me that it's just returning the Table name. What I do what is the values in the table.
If I query the DB directly with
select distinct "State:" from Accomodations where "State:"<>NULL,
I get
State:
AL
AR
AZ
GA
etc...
in a table.
Protected Sub DropDownList2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList2.SelectedIndexChanged
Dim pType As String
pType = DropDownList2.SelectedValue.ToString
Dim strConnection As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=somedomain.com\app_data\SomeDomain.accdb"
Dim strSQL As String = "select DISTINCT ""State:"" from " + pType + " where ""State:""<>NULL"
Dim objConnection As New OleDbConnection(strConnection)
Dim objCommand As New OleDbCommand(strSQL, objConnection)
Dim objDataReader As OleDbDataReader
Dim list As New ArrayList
list.Add("[Select]")
Dim count As Integer
Try
count = count + 1
objConnection.Open()
count = count + 1
objDataReader = objCommand.ExecuteReader()
count = count + 1
list.Add("fieldCount= " + objDataReader.FieldCount.ToString)
Do While objDataReader.Read() = True
list.Add(objDataReader.Item(0).ToString)
count = count + 1
Loop
list.Add(strSQL)
list.Add("Caught " + count.ToString)
objDataReader.Close()
objConnection.Close()
Catch ex As Exception
list.Add("Caught " + count.ToString + " " + ex.Message.ToString)
list.Add(strSQL)
End Try
DropDownList1.DataSource = list
DropDownList1.DataBind()
End Sub