Hi,
I tried your code and changed it a lot.
I made from it in a workingsample situation but had to change a lot.
(It is still not what I would make, but I did not want to change to much
from your originalcode)
I also used sqlclient.sql........ that you can everywhere change in
oledb.oledb....... and then use the right connection string.
I did use the northwind database because I have of course not your database
and therfore I had to remove some things.
Under the sample I give some comments on your code.
I hope this helps?
Cor
\\\
Private Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Dim Conn As New SqlClient.SqlConnection _
("Server=xxxxxxx;DataBase=northwind;Integrated Security=SSPI")
PopulateDropDownListbox2("EmployeeID", "Employees",
DropDownList1, Conn, True, )
End If
End Sub
Private Function PopulateDropDownListbox2 _
(ByVal iField As String, _
ByVal iTable As String, _
ByVal iDDL As DropDownList, _
ByVal iConnection As SqlClient.SqlConnection, _
Optional ByVal iDistinct As Boolean = True, _
Optional ByVal iAdditionalQuery As String = "") As
System.Data.DataSet
' Create a recodset from the Connection, use distinct if required
Dim sql As String = "Select EmployeeID, FirstName from Employees"
Dim ds As New DataSet
Dim da As New SqlClient.SqlDataAdapter(sql, iConnection)
da.Fill(ds, iTable)
' Populate and update the Drop Down Listbox
iDDL.DataTextField = iField
iDDL.DataValueField = "Firstname"
iDDL.DataSource = ds.Tables(0)
iDDL.DataBind()
' Clear Up
iConnection.Close()
Return ds
End Function
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Label1.Text = Me.DropDownList1.SelectedValue
End Sub
End Class
///
Private Function PopulateDropDownListbox2(ByVal iField As
String, _
ByVal iTable As String, _
ByVal iDDL As DropDownList, _
ByVal iConnection As ADODB.Connection, _
Optional ByVal iDistinct As Boolean = True, _
Optional ByVal iAdditionalQuery As String = "")
As System.Data.DataSet
Why you make this difficult function. The most important thing the sql
string is not in it, when that was I could understand it, but than you do
not need that two last optional parameters.
' Create a recodset from the Connection, use
distinct if required
Dim recs As New ADODB.Recordset, sql As String
Why you need a recordset you are not using it and set it even to nothing at
the end
If iDistinct = True Then
sql = "Select Distinct " & iField & " from "
& iTable
Else
sql = "Select " & iField & " from " & iTable
End If
' Apply a filter string
If iAdditionalQuery = "" Then
' do nothing
Else
sql = sql & " WHERE " & iAdditionalQuery
End If
See for that my first comment
' Query the connection
recs = iConnection.Execute(sql)
' Populate a tempory data table and data adapter
Dim dt As New DataTable
Dim da As New System.Data.OleDb.OleDbDataAdapter
da.Fill(dt, recs)
You have to file a dataset, just change dt for ds and datatable for datset
but see my code to do it right there are more errors in that
' Create a dataset
Dim ds As New DataSet
ds.Tables.Add(dt)
See before and my code
' Populate and update the Drop Down Listbox
iDDL.DataTextField = iField
iDDL.DataSource = ds.Tables(0)
iDDL.DataBind()
Return ds
' Clear Up
recs = Nothing
dt = Nothing
da = Nothing
ds = Nothing
Why is all that setting to nothing I see it nowhere in the documentation
There is no close of the connection.
End Function
