Solved

Using table as cmbo rowsource in .NET

Posted on 2009-04-08
14
196 Views
Last Modified: 2013-11-26
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!!!!
0
Comment
Question by:c9k9h
  • 8
  • 5
14 Comments
 
LVL 3

Expert Comment

by:Andy Green
ID: 24097538
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
 
LVL 15

Expert Comment

by:spprivate
ID: 24097554

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
 

Author Comment

by:c9k9h
ID: 24097645
I'm using VB.NET
0
 
LVL 15

Expert Comment

by:spprivate
ID: 24097711
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
 

Author Comment

by:c9k9h
ID: 24097928
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
 

Author Comment

by:c9k9h
ID: 24097997
Ooops didn't add
Imports System.Data.SqlClient
0
 
LVL 15

Expert Comment

by:spprivate
ID: 24098041
make sure you have control.databind() as well
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:c9k9h
ID: 24098079
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
 
LVL 15

Expert Comment

by:spprivate
ID: 24098131
Your connection object is 'conn'  but in your statement you have mentioned 'con'
Also please post the whole code including the imports
0
 

Author Comment

by:c9k9h
ID: 24098195
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
 
LVL 15

Accepted Solution

by:
spprivate earned 500 total points
ID: 24098502
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
 

Author Comment

by:c9k9h
ID: 24098792
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
 

Author Comment

by:c9k9h
ID: 24098885
Ah, Nevermind! I'm sorry - I had both the display member and Value set to the same thing! sorry!

Works Perfect!!!
0
 

Author Closing Comment

by:c9k9h
ID: 31568039
VERY Helpful and right on - Thanks so much!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Log4Net custom Appender not being fired. 5 44
Need to Modify a Script I found 5 77
Code enhancement 5 12
String manipulation 15 49
Normally the drop down box control found in the .Net framework tools is able to select just one data and value at a time, which is displayed on the text area.   But what if you want to have multiple values to be selected in the drop down box? As …
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now