none
fill comobox items from datatable RRS feed

  • Question

  • hi everybody, im trying to additems into the combobox but i failed. im using the following code:

        Dim ds As DataSet

        Dim dt As New DataTable

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

            'TODO: This line of code loads data into the '

            Me.TblMarketTableAdapter.Fill(Me.DatabaseDataSet.tblFruits)

            dt = DatabaseDataSet.tblFruits

            For Each s As String In dt.Rows.Item(0).Item("fruits")

                ComboBox1.Items.Add(s)

            Next

     

     

        End Sub

    when i tried to run the application combo box is filled with the first row in the table like this:

    a

    p

    p

    l

    e

     

    where the first row fruit column value is "apple". please tell me how i can add column values into the combobox items list. 

    thanx

    Sunday, May 9, 2010 2:24 PM

Answers

  • Your code still doesn't look quite right. Instead of filling the ComboBox by adding Items, why don't you simply use the DataSource:

    Private Sub admForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
     Me.TblMarketTableAdapter.Fill(Me.DatabaseDataSet.tblFruits)
     
     With ComboBox1
      ' I added the DataSource and changed the setting of Display/ValueMember properties
      .DataSource = Me.DatabaseDataSet.tblFruits
      .DisplayMember = Me.DatabaseDataSet.tblFruits.Columns(1).ColumnName
      .ValueMember = Me.DatabaseDataSet.tblFruits.Columns(0).ColumnName
     
      .SelectedIndex = 0
      .AutoCompleteSource = AutoCompleteSource.ListItems
      .AutoCompleteMode = AutoCompleteMode.SuggestAppend
     End With
    End Sub
     

     

     

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, May 11, 2010 5:10 AM
  • I am adding items into the ComboBox because I want user to select the Items from the ComboBox. If user entered something which is not in the list of ComboBox Items, then a message box will be appeared asking user to add it into the database. Here is the code:

    Private Sub ComboBox1_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.LostFocus

            If Not ComboBox1.Items.Contains(ComboBox1.Text) AndAlso MsgBox("Do You Want to Add This Rank?", 1, "Adding into Database") = 1 Then

                'add inoto the database 

            End If

    End Sub

    Hi faisalVB,

    I think you can also achieve your requirement mentioned above with Bonnie's suggestion which is much clear and easy.  By the way, please note that:

    "The GotFocus and LostFocus events are low-level focus events that are tied to the WM_KILLFOCUS and WM_SETFOCUS Windows messages. Typically, the GotFocus and LostFocus events are only used when updating UICues or when writing custom controls. Instead the Enter and Leave events should be used for all controls except the Form class, which uses the Activated and Deactivate events. For more information about the GotFocus and LostFocus events, see the WM_SETFOCUS and WM_KILLFOCUS topics in the "Keyboard Input Reference" section in the MSDN library at http://msdn.microsoft.com/library."

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, May 12, 2010 5:29 AM
    Moderator

All replies

  • Use this instead:

    For Each row As DataRow In dt.Rows.Item(0).Item("fruits")...


    Only performance counts!
    Sunday, May 9, 2010 3:00 PM
  • thanx sylva it worked. but one thing that is happening to me when i filled the combo box items its taking alot of space in the combo box field. 

     

    like if i select "apple" then it should be "apple" but in this case its "apple                               ".

     

    this field in database is defined as varchar(50). please tell me why is there extra space on the right side of the text inside combo box.

    here is the code:

     

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

            'TODO: This line of code loads data into the '

            Me.TblMarketTableAdapter.Fill(Me.DatabaseDataSet.tblFruits)

            dt = DatabaseDataSet.tblFruits

            For Each row as DataRow In dt.Rows

                ComboBox1.Items.Add(row(1))

            Next

     With ComboBox1

                .DisplayMember = Me.row(1)

                .ValueMember = Me.row(0)

                .SelectedIndex = 0

                .AutoCompleteSource = AutoCompleteSource.ListItems

                .AutoCompleteMode = AutoCompleteMode.SuggestAppend

            End With

     

        End Sub

     

    thanx

    Monday, May 10, 2010 5:15 PM
  • I have no idea how to resolve that moreso when it appears that you're using the wizard. If you're writing your query by yourself, you can use the LEFT function to restrict the number of characters selected. But as a work-around, how about simply reducing the width of your combobox to the maximum acceptable lenght of your values?
    Only performance counts!
    Monday, May 10, 2010 6:24 PM
  • i will try the LEFT function and then let you know about it. yes im using the wizard to bind the data source but still i want to ask you something about it. i have also tried to look for help on the google but there is alot of material available which have different approaches and every approach can't be implemented so dats why i just want to ask you about it. 

     

    First I want to tell you about the application.

    1. It will be a windows form application which will be installed on the cilent's computer running Windows XP. 

    2. Upon Installation I will first check if the network connection is available, if yes, then the application will be installed. Also for the security purposes I will check the hard drive's ID number etc.

    3. This form will have to connect to a SQL Server installed on the Windows Server on the same network. 

    4. User's will add, update, detele and edit data from the client's computer where this application will be installed. 

    5. Some combo boxes will check the values entered by user, if they are in the items list of that combo box, if yes then nothing and if not then it will ask to add into the database and if yes then that value will be stored into the database. 

     

    This a limited but major functions that my application will be perform. So under such scenario, what you suggest me?

    1. How should I connec to MS SQL database? I have also got idea to define global variables in a Moduel?

    2. Cconfig file can be used to connect to sql server everytime i need a connection?

    3. I am really worried about the performance  because performance is the key. what parameters should i keep in mind?

     

    I am looking forward for your reply Sylva and I really appreciate the way you are replying to me. 

     

    Thank you once again.

     

    Regards

    Faisal

    Tuesday, May 11, 2010 4:56 AM
  • Your code still doesn't look quite right. Instead of filling the ComboBox by adding Items, why don't you simply use the DataSource:

    Private Sub admForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
     Me.TblMarketTableAdapter.Fill(Me.DatabaseDataSet.tblFruits)
     
     With ComboBox1
      ' I added the DataSource and changed the setting of Display/ValueMember properties
      .DataSource = Me.DatabaseDataSet.tblFruits
      .DisplayMember = Me.DatabaseDataSet.tblFruits.Columns(1).ColumnName
      .ValueMember = Me.DatabaseDataSet.tblFruits.Columns(0).ColumnName
     
      .SelectedIndex = 0
      .AutoCompleteSource = AutoCompleteSource.ListItems
      .AutoCompleteMode = AutoCompleteMode.SuggestAppend
     End With
    End Sub
     

     

     

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, May 11, 2010 5:10 AM
  • I'll suggest you resolve the combobox issue first and open as many new threads as possible for the other issues so that other forum members who maybe more experienced on those will weigh in.

    I hope this helps.


    Only performance counts!
    Tuesday, May 11, 2010 5:52 AM
  • Bonnie,

    I am adding items into the ComboBox because I want user to select the Items from the ComboBox. If user entered something which is not in the list of ComboBox Items, then a message box will be appeared asking user to add it into the database. Here is the code:

     

     Private Sub ComboBox1_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.LostFocus

            If Not ComboBox1.Items.Contains(ComboBox1.Text) AndAlso MsgBox("Do You Want to Add This Rank?", 1, "Adding into Database") = 1 Then

                'add inoto the database 

            End If

    End Sub

     

    This is what I am trying to achieve with the above code. If anything which is going wrong with the above code, please tell me as there will be 3 more ComboBoxes at the same form doing the same action. Please tell me if there is some better way to achieve this.

     

    Thank you.

    Tuesday, May 11, 2010 5:17 PM
  • Yes you are right, I should resolve ComboBox issue first.
    Tuesday, May 11, 2010 5:19 PM
  • I am adding items into the ComboBox because I want user to select the Items from the ComboBox. If user entered something which is not in the list of ComboBox Items, then a message box will be appeared asking user to add it into the database. Here is the code:

    Private Sub ComboBox1_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.LostFocus

            If Not ComboBox1.Items.Contains(ComboBox1.Text) AndAlso MsgBox("Do You Want to Add This Rank?", 1, "Adding into Database") = 1 Then

                'add inoto the database 

            End If

    End Sub

    Hi faisalVB,

    I think you can also achieve your requirement mentioned above with Bonnie's suggestion which is much clear and easy.  By the way, please note that:

    "The GotFocus and LostFocus events are low-level focus events that are tied to the WM_KILLFOCUS and WM_SETFOCUS Windows messages. Typically, the GotFocus and LostFocus events are only used when updating UICues or when writing custom controls. Instead the Enter and Leave events should be used for all controls except the Form class, which uses the Activated and Deactivate events. For more information about the GotFocus and LostFocus events, see the WM_SETFOCUS and WM_KILLFOCUS topics in the "Keyboard Input Reference" section in the MSDN library at http://msdn.microsoft.com/library."

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, May 12, 2010 5:29 AM
    Moderator
  • Hello again,

     

    after going though some articles and blogs and as suggested by Bonnie in her blog to not use wizards, i have decided to not use the wizards which is quite right thing to do. well my application is an admission and discharge system for a hospital. i have made m home work on it and now trying to code it. as suggested by bonnie above the combo box is filling in the items correctly but the problem is when i add anything into the database and return back to combobox, it wont updated. i tried almost everything which is available by googling but nothing tangible achieved. here is my code for the admission form (admForm). there will be a combo box for ranks/rates. if the ranks are not in the database and if user said yes then it will be added into the database which is doing right now, bu the combobox is not updated. Here is the code:

     

     

    Imports System.Data
    Imports System.Data.SqlClient
    
    Public Class admForm
    
     Dim daRanks As New SqlDataAdapter
     Dim cmdRanks As New SqlCommand
     Dim dsRanks As New DataSet
     Dim dtRanks As New DataTable
     Dim rankCat As String = Nothing
     
     Private Sub admForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
      Try
       conn = getConnect()
       conn.Open()
       cmdRanks = conn.CreateCommand
       cmdUnits = conn.CreateCommand
       cmdRanks.CommandText = "select * from tblRanks"
       daRanks.SelectCommand = cmdRanks
       daRanks.Fill(dsRanks, "tblRanks")
       dtRanks = dsRanks.Tables("tblRanks")
    If dtRanks.Rows.Count > 0 Then
        With cboRank
         .DataSource = dtRanks
         .DisplayMember = dtRanks.Columns(1).ColumnName
         .ValueMember = dtRanks.Columns(0).ColumnName
         .SelectedIndex = -1
    
         .AutoCompleteSource = AutoCompleteSource.ListItems
         .AutoCompleteMode = AutoCompleteMode.SuggestAppend
    
        End With
        
       End If
      Catch ex As Exception
       MsgBox("Error" & ex.Source & " : " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error!!")
      End Try
      conn.Close()
     End Sub
     
    Private Sub cboRank_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboRank.GotFocus
      With cboRank
       .DataSource = Nothing
       .Items.Clear()
       .DataSource = dsRanks.Tables("tblRanks")
       .DisplayMember = dsRanks.Tables("tblRanks").Columns(1).ColumnName
       .ValueMember = dsRanks.Tables("tblRanks").Columns(0).ColumnName
      End With
     End Sub
    
     Private Sub cboRank_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboRank.Leave
      If cboRank.Text = "" Then
       cboRank.Focus()
      Else
       Try
        conn.Open()
        For Each row As DataRow In dtRanks.Rows
         If cboRank.Text.ToUpper = row(1).ToString.ToUpper Then
          rankCat = row(2)
          Return
         End If
        Next
        If MsgBox("Rank/Rate Not Found. Add it now?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
         Try
          Dim newrank As String = cboRank.Text.ToUpper
          newrank = newrank.ToString
          trcomm = conn.CreateCommand
          trans = conn.BeginTransaction("BuilderTransaction")
          trcomm.Connection = conn
          trcomm.Transaction = trans
          trcomm.CommandText = "INSERT INTO tblRanks(Rank)VALUES('" & newrank & " ')"
          trcomm.ExecuteNonQuery()
          trans.Commit()
    
         Catch ex As Exception
          Try
           If Not (trans Is Nothing) Then
            trans.Rollback("BuilderTransaction")
           End If
          Catch b As SqlException
          End Try
         Finally
          If Not (conn Is Nothing) Then
           conn.Close()
          End If
         End Try
        Else
         If MsgBoxResult.No Then
          cboRank.Text = ""
          cboRank.Focus()
         End If
        End If
       Catch ex As Exception
       End Try
      End If
     End Sub
    End Class

    currently i am checking the updated value upon the got focus event of cboRank which is not a good idea too. So what i want is to add the new rank into the database and then select it in the combobox. So please help me to solve this issue. 

    thank you again.

    Wednesday, May 19, 2010 5:51 PM
  • Hi faisalVB,

    You just add the newrandk into the database, but not reflect it to the datasource of your combobox. You have two ways to solve the issue.
    One is after you add the newrank into the database, refill the dtRanks, then bind this new dtranks to the combobox. You can add all code in form_load into a new Sub(you can name it as "FillCombobox"), call the sub in form_load, and when you add the newrank into the database, call the sub again to refill the combobox. For example:

    Private Sub admForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

          FillCombobox()

    End Sub

     

    Private Sub FillCombobox()

          ‘Add all code previously in admForm_Load here

    End Sub

     
    '
    When you add newrank to database, call FillCombobox again

    '…………….

    trcomm.CommandText = "INSERT INTO tblRanks(Rank)VALUES('" & newrank & " ')"

    trcomm.ExecuteNonQuery()

    trans.Commit()

    FillCombobox()

    '……………..


    Another method is to add the newrank into the datatable dtRanks(which is the datasource of the combobox), then use daRanks.Update(dsRanks, "tblRanks") to update the newrank into the database. And it will be also added to the combobox.

     

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, May 20, 2010 3:22 AM
    Moderator