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

Return SQL Data to A DropDown Box

Experts,

How do you return data to a drop down box?

I want the employee name in the drop down with the value EmployeeId.

The SQL is Select EmployeeId, Shortname
From EmployeeAnalysis


CONNECTION CODE
---------------------------------------------------
   Private Sub btnOpenConnection_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnOpenConnection.Click

        'Initialize a new instance of the OleDbConnection class
        objConnection = New OleDbConnection( _
                      "Provider=SQLOLEDB;Server=" & txtServer.Text & ";Database=tw_Cent_Time;UID=" & txtUserName.Text & ";PWD=" & txtPassword.Text & ";")

        Try
            'Open the connection
            objConnection.Open()
        Catch OleDbExceptionErr As OleDbException
            'Display the error
            MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
        Catch InvalidOperationExceptionErr As InvalidOperationException
            'Display the error
            MessageBox.Show(InvalidOperationExceptionErr.Message, "Access SQL")
        End Try

        'Check the state of the connection and report appropriately
        If objConnection.State = ConnectionState.Open Then
            lblStatus.Text = "Database connection is open"
        Else
            lblStatus.Text = "Database connection failed"
        End If
    End Sub
0
padgettbrian
Asked:
padgettbrian
  • 10
  • 5
  • 3
1 Solution
 
Brian CroweCommented:
store the data in a datatable and set that table as the datasource for your control and set the displaymember and valuemember properties accordingly.
0
 
padgettbrianAuthor Commented:
i wish i knew how to do that... can you code some of this?
0
 
Brian CroweCommented:
private dtData as new datatable
...
with dtdata.columns
   .add("column1", gettype(system.string))
   .add("column2", gettype(system.integer))
end with
...
dim cmd as new oledbcommand
dim dr as oledbdatareader
dim row as datarow

cmd.commandtext = "SELECT column1, column2 FROM myTable"
cmd.connection = objConnection

try
   cmd.connection.open
   dr = cmd.executereader
   while dr.read
      row = dtdata.newrow
      row("column1") = dr("column1")
      row("column2") = dr("column2")
      dtdata.rows.add(row)
   end while
catch ex as exception
   messagebox.show(ex.message)
finally
   if cmd.connection.state = connectionstate.open
      cmd.connection.close
   end if
end try

mycombobox.datasource = dtdata
mycombobox.displaymember = "column1"
mycombobox.valuemember = "column2"
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
padgettbrianAuthor Commented:
Holly lord what is that!
0
 
Brian CroweCommented:
LoL...what part is throwing you?
0
 
padgettbrianAuthor Commented:
The whole thing!

why is there a datagrid? I have no idea what the part below has to do with a combo box

try
   cmd.connection.open
   dr = cmd.executereader
   while dr.read
      row = dtdata.newrow
      row("column1") = dr("column1")
      row("column2") = dr("column2")
      dtdata.rows.add(row)
   end while
catch ex as exception
   messagebox.show(ex.message)
finally
   if cmd.connection.state = connectionstate.open
      cmd.connection.close
   end if
end try
0
 
padgettbrianAuthor Commented:
Big thanks btw...
0
 
wtconwayCommented:
Hey padge. Just give this a try. I imagine that you are familiar with basic OOP principles with the following code.

    Overloads Function GetDataTable(ByVal sql As String, ByVal Source As String) As DataTable 'this will get you your datatable object
        Dim conConnect As OleDbConnection = New OleDbConnection(Source)
        Dim dapAdapter As OleDbDataAdapter = New OleDbDataAdapter(sql, conConnect)
        Dim dstDataSet As New DataSet
        Dim tblDataTable As DataTable
        Try
            conConnect.Open()
            dapAdapter.Fill(dstDataSet)
            tblDataTable = dstDataSet.Tables(0)
            dstDataSet.Tables.Clear()
        Catch ex As OleDbException
            MsgBox(ex.Message)
        Finally
            conConnect.Close()
        End Try
        Return tblDataTable
    End Function

Public Sub LoadEmployees()
    Dim sql as String = "Select EmployeeId, Shortname From EmployeeAnalysis"
    Dim ds as String = "Provider=SQLOLEDB;Server=" & txtServer.Text & ";Database=tw_Cent_Time;UID=" & txtUserName.Text & ";PWD=" & txtPassword.Text & ";"
    Dim tbl as DataTable = GetDataTable(sql,ds) 'the employee table
    cboEmployees.DataSource = tbl
    cboEmployees.DisplayMember = "Shortname"
    cboEmployees.ValueMember = "EmployeeId"
End Sub

This assumes that your drop down list for the employees is named "cboEmployees" Since you are using the SQLOLEDB provider, my function will work just fine. I have an entire class devoted to OLEDB data operators. If you would like a copy of the class it reduces data calls down to what you see here. All you need is a reference to the DLL.

Let me know if this works.
0
 
padgettbrianAuthor Commented:
I pasted your code in and it has 2 blue lines.

1. Overloads Function - Statement cannot appear within a method body. End of method assumed.
2. tblDataTable - is used before it has been assigned a value. A null reference exception could result at runtime.

ANy Ideas????
0
 
wtconwayCommented:
The code I sent you should not be inside any other methods. This includes Functions and Subs. And for that matter, make sure it's not accidentally placed inside any properties. What seems to have happened is that you have pasted the code inside a function or sub you already had in the class.

This will most likely solve the 2nd problem as according to my code, tblDataTable is not used before it is instantiated.
0
 
padgettbrianAuthor Commented:
I fixed the first problem with the Overload but still have the tbDataTable one

Here is my Complete project Code...
-------------------------------------------------------------------------------

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class Form1
    'Form level variables
    Private objConnection As OleDbConnection

    Private Sub btnDatabase_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnDatabase.Click

        'Declare OpenFileDialog object
        Dim objOpenFileDialog As New OpenFileDialog

        'Set the OpenFileDialog properties
        With objOpenFileDialog
            .Filter = "Access Database (*.mdb)|*.mdb|All files (*.*)|*.*"
            .FilterIndex = 1
            .Title = "Open Access Database"
        End With

        'Show the dialog
        If objOpenFileDialog.ShowDialog = DialogResult.OK Then
            'If the Open button was clicked, then load the file name selected
            txtDatabase.Text = objOpenFileDialog.FileName
            'Change the status
            lblStatus.Text = "Database is set"
        End If

        'Cleanup
        objOpenFileDialog.Dispose()
        objOpenFileDialog = Nothing
    End Sub

    Private Sub btnOpenConnection_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnOpenConnection.Click

        'Initialize a new instance of the OleDbConnection class
        objConnection = New OleDbConnection( _
                      "Provider=SQLOLEDB;Server=" & txtServer.Text & ";Database=tw_Cent_Time;UID=" & txtUserName.Text & ";PWD=" & txtPassword.Text & ";")

        Try
            'Open the connection
            objConnection.Open()
        Catch OleDbExceptionErr As OleDbException
            'Display the error
            MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
        Catch InvalidOperationExceptionErr As InvalidOperationException
            'Display the error
            MessageBox.Show(InvalidOperationExceptionErr.Message, "Access SQL")
        End Try

        'Check the state of the connection and report appropriately
        If objConnection.State = ConnectionState.Open Then
            lblStatus.Text = "Database connection is open"
        Else
            lblStatus.Text = "Database connection failed"
        End If

    End Sub

    Private Sub btnCloseConnection_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnCloseConnection.Click

        'Close the connection
        objConnection.Close()
        'Display the status of the connection
        lblStatus.Text = "Database connection is closed"

        'Cleanup
        objConnection.Dispose()
        objConnection = Nothing
    End Sub

    Private Function IsConnectionOpen() As Boolean
        'Is the connection object set to a valid instance
        'of the OleDbConnection class
        If IsNothing(objConnection) Then
            Return False
        End If

        'Is the connection open
        If objConnection.State <> ConnectionState.Open Then
            Return False
        End If

        'If we made it this far return True
        Return True
    End Function

    Private Sub btnDataReader_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnDataReader.Click

        'Validate connection state
        If Not IsConnectionOpen() Then
            Exit Sub
        End If

        'Declare and initialize a new instance of the OleDbCommand class
        Dim objCommand As New OleDbCommand(txtSQL.Text, objConnection)

        'Set the CommandType property
        If optText.Checked Then
            objCommand.CommandType = CommandType.Text
        Else
            objCommand.CommandType = CommandType.TableDirect
        End If

        'Declare an OleDbDataReader object
        Dim objDataReader As OleDbDataReader

        'Declare a String variable
        Dim strData As String

        Try
            'Execute the SQL text
            objDataReader = objCommand.ExecuteReader()

            'Check to see if we have data
            If objDataReader.HasRows Then

                'Process all rows
                While objDataReader.Read()

                    'Clear the variable
                    strData = String.Empty

                    'Get the data in each column
                    For intIndex As Integer = 0 To objDataReader.FieldCount - 1
                        strData &= objDataReader.Item(intIndex).ToString & ", "
                    Next

                    'Remove the last comma from the string
                    strData = strData.Remove(strData.Length - 2, 2)

                    'Write the data to the TextBox
                    txtSQL.Text &= ControlChars.CrLf & strData

                End While

            End If

            'Close the reader
            objDataReader.Close()

        Catch OleDbExceptionErr As OleDbException
            MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
        End Try

        'Cleanup
        objCommand.Dispose()
        objCommand = Nothing
        objDataReader = Nothing
    End Sub

    Private Sub btnDataAdapter_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnDataAdapter.Click

        'Validate connection state
        If Not IsConnectionOpen() Then
            Exit Sub
        End If

        'Declare and initialize a new instance of the OleDbCommand class
        Dim objCommand As New OleDbCommand(txtSQL.Text, objConnection)

        'Set the CommandType property
        If optText.Checked Then
            objCommand.CommandType = CommandType.Text
        Else
            objCommand.CommandType = CommandType.TableDirect
        End If

        'Declare a OleDbDataAdapter object
        Dim objDataAdapter As New OleDbDataAdapter

        'Declare a DataTable object
        Dim objDataTable As New DataTable

        'Set the SelectCommand for the OleDbDataAdapter
        objDataAdapter.SelectCommand = objCommand

        Try
            'Populate the DataTable
            objDataAdapter.Fill(objDataTable)

            'Bind the DataTable to the DataGridView
            grdResults.DataSource = objDataTable

            'Set the AlternatingRowsDefaultCellStyle.BackColor property
            grdResults.AlternatingRowsDefaultCellStyle.BackColor = Color.WhiteSmoke

            'Set the CellBorderStyle property
            grdResults.CellBorderStyle = DataGridViewCellBorderStyle.None

            'Set the SelectionMode property
            grdResults.SelectionMode = DataGridViewSelectionMode.FullRowSelect

        Catch OleDbExceptionErr As OleDbException
            MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
        End Try

        'Cleanup
        objCommand.Dispose()
        objCommand = Nothing
        objDataAdapter.Dispose()
        objDataAdapter = Nothing
        objDataTable.Dispose()
        objDataTable = Nothing
    End Sub


    Overloads Function GetDataTable(ByVal sql As String, ByVal Source As String) As DataTable 'this will get you your datatable object
        Dim conConnect As OleDbConnection = New OleDbConnection(Source)
        Dim dapAdapter As OleDbDataAdapter = New OleDbDataAdapter(sql, conConnect)
        Dim dstDataSet As New DataSet
        Dim tblDataTable As DataTable
        Try
            conConnect.Open()
            dapAdapter.Fill(dstDataSet)
            tblDataTable = dstDataSet.Tables(0)
            dstDataSet.Tables.Clear()
        Catch ex As OleDbException
            MsgBox(ex.Message)
        Finally
            conConnect.Close()
        End Try
        Return tblDataTable
    End Function

    Public Sub LoadEmployees()
        Dim sql As String = "Select EmployeeId, Shortname From EmployeeAnalysis"
        Dim ds As String = "Provider=SQLOLEDB;Server=" & txtServer.Text & ";Database=tw_Cent_Time;UID=" & txtUserName.Text & ";PWD=" & txtPassword.Text & ";"
        Dim tbl As DataTable = GetDataTable(Sql, ds) 'the employee table
        cboEmployees.DataSource = tbl
        cboEmployees.DisplayMember = "Shortname"
        cboEmployees.ValueMember = "EmployeeId"
    End Sub




    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


    End Sub
End Class
0
 
wtconwayCommented:
It looks as though VS.NET is just warning you about tblDataTable. And it has every right to. I noticed that my code merely declares tblDataTable and then ATTEMPTS to assign a value to it based on the SQL string and datasource connection string you send to the function. Should that process fail, you'll get a null ref exception as a result if you reference the returned value of the function. To remedy that, simply do this:

Change

Dim tblDataTable As DataTable

to:

Dim tblDataTable as New DataTable

and voila. Tell me what happens. I also noticed that you aren't USING my code. Unless of course you've left code out.
0
 
padgettbrianAuthor Commented:
It was left out on accident. Im tryin to clean it up now.

That did fix my tblDataTable know i just need to stitch this thing together i think. Ill post the full code in just a couple min. :)

Thanks
0
 
padgettbrianAuthor Commented:
Its not working :( what am i missing???????

COMPLETE CODE
----------------------------------------------------------------------
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class Form1
    'Form level variables
    Private objConnection As OleDbConnection

    Private Sub btnDatabase_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnDatabase.Click

        'Declare OpenFileDialog object
        Dim objOpenFileDialog As New OpenFileDialog

        'Set the OpenFileDialog properties
        With objOpenFileDialog
            .Filter = "Access Database (*.mdb)|*.mdb|All files (*.*)|*.*"
            .FilterIndex = 1
            .Title = "Open Access Database"
        End With

        'Show the dialog
        If objOpenFileDialog.ShowDialog = DialogResult.OK Then
            'If the Open button was clicked, then load the file name selected
            txtDatabase.Text = objOpenFileDialog.FileName
            'Change the status
            lblStatus.Text = "Database is set"
        End If

        'Cleanup
        objOpenFileDialog.Dispose()
        objOpenFileDialog = Nothing
    End Sub

    Private Sub btnOpenConnection_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnOpenConnection.Click

        'Initialize a new instance of the OleDbConnection class
        objConnection = New OleDbConnection( _
                      "Provider=SQLOLEDB;Server=" & txtServer.Text & ";Database=tw_Cent_Time;UID=" & txtUserName.Text & ";PWD=" & txtPassword.Text & ";")

        Try
            'Open the connection
            objConnection.Open()
        Catch OleDbExceptionErr As OleDbException
            'Display the error
            MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
        Catch InvalidOperationExceptionErr As InvalidOperationException
            'Display the error
            MessageBox.Show(InvalidOperationExceptionErr.Message, "Access SQL")
        End Try

        'Check the state of the connection and report appropriately
        If objConnection.State = ConnectionState.Open Then
            lblStatus.Text = "Database connection is open"
        Else
            lblStatus.Text = "Database connection failed"
        End If

    End Sub

    Private Sub btnCloseConnection_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnCloseConnection.Click

        'Close the connection
        objConnection.Close()
        'Display the status of the connection
        lblStatus.Text = "Database connection is closed"

        'Cleanup
        objConnection.Dispose()
        objConnection = Nothing
    End Sub

    Private Function IsConnectionOpen() As Boolean
        'Is the connection object set to a valid instance
        'of the OleDbConnection class
        If IsNothing(objConnection) Then
            Return False
        End If

        'Is the connection open
        If objConnection.State <> ConnectionState.Open Then
            Return False
        End If

        'If we made it this far return True
        Return True
    End Function

    Private Sub btnDataAdapter_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnDataAdapter.Click

        'Validate connection state
        If Not IsConnectionOpen() Then
            Exit Sub
        End If

        'Declare and initialize a new instance of the OleDbCommand class
        Dim objCommand As New OleDbCommand(txtSQL.Text, objConnection)

        'Set the CommandType property
        If optText.Checked Then
            objCommand.CommandType = CommandType.Text
        Else
            objCommand.CommandType = CommandType.TableDirect
        End If

        'Declare a OleDbDataAdapter object
        Dim objDataAdapter As New OleDbDataAdapter

        'Declare a DataTable object
        Dim objDataTable As New DataTable

        'Set the SelectCommand for the OleDbDataAdapter
        objDataAdapter.SelectCommand = objCommand

        Try
            'Populate the DataTable
            objDataAdapter.Fill(objDataTable)

            'Bind the DataTable to the DataGridView
            grdResults.DataSource = objDataTable

            'Set the AlternatingRowsDefaultCellStyle.BackColor property
            grdResults.AlternatingRowsDefaultCellStyle.BackColor = Color.WhiteSmoke

            'Set the CellBorderStyle property
            grdResults.CellBorderStyle = DataGridViewCellBorderStyle.None

            'Set the SelectionMode property
            grdResults.SelectionMode = DataGridViewSelectionMode.FullRowSelect

        Catch OleDbExceptionErr As OleDbException
            MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
        End Try

        'Cleanup
        objCommand.Dispose()
        objCommand = Nothing
        objDataAdapter.Dispose()
        objDataAdapter = Nothing
        objDataTable.Dispose()
        objDataTable = Nothing
    End Sub

    Overloads Function GetDataTable(ByVal sql As String, ByVal Source As String) As DataTable 'this will get you your datatable object
        Dim conConnect As OleDbConnection = New OleDbConnection(Source)
        Dim dapAdapter As OleDbDataAdapter = New OleDbDataAdapter(sql, conConnect)
        Dim dstDataSet As New DataSet
        Dim tblDataTable As New DataTable

        Try
            conConnect.Open()
            dapAdapter.Fill(dstDataSet)
            tblDataTable = dstDataSet.Tables(0)
            dstDataSet.Tables.Clear()
        Catch ex As OleDbException
            MsgBox(ex.Message)
        Finally
            conConnect.Close()
        End Try
        Return tblDataTable
    End Function

    Public Sub LoadEmployees()
        Dim sql As String = "Select EmployeeId, Shortname From EmployeeAnalysis"
        Dim ds As String = "Provider=SQLOLEDB;Server=" & txtServer.Text & ";Database=tw_Cent_Time;UID=" & txtUserName.Text & ";PWD=" & txtPassword.Text & ";"
        Dim tbl As DataTable = GetDataTable(Sql, ds) 'the employee table
        cboEmployees.DataSource = tbl
        cboEmployees.DisplayMember = "Shortname"
        cboEmployees.ValueMember = "EmployeeId"
    End Sub


End Class
0
 
wtconwayCommented:
Well first off you aren't using my code. You've simply placed it in the class. Inside the Try/Catch block in btnDataAdapter_Click try this:

Dim ds as String = "Provider=SQLOLEDB;Server=" & txtServer.Text & ";Database=tw_Cent_Time;UID=" & txtUserName.Text & ";PWD=" & txtPassword.Text & ";"
grdResults.DataSource = GetDataTable(txtSQL.Text,ds)

that should get your table back. That is, if Access likes your connection string.
0
 
padgettbrianAuthor Commented:
Im not sure what u mean when yo usay im not using your code cause i have it in there...

and were connecting to SQL Server
0
 
padgettbrianAuthor Commented:
if i do Dim ds as String it kills all the objConnection code. does yours work?
0
 
wtconwayCommented:
Just having the code in the class doesn't mean you USE it. You are simply DECLARING it right now. It's kinda like having food on your plate, but it doesn't mean you're eating it. If you look at the method I gave you, GetDataTable, it receives two parameters. A sql statement and a datasource connection string. It handles creating all the connection objects,dataadapters, datatables, in order to retrieve a datatable. It seems as though you are writing a simple Query Analyzer program to query a database with a sql statement you type in a textbox. Let's completely strip out the btnDataAdapter handler and re-write it to look like this:

Private Sub btnDataAdapter_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnDataAdapter.Click
        Dim ds as String =  "Provider=SQLOLEDB;Server=" & txtServer.Text & ";Database=tw_Cent_Time;UID=" & txtUserName.Text & ";PWD=" & txtPassword.Text & ";" 'this is a datasource connection string, just like in your old code it controls how you connect to the datasource (your database)
        Dim sql as String = txtSql.Text
        Dim tbl as DataTable = GetDataTable(sql,ds) 'this will return a datatable, that's all you need

            'Bind the DataTable to the DataGridView
            grdResults.DataSource = tbl

            'Set the AlternatingRowsDefaultCellStyle.BackColor property
            grdResults.AlternatingRowsDefaultCellStyle.BackColor = Color.WhiteSmoke

            'Set the CellBorderStyle property
            grdResults.CellBorderStyle = DataGridViewCellBorderStyle.None

            'Set the SelectionMode property
            grdResults.SelectionMode = DataGridViewSelectionMode.FullRowSelect


    End Sub

All your other code should be unneccessary at this point.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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