Displaying values from a single combobox

[jmensah]

the code display 2 colunms in one combo box from a table. i have table with 2 columns account_type and account_name. the account_type is an abbreviation(codes) which would be stored in a new table when selected. I want the code when the combo is click, would display the code-account_type and account_name in the same combo box.

Private Sub cboAccount_type_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboAccount_type.SelectedIndexChanged
        Dim cn As New SqlConnection(ConnectionString)
        Dim query As String = "Select Concat(Account_type, ' - ', Account_Name) As AccountDesc, Account_type from Accounts"
        Try
            cn.Open()
            Dim Account_type, Account_Name As String
            Dim Table As New DataTable()
            put = "Select * from Accounts"
            cboAccount_type.DisplayMember = "AccountDesc"
            cboAccount_type.ValueMember = "Account_type"
            'String[]{"Account_Type","AccountDesc"}
            cboAccount_type.DataSource = theDataTable
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

[jmensah]

am geting the error cboAccount_type.datasource=thedatatable
thedatatableis not decleared

[prvnkmr194]

Please first clear your question as mention your problem
You display the two column of table in one combobox, i understand your up to that but what you try to do when user click on combobox.

[jmensah]

yes when the user click on the combobox, then the two values display

[jmensah]

yes, You display the two column of table in one combobox when user click on the combo box

[prvnkmr194]

cmd.CommandText = "select account_type1,account_name1 +'-'+ account_type1 As AccountDesc from accounts1"
        cmd.Connection = conn
        da.SelectCommand = cmd
        da.Fill(ds, "accounts1")
        ComboBox1.DataSource = ds.Tables(0)
        ComboBox1.DisplayMember = "accounts1"
        ComboBox1.ValueMember = "AccountDesc"

Replace your code by this code, it will display two column of your table in one combobox

[jmensah]

thanks for your help the code. There is no error but it doesnot display the values from the combo even though there are records in the table

[prvnkmr194]

please post your updated code because in my case it display the both column(account_name1 , account_type1) in single combobox , so that i can help you

[jmensah]

Private Sub cboAccount_type_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboAccount_type.SelectedIndexChanged
        Dim cmd As New SqlCommand
        put = "select account_type,account_name +'-' +account_type as AccountDesc from Accounts"
        cmd.Connection = con
        da.SelectCommand = cmd
        da.Fill(ds, "Accounts")
        cboAccount_type.DataSource = ds.Tables(0)
        cboAccount_type.DisplayMember = "Account_Type"
        cboAccount_type.DisplayMember = "AccountDesc"

    End Sub

[prvnkmr194]

you have to put this code in form_load event or some where else not in selectedIndexChange event and line number 3 is

cmd.commandtext = "select account_type,account_name +'-' +account_type as AccountDesc from Accounts"

this line add two column and display in combobox

[jmensah]

i had sent whole code for your attention to see the problem. it is still not displaying.
thanks for your help
Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.DataSet
Imports System.Data.Sql
Imports System.Data.SqlTypes
Public Class NewApplicant
Dim ConnectionString As String
Dim Sql1, sql2 As String
Dim da As SqlDataAdapter
Dim ds As New DataSet
Private Bindingsource As New BindingSource
Private DataAdaptor As New SqlDataAdapter
Dim connection As String
Dim con As New SqlConnection
Dim put, fresh As String
Dim cmd As New SqlCommand
Private Sub NewApplicant_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'SusuprojectDataSet1.Branch' table. You can move, or remove it, as needed.
Me.BranchTableAdapter.Fill(Me.SusuprojectDataSet1.Branch)
'TODO: This line of code loads data into the 'SusuprojectDataSet.Accounts' table. You can move, or remove it, as needed.
Me.AccountsTableAdapter.Fill(Me.SusuprojectDataSet.Accounts)
ConnectionString = "Data Source=GYANFI-PC\SA;Initial Catalog=Susuproject;User ID=sa;Password=303444"
'cmd.CommandText = "select account_type,account_name +'-' +account_type as AccountDesc from Account"
Try
Dim cn As New SqlConnection(ConnectionString)
cn.Open()
Dim cmd As New SqlCommand("Select * from NewAccount", cn)
Me.DataAdaptor = New SqlDataAdapter(cmd)
Dim commandBuilder As New SqlCommandBuilder(Me.DataAdaptor)
Dim Table As New DataTable()
Table.Locale = System.Globalization.CultureInfo.InvariantCulture
Me.DataAdaptor.Fill(Table)
Me.Bindingsource.DataSource = Table
cmd.CommandText = "select account_type,account_name +'-' +account_type as AccountDesc from Account"
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub
Private Sub txtAccount_No_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtAccount_No.LostFocus
Try
put = "SELECT First_Name,Surname FROM NewAccount WHERE (Account_no = @Account_no)"
Dim mySqlDBConnection As New SqlConnection(connectionstring)
Dim mySqlDBCommand As New SqlCommand(put, mySqlDBConnection)
mySqlDBCommand.Parameters.AddWithValue("@Account_no", txtAccount_No.Text)
mySqlDBConnection.Open()
Using myDataReader As SqlDataReader = mySqlDBCommand.ExecuteReader
If myDataReader.Read() Then
MsgBox("Account number already exists")
Me.txtAccount_No.Clear()
txtAccount_No.Focus()
Else
MessageBox.Show("Record Doesn't exit, add as new record")
End If
End Using
mySqlDBConnection.Close()
Catch ex As SqlException
MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch err As System.Exception
MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub

Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
Try
Dim cn As New SqlConnection(ConnectionString)
Dim sql As String = "INSERT INTO NewAccount(Account_no, Surname, Branch_id,Date_Applied,Account_type,First_Name, sex, Date_Birth,Address,Contact_Address, Contact_no) " & _
"VALUES (@Account_no, @Surname, @Branch_id,@Date_Applied,@Account_type,@First_Name, @sex, @Date_Birth,@Address,@Contact_Address, @Contact_no)"
Dim myCommand As New SqlCommand(sql, cn) 'Don't forget to include the connection....

With myCommand.Parameters
.AddWithValue("@Account_no", Me.txtAccount_No.Text)
.AddWithValue("@Surname", Me.txtSurname.Text)
.AddWithValue("@Branch_id", Me.cboBranch_id.Text)
.AddWithValue("@Date_Applied", Me.DtDate_Applied.Value.Date)
.AddWithValue("@Account_type", Me.cboAccount_type.Text)
.AddWithValue("@First_Name", Me.txtFirst_Name.Text)
.AddWithValue("@sex", Me.cbosex.Text)
.AddWithValue("@Date_Birth", Me.DtDate_Applied.Value.Date)
.AddWithValue("@Address", Me.TxtAddress.Text)
.AddWithValue("@Contact_Address", Me.txtContact_Address.Text)
.AddWithValue("@Contact_no", Me.MskContact_no.TextMaskFormat)
End With
'Took out the adaptor, as it's not needed.
cn.Open() 'Personally I don't like to open teh connection until the last second when I need it.
myCommand.ExecuteNonQuery() 'This will execute the statement
cn.Close() 'If you open it, close it
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
'Even better would be to put the cn.close() here, after checking the state... that way you can be sure the connection closes regardless if the call to the database succeeds or not.
End Try
End Sub

Private Sub BtnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClose.Click
Me.Close()
End Sub

Private Sub cboAccount_type_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboAccount_type.SelectedIndexChanged
Dim cmd As New SqlCommand
da.SelectCommand = cmd
da.Fill(ds, "Accounts")
cboAccount_type.DataSource = ds.Tables(0)
cboAccount_type.DisplayMember = "Account_Type"
cboAccount_type.DisplayMember = "AccountDesc"

End Sub
Private Sub cboBranch_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboBranch_id.SelectedIndexChanged
Try
Dim cn As New SqlConnection(ConnectionString)
cn.Open()
put = "select Branch_id,Branch_Name from Branch where Branch_id in(Select Branch_id from Branch where Branch_id='" & cboBranch_id.SelectedValue.ToString & ")"
' Dim dr As SqlDataReader = cmd.ExecuteReader()
' While dr.Read()
' cboBranch_id.Items.Add(dr.GetValue(0))
' End While
' dr.Close()
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class

[jmensah]

Imports System.Data.SqlClient
Imports System.Data
i am seend back the whole code to check the real problem why it's still not displaying the values.
thank you very much for your help
Imports System.Data.DataSet
Imports System.Data.Sql
Imports System.Data.SqlTypes
Public Class NewApplicant
Dim ConnectionString As String
Dim Sql1, sql2 As String
Dim da As SqlDataAdapter
Dim ds As New DataSet
Private Bindingsource As New BindingSource
Private DataAdaptor As New SqlDataAdapter
Dim connection As String
Dim con As New SqlConnection
Dim put, fresh As String
Dim cmd As New SqlCommand
Private Sub NewApplicant_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'SusuprojectDataSet1.Branch' table. You can move, or remove it, as needed.
Me.BranchTableAdapter.Fill(Me.SusuprojectDataSet1.Branch)
'TODO: This line of code loads data into the 'SusuprojectDataSet.Accounts' table. You can move, or remove it, as needed.
Me.AccountsTableAdapter.Fill(Me.SusuprojectDataSet.Accounts)
ConnectionString = "Data Source=GYANFI-PC\SA;Initial Catalog=Susuproject;User ID=sa;Password=303444"
'cmd.CommandText = "select account_type,account_name +'-' +account_type as AccountDesc from Account"
Try
Dim cn As New SqlConnection(ConnectionString)
cn.Open()
Dim cmd As New SqlCommand("Select * from NewAccount", cn)
Me.DataAdaptor = New SqlDataAdapter(cmd)
Dim commandBuilder As New SqlCommandBuilder(Me.DataAdaptor)
Dim Table As New DataTable()
Table.Locale = System.Globalization.CultureInfo.InvariantCulture
Me.DataAdaptor.Fill(Table)
Me.Bindingsource.DataSource = Table
cmd.CommandText = "select account_type,account_name +'-' +account_type as AccountDesc from Account"
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub
Private Sub txtAccount_No_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtAccount_No.LostFocus
Try
put = "SELECT First_Name,Surname FROM NewAccount WHERE (Account_no = @Account_no)"
Dim mySqlDBConnection As New SqlConnection(connectionstring)
Dim mySqlDBCommand As New SqlCommand(put, mySqlDBConnection)
mySqlDBCommand.Parameters.AddWithValue("@Account_no", txtAccount_No.Text)
mySqlDBConnection.Open()
Using myDataReader As SqlDataReader = mySqlDBCommand.ExecuteReader
If myDataReader.Read() Then
MsgBox("Account number already exists")
Me.txtAccount_No.Clear()
txtAccount_No.Focus()
Else
MessageBox.Show("Record Doesn't exit, add as new record")
End If
End Using
mySqlDBConnection.Close()
Catch ex As SqlException
MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch err As System.Exception
MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub

Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
Try
Dim cn As New SqlConnection(ConnectionString)
Dim sql As String = "INSERT INTO NewAccount(Account_no, Surname, Branch_id,Date_Applied,Account_type,First_Name, sex, Date_Birth,Address,Contact_Address, Contact_no) " & _
"VALUES (@Account_no, @Surname, @Branch_id,@Date_Applied,@Account_type,@First_Name, @sex, @Date_Birth,@Address,@Contact_Address, @Contact_no)"
Dim myCommand As New SqlCommand(sql, cn) 'Don't forget to include the connection....

With myCommand.Parameters
.AddWithValue("@Account_no", Me.txtAccount_No.Text)
.AddWithValue("@Surname", Me.txtSurname.Text)
.AddWithValue("@Branch_id", Me.cboBranch_id.Text)
.AddWithValue("@Date_Applied", Me.DtDate_Applied.Value.Date)
.AddWithValue("@Account_type", Me.cboAccount_type.Text)
.AddWithValue("@First_Name", Me.txtFirst_Name.Text)
.AddWithValue("@sex", Me.cbosex.Text)
.AddWithValue("@Date_Birth", Me.DtDate_Applied.Value.Date)
.AddWithValue("@Address", Me.TxtAddress.Text)
.AddWithValue("@Contact_Address", Me.txtContact_Address.Text)
.AddWithValue("@Contact_no", Me.MskContact_no.TextMaskFormat)
End With
'Took out the adaptor, as it's not needed.
cn.Open() 'Personally I don't like to open teh connection until the last second when I need it.
myCommand.ExecuteNonQuery() 'This will execute the statement
cn.Close() 'If you open it, close it
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
'Even better would be to put the cn.close() here, after checking the state... that way you can be sure the connection closes regardless if the call to the database succeeds or not.
End Try
End Sub

Private Sub BtnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClose.Click
Me.Close()
End Sub

Private Sub cboAccount_type_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboAccount_type.SelectedIndexChanged
Dim cmd As New SqlCommand
da.SelectCommand = cmd
da.Fill(ds, "Accounts")
cboAccount_type.DataSource = ds.Tables(0)
cboAccount_type.DisplayMember = "Account_Type"
cboAccount_type.DisplayMember = "AccountDesc"

End Sub
Private Sub cboBranch_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboBranch_id.SelectedIndexChanged
Try
Dim cn As New SqlConnection(ConnectionString)
cn.Open()
put = "select Branch_id,Branch_Name from Branch where Branch_id in(Select Branch_id from Branch where Branch_id='" & cboBranch_id.SelectedValue.ToString & ")"
' Dim dr As SqlDataReader = cmd.ExecuteReader()
' While dr.Read()
' cboBranch_id.Items.Add(dr.GetValue(0))
' End While
' dr.Close()
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class

[prvnkmr194]

Check the example..
I dont know why you make your code in such a complex manner and always use code tag for posting your code.
Before executing this example paste the database into d drive , actually i don;t have sql server install in pc so is it in ms-access

[prvnkmr194]

You try to execute my code is it working?