Results 1 to 3 of 3

Thread: [RESOLVED] Combobox - Showing Multiple Columns in DataTable in each ComboBox Item (multi column)

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Apr 2005
    Location
    Debug.Print
    Posts
    3,885

    Resolved [RESOLVED] Combobox - Showing Multiple Columns in DataTable in each ComboBox Item (multi column)

    I am using SQL Server 2005 and VB.NET 2005.
    I am using a Stored Procedure to obtain information from two tables in SQL Server 2005 as follows:
    Code:
    ALTER PROCEDURE GetCOA
    (
    	@COATypeID AS INTEGER
    )
    AS
    BEGIN
    SET NOCOUNT ON
    	SELECT COAT.GLAccountTypeID AS COATID, 
    		   COAT.AccountType AS AcctType, 
    		   COA.GLAccountTypeID AS COAID, 
    		   COA.AccountNumber AS AcctNum, 
    		   COA.AccountName AS AcctName
    	FROM GL_ChartOfAccounts AS COA
    	INNER JOIN GL_ChartOfAccountTypes AS COAT
    	ON COATID = COAID
    	WHERE COATID = @COATypeID
    	ORDER BY AcctNum
    END
    I want to show the Account Number on the left and then the Account Name on the right for each item.
    I am not sure what would be the best way of doing it.

    Function to get the data from SQL Server into a DataTable
    Code:
        Public Function GetCOA(ByVal AccountType As Integer) As DataTable
            '1 = Asset.
            '2 - Liability.
            '3 - Equity.
            '4 - Income/Revenue.
            '5 - Expenses.
    
            Dim dt As DataTable = New DataTable
            Dim dr As SqlDataReader
    
            Using SetDatabaseConnection As SqlConnection = New SqlConnection(ConnectToDatabase)
                Using cmd As SqlCommand = New SqlCommand
                    Try
                        With cmd
                            .CommandText = "GetCOA"
                            .CommandType = CommandType.StoredProcedure
                            .Connection = SetDatabaseConnection
                            .Parameters.Add("@COATypeID", SqlDbType.Int).Value = AccountType
                        End With
    
                        With SetDatabaseConnection
                            If .State = ConnectionState.Broken _
                            OrElse .State = ConnectionState.Closed Then
                                .Open()
                            End If
                        End With
    
                        dr = cmd.ExecuteReader
    
                        With dr
                            If .HasRows Then
                                dt.Load(dr)
                            End If
                        End With
                    Catch RangeEx As IndexOutOfRangeException
                        MessageBox.Show(RangeEx.ToString(), _
                                        "Index Out Of Range Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Catch CastEx As InvalidCastException
                        MessageBox.Show(CastEx.ToString(), _
                                        "Invalid Cast Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Catch ArgNullEx As ArgumentNullException
                        MessageBox.Show(ArgNullEx.ToString(), _
                                        "Argument Null Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Catch ArgEx As ArgumentException
                        MessageBox.Show(ArgEx.ToString(), _
                                        "Argument Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Catch SQLEx As SqlException
                        MessageBox.Show(SQLEx.ToString(), _
                                        "SQL Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Catch InvalidOpEx As InvalidOperationException
                        MessageBox.Show(InvalidOpEx.ToString(), _
                                        "Invalid Operation Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Catch NotSuppEx As NotSupportedException
                        MessageBox.Show(NotSuppEx.ToString(), _
                                        "Not Supported Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Catch NullRefEx As NullReferenceException
                        MessageBox.Show(NullRefEx.ToString(), _
                                        "Null Reference Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Finally
                        With SetDatabaseConnection
                            If .State = ConnectionState.Open Then
                                .Close()
                            End If
                        End With
                    End Try
                End Using
            End Using
    
            Return dt
        End Function
    Form Load:
    Code:
            Try
                With SubAccountOfAssets
                    .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                    .AutoCompleteSource = AutoCompleteSource.ListItems
                    .DataSource = GetCOA(1)
                    .DisplayMember = "AcctName"
                    .ValueMember = "AcctNum"
                End With
            Catch CastEx As InvalidCastException
                MessageBox.Show(CastEx.ToString(), _
                                "Invalid Cast Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Catch OutOfMemEx As OutOfMemoryException
                MessageBox.Show(OutOfMemEx.ToString(), _
                                "Out Of Memory Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Catch ArgEx As ArgumentException
                MessageBox.Show(ArgEx.ToString(), _
                                "Argument Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            End Try
    Errors are in the screen shots.
    Line 24: .ValueMember = "AcctNum"
    Line 253: dr = cmd.ExecuteReader
    Attached Images Attached Images   

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Combobox - Showing Multiple Columns in DataTable in each ComboBox Item (multi col

    you can add a field in your SELECT statement:

    select something = field1 & vbtab & field2

    which will then contain what you want your combobox to display

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Apr 2005
    Location
    Debug.Print
    Posts
    3,885

    Re: Combobox - Showing Multiple Columns in DataTable in each ComboBox Item (multi col

    Quote Originally Posted by .paul. View Post
    you can add a field in your SELECT statement:

    select something = field1 & vbtab & field2

    which will then contain what you want your combobox to display
    Thanks .paul.

    I have been playing around with this idea for a while and finally got it to work.

    working code below for others that might want to implement something like this in their apps. Of course, there is much more I need to code, but least this part works like a charm.

    I have this in my form load as I want to populate the combobox on form load.
    Code:
    LoadGLAccountsDropDown(SubAccountOfAssets, 1)
    My sub is as follows (the 1 above indicates the type of account I want to show in the combobox. Of course, there are many types I want to show, but they live in other comboboxes on other forms/pages)
    Code:
       Public Sub LoadGLAccountsDropDown(ByVal CBName As ComboBox, _
                                          ByVal GLAccountType As Integer)
    
            Using SetDatabaseConnection As New SqlConnection(ConnectToDatabase)
                Using GetGLAccounts As SqlCommand = New SqlCommand
                    Try
                        SetDatabaseConnection.Open()
    
                        With GetGLAccounts
                            .Connection = SetDatabaseConnection
                            .CommandType = CommandType.StoredProcedure
                            .CommandText = "GetGLAccounts"
                            .Parameters.Add("@AccountType", SqlDbType.Int).Value = GLAccountType
                            .ExecuteNonQuery()
                        End With
    
                        Dim GLAcctDr As SqlDataReader
                        GLAcctDr = GetGLAccounts.ExecuteReader
    
                        With GLAcctDr
                            While .Read
                                If .HasRows Then
                                    CBName.Items.Add(GLAcctDr("AccountNumber") & vbTab & GLAcctDr("AccountName"))
                                End If
                            End While
                        End With
                    Catch CastEx As InvalidCastException
                        MessageBox.Show(CastEx.ToString, _
                                        "Invalid Cast Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Catch ArgEx As ArgumentException
                        MessageBox.Show(ArgEx.ToString, _
                                        "Argument Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Catch NullRefEx As NullReferenceException
                        MessageBox.Show(NullRefEx.ToString, _
                                        "Null Reference Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Catch SQLEx As SqlException
                        MessageBox.Show(SQLEx.ToString, _
                                        "SQL Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Catch FormatEx As FormatException
                        MessageBox.Show(FormatEx.ToString, _
                                        "Format Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Catch OverflowEx As OverflowException
                        MessageBox.Show(OverflowEx.ToString, _
                                        "Overflow Exception", _
                                        MessageBoxButtons.OK, _
                                        MessageBoxIcon.Error, _
                                        MessageBoxDefaultButton.Button1)
                    Finally
                        SetDatabaseConnection.Close()
                    End Try
                End Using
            End Using
    I love using SPROCS as it makes it easier in the long run. I dont have to rebuild the app if I need to make a change, just update the SPROC on the server and bingo.
    Code:
    CREATE PROCEDURE GetGLAccounts
    (
    	@AccountType AS INT
    )
    AS
    SET NOCOUNT ON
    BEGIN
    	SELECT COAT.GLAccountTypeID, 
    		   COAT.AccountType, 
    		   COA.GLAccountTypeID, 
    		   COA.AccountNumber, 
    		   COA.AccountName
    	FROM GL_ChartOfAccountsTypes AS COAT
    	INNER JOIN GL_ChartOfAccounts AS COA
    	ON 	COAT.GLAccountTypeID = COA.GLAccountTypeID
    	WHERE COAT.GLAccountTypeID = @AccountType
    	ORDER BY COA.AccountNumber ASC	
    END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width