none
Trim combobox empty space RRS feed

  • Question

  • Hello,

    I fill a lot of comboboxes with data from a database , but in one combobox there are 100 items and another one are only 2

    I select with * so there is a lot of empty space in the combobox where there are 2 items. The one with 100 items is full.

    Can i trim the combobox so it only shows the items and not the empty space ?

    There is a lot of empty space under the 2 numbers.


      cmd = New SqlCeCommand("Select * FROM Data", con)
            If con.State = ConnectionState.Closed Then con.Open()
            Dim sdr As SqlCeDataReader = cmd.ExecuteReader()
            While sdr.Read = True
               Combobox3.Text = sdr.Item("3")
                Combobox2.Items.Add(sdr.Item(1))
    
      End While
    
            sdr.Close()
            If con.State = ConnectionState.Open Then con.Close()
    
    
    

    Monday, June 18, 2018 1:41 PM

All replies

  • Hi

    Have you checked that the items being added to the CB are as displayed in the CB - for example,the only way I could reproduce your results was to add blank or Null items.

    Suggest you add a temporary line to check for Null or Empty items being added to the CB.


    Regards Les, Livingston, Scotland

    Monday, June 18, 2018 2:03 PM
  • Always do the least amount of work in code when working with database, instead let the database do the work for you. For example, to exclude empty values the following excludes values equal to null or an empty string.

    Using SQL-Server, should be the same for SqlCe

    SELECT FirstName 
    FROM dbo.People 
    WHERE FirstName IS NOT NULL AND FirstName <> '';


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, June 18, 2018 2:24 PM
    Moderator
  • Hello Karen ,

    I was looking at Google and found this.

    It is working , but is there a way that i don't have to declare every Column (Naam) when i select with *

    cmd = New SqlCeCommand("Select * FROM Data WHERE Naam <> ''", con)

    Monday, June 18, 2018 2:28 PM
  • Thanks Leshay.

    I am looking for this on Google

    check for Null or Empty items

    Monday, June 18, 2018 2:33 PM
  • You can use string concatenation. In SSMS (SQL-Server Management Studio) I wrote the following.

    You don't need the amount of single quotes I used, the trail end needs three rather than five.

    DECLARE @ColumnName AS NVARCHAR(MAX) = 'FirstName';
    
    DECLARE @sql nvarchar(max) = 'SELECT ' + @ColumnName + 
    ' FROM dbo.People WHERE ' + @ColumnName + ' IS NOT NULL AND ' + @ColumnName + ' <> '''''
    
    PRINT @sql
    EXEC sp_sqlexec  @sql

    The PRINT result is

    SELECT FirstName FROM dbo.People WHERE FirstName IS NOT NULL AND FirstName <> ''

    So in code you would have a local string variable, let's call it ColumnName e.g.

    Dim ColumnName As String = "FirstName"

    Which would be used to do the replacement as I did in SQL but you do this in VB.NET.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Monday, June 18, 2018 3:44 PM
    Moderator
  • Hi keeBlunder,

    If you want to add not null item into combobox, you just add one condiation in your code.

     While sdr.Read = True
                If sdr.Item("3").ToString = Nothing Then
                Else
                    ComboBox1.Items.Add(sdr.Item(“3”))
                End If
                If sdr.Item(1).ToString = Nothing Then
                Else
    
                    ComboBox2.Items.Add(sdr.Item(1))
                End If
    
            End While

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 19, 2018 6:04 AM
    Moderator
  • Hello Cherry Bu

    It is getting an error. 

    In try and catch it is showing the first line of data 2 time's and not the rest.

     Try
                cmd = New SqlCeCommand("Select * FROM Data ", con)
    
                If con.State = ConnectionState.Closed Then con.Open()
                Dim sdr As SqlCeDataReader = cmd.ExecuteReader()
                While sdr.Read = True
                    Plaats.Items.Add(sdr.Item(1))
    
                    Haven.Items.Add(sdr.Item(2))
                    If sdr.Item("1").ToString = Nothing Then ( here is the error System.IndexOutOfRangeException was unhandled .Make sure that the maximum index on a list is less than the list size)
                    Else
                        Plaats.Items.Add(sdr.Item("1"))
                    End If
                    If sdr.Item("2").ToString = Nothing Then
                    Else
                        Haven.Items.Add(sdr.Item("2"))
                    End If
    
                End While
    
    
                sdr.Close()
                If con.State = ConnectionState.Open Then con.Close()
    
            Catch ex As Exception
    
            End Try

    Friday, June 29, 2018 2:33 PM
  • You are entering into unsafe coding here via sdr.Item, instead use sdr.Get..... where Get may be GetString or GetDate etc.

    See the following;

    Dim conn As SqlCeConnection = Nothing
    Dim rdr As SqlCeDataReader = Nothing
    
    Try
        conn = New SqlCeConnection(connStr)
        conn.Open()
    
        Dim cmd As New SqlCeCommand("SELECT * FROM myTable", conn)
        cmd.Connection.Open()
    
        rdr = cmd.ExecuteReader()
    
        While rdr.Read()
            Console.WriteLine(rdr.GetString(0))
        End While
    Finally
        rdr.Close()
        conn.Close()
    End Try


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, June 29, 2018 2:41 PM
    Moderator
  • Hi

    First of all: DO NOT have an empty Try Catch block in the way you show. If an Exception occurs,your code effectively ignores it.

    In the Catch Ex As Exception, at the very least, put a Stop command there. Better still, a MessageBox to show the Ex.Message.

    Example:

     Try
       Dim a As Integer = Integer.MaxValue
       a += 1
     Catch ex As Exception
       MessageBox.Show(ex.Message)
     End Try
    


    Regards Les, Livingston, Scotland

    Friday, June 29, 2018 2:41 PM
  •  OT -  If your last question has been answered (link below),  then please remember to mark the post or posts that solved your problem as the answer(s).  Thanks.  8)

    Check if textboxes are empty then


    If you say it can`t be done then i`ll try it

    Friday, June 29, 2018 4:01 PM