-
Dec 25th, 2014, 10:09 PM
#1
Thread Starter
PowerPoster
[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
Last edited by BrailleSchool; Dec 25th, 2014 at 10:16 PM.
Reason: Added Line Numbers.
-
Dec 26th, 2014, 06:32 PM
#2
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Dec 28th, 2014, 01:15 AM
#3
Thread Starter
PowerPoster
Re: Combobox - Showing Multiple Columns in DataTable in each ComboBox Item (multi col
Originally Posted by .paul.
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
Last edited by BrailleSchool; Dec 28th, 2014 at 01:22 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|