Using table as cmbo rowsource in .NET

Hello,

I'm trying to select a couple of columns from a table to display in a combo box.

VBA would be something like me.cbobox.rowsource = "Select something from somewhere"

It's been a WHILE since I've played around with .NET or even VB6 - just trying to get refreshed on some simple functions!

I'm thinking something like:

sSQL = "SELECT EmpID FROM Employees"
me.cboCustodian.rowsource? =  sSQL

not exactly of course... Also... What is the method of defining the bound column? I know that you can't specify how many colums are in a combo box or list box in VB or .NET it just displays however many you specify in your select statement.

All help greatly appreciated!!!!
c9k9hAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
spprivateConnect With a Mentor Commented:
Here is the final code.I was using sql data client but your db was access.So i had to use oledb.
Imports System.Data
Imports System.Data.OleDb
 
Public Class Form1
 
    Inherits System.Windows.Forms.Form
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
 
        Dim sConnectionString, sSQL As String
 
        'SQL Connection String To Backend
        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb"
 
        sSQL = "SELECT EmployeeID as empid FROM Employees"
 
        Dim conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
        Dim cmd As New System.Data.OleDb.OleDbCommand(sSQL, conn)
        Dim dr As System.Data.OleDb.OleDbDataReader
        'Populate ComboBox
        Try
            conn.Open()
            Dim dat As OleDbDataAdapter = New OleDbDataAdapter(sSQL, conn)
            Dim dt As New DataSet()
            dat.Fill(dt, "Employees")
            cboCustodian.DataSource = dt.Tables("Employees").DefaultView
            cboCustodian.DisplayMember = "empid"
            cboCustodian.ValueMember = "empid"
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            conn.Close()
        End Try
 
 
 
    End Sub
 
 
End Class

Open in new window

0
 
Andy GreenCommented:
I'd use a DataSource control, you dont say what database you have so use the one appropriate for your database.
You can write your SQL into the datasource control, and set the datasource for your dropdown control to the datasource control.
Andy
0
 
spprivateCommented:

Query="select StudentID, Name From StudentRecord"
com = new sqlcommand
com.commandText = Query
da= new sqldataadapter
dt = new DataTable
da.selectcommand = com
da.fill(dt)
cmb1.DataSource = dt
cmb1.ValueMember = dt.columns("Name").ToString
cmb1.DisplayMembers = dt.columns("StudentID").ToString

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
c9k9hAuthor Commented:
I'm using VB.NET
0
 
spprivateCommented:
Here is a full code for you assuming AsP.net with VB.net and SQL Server
<%@ Page language="VB" Debug="false" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script language="VB" runat="server">
Sub Page_Load(Source As Object, E As EventArgs)
  if Not Page.IsPostBack Then
    Dim oConn As SQLConnection
    Dim oComm As SQLCommand
    Dim oReader As SQLDataReader
    Dim sSQL As String
    Dim sConn As String
 
    sSQL= "SELECT au_id, au_fname + ' ' + au_lname AS AuthorName "
    sSQL+= "FROM Authors"
    sConn= "server=(local);database=pubs;Trusted_Connection=yes"
 
    oConn = New SQLConnection(sConn)
    oConn.Open()
 
    oComm = New SQLCommand(sSQL,oConn)
    oReader = oComm.ExecuteReader()
 
    oAuthors.DataSource = oReader
    oAuthors.DataBind()
  end if
End Sub
 
Sub Go_Click(Src As Object, E As EventArgs)
  SelectedItem.Text = "You picked " & oAuthors.SelectedItem.Text & " (" & _
    oAuthors.SelectedItem.Value & ")."
End Sub
</script>
<html>
<head>
  <title>Populate A Drop-down List From A SQL Server Database</title>
</head>
<body>
<form runat="server">
  Please select an author:
  <asp:DropDownList ID="oAuthors" DataTextField="AuthorName"
DataValueField="au_id" Runat="server" />
  <asp:Button ID="Go" Text="Go" OnClick="Go_Click" Runat="server" />
  <br />
  <br />
  <asp:Label ID="SelectedItem" Runat="server" />
</form>
</body>
</html>

Open in new window

0
 
c9k9hAuthor Commented:
I'm using straight VB.NET - Just a windows App


I'm trying to use something like the code below - It's not liking SqlDataAdapter though - stating it is undefined





Try
            conn.Open()
            Dim dat As SqlDataAdapter = New SqlDataAdapter("select EmpID from Employees ", con)
            Dim dt As New DataSet()
            dat.Fill(dt, "table")
            cboCustodian.DataSource = dt.Tables("Employees").DefaultView
            cboCustodian.DisplayMember = "EmpID"
            cboCustodian.ValueMember = "EmpID"
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            conn.Close()
        End Try

Open in new window

0
 
c9k9hAuthor Commented:
Ooops didn't add
Imports System.Data.SqlClient
0
 
spprivateCommented:
make sure you have control.databind() as well
0
 
c9k9hAuthor Commented:
Okay - It seems like this will work for what I'm trying to accomplish, however, I'm still getting errors in debug....


I'm getting the following error for:

New SqlDataAdapter("select EmpID from Employees ", conn)

"C:\Documents and Settings\diw07\My Documents\Visual Studio Projects\Medical Records Requests2\Form1.vb(289): Overload resolution failed because no accessible 'New' can be called with these arguments:
    'Public Overloads Sub New(selectCommandText As String, selectConnection As System.Data.SqlClient.SqlConnection)': Value of type 'System.Data.OleDb.OleDbConnection' cannot be converted to 'System.Data.SqlClient.SqlConnection'.
    'Public Overloads Sub New(selectCommandText As String, selectConnectionString As String)': Value of type 'System.Data.OleDb.OleDbConnection' cannot be converted to 'String'."




0
 
spprivateCommented:
Your connection object is 'conn'  but in your statement you have mentioned 'con'
Also please post the whole code including the imports
0
 
c9k9hAuthor Commented:
Okay here we go

Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
 
    Inherits System.Windows.Forms.Form
 
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
 
        Dim sConnectionString, sSQL As String
 
        'SQL Connection String To Backend
        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=o:UA\Finance\LTC\MIS\Department\Databases\phone memo backend.mdb"
 
        sSQL = "SELECT EmpID FROM Employees"
 
        Dim conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
        Dim cmd As New System.Data.OleDb.OleDbCommand(sSQL, conn)
        Dim dr As System.Data.OleDb.OleDbDataReader
        conn.Open()
 
        
        conn.Close()
        Dim Newdatasource As DataGridTextBoxColumn
 
        'Populate ComboBox
        Try
            conn.Open()
            Dim dat As SqlDataAdapter = New SqlDataAdapter("select EmpID from Employees ", conn)
            Dim dt As New DataSet()
            dat.Fill(dt, "Employees")
            cboCustodian.DataSource = dt.Tables("Employees").DefaultView
            cboCustodian.DisplayMember = "EmpID"
            cboCustodian.ValueMember = "EmpID"
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            '   conn.Close()
        End Try
 
 
 
    End Sub
 
 
End Class

Open in new window

0
 
c9k9hAuthor Commented:
Okay I think we are almost there... Thank you so much for your help (i've upped the Points)

When running I'm getting the msg box saying "Could not bind to the new display member. Paremeter name:newDisplayMember"

in the combo box i'm getting a ton of rows reading "System.Data.DataRowView"



0
 
c9k9hAuthor Commented:
Ah, Nevermind! I'm sorry - I had both the display member and Value set to the same thing! sorry!

Works Perfect!!!
0
 
c9k9hAuthor Commented:
VERY Helpful and right on - Thanks so much!
0
All Courses

From novice to tech pro — start learning today.