SqlDataReader Add Blank Row

[BrailleSchool]

Hi all,

I am populating a ComboBox using an SPROC using a SqlDataReader and it works perfectly; however, I want to be able to manually add a blank row to the SqlDataReader, so that the default option in the ComboBox is empty.

I can do this using a DataSet with no issues, but I don't want to update a database (MSSQL), I just want to pull information from the database to provide options to the end user.

This is what I have and how I am going about it. Any suggestions as to how this can be done? I have searched for many hours on adding a blank row to a SqlDataReader and have not found anything so far.

ALTER PROCEDURE GetFeeFrequencies
AS
BEGIN
    SELECT ServiceFeeFrequency AS SFF
    FROM FeeFrequencies
    ORDER BY SFF ASC
END

Private Sub CCProcessors_Load(ByVal sender As System.Object, _
                                  ByVal e As System.EventArgs) Handles MyBase.Load
 
        LoadFeeFreqDropDown(ServiceFeeFrequencySR)
    End Sub
 
    Public Sub LoadFeeFreqDropDown(ByVal CBName As Elegant.Ui.ComboBox)
        Using SetDatabaseConnection As New SqlConnection(ConnectToDatabase)
            Using GetFeeFreq As SqlCommand = New SqlCommand
                Try
                    SetDatabaseConnection.Open()
 
                    With GetFeeFreq
                        .Connection = SetDatabaseConnection
                        .CommandType = CommandType.StoredProcedure
                        .CommandText = "GetFeeFrequencies"
                        .ExecuteNonQuery()
                    End With
 
                    Dim FeeFreqDr As SqlDataReader
                    FeeFreqDr = GetFeeFreq.ExecuteReader
 
                    'TODO: Add a blank entry/row to the SqlDataReader and make it default.
 
                    With CBName
                        While FeeFreqDr.Read
                            If FeeFreqDr.HasRows Then
                                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                                .AutoCompleteSource = AutoCompleteSource.ListItems
                                .Items.Add(FeeFreqDr("SFF"))
                                .DisplayMember = CStr(FeeFreqDr("SFF"))
                                .ValueMember = CStr(FeeFreqDr("SFF"))
                                '.Text = vbNullString
                            End If
                        End While
                    End With
 
                    FeeFreqDr.Close()
                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)
                Catch IdxOutOfRangeEx As IndexOutOfRangeException
                    MessageBox.Show(IdxOutOfRangeEx.ToString, _
                                    "Index Out Of Range Exception", _
                                    MessageBoxButtons.OK, _
                                    MessageBoxIcon.Error, _
                                    MessageBoxDefaultButton.Button1)
                Finally
                    SetDatabaseConnection.Close()
                End Try
            End Using
        End Using
    End Sub

[wes4dbt]

I can do this using a DataSet with no issues, but I don't want to update a database (MSSQL)

You wouldn't use a dataset, you would use a datatable (it could be part of a dataset or a stand alone). There's no issue with updating the database, you just don't call the update method.

[BrailleSchool]

You wouldn't use a dataset, you would use a datatable (it could be part of a dataset or a stand alone). There's no issue with updating the database, you just don't call the update method.

Oh, okay wes4dbt. I'll look into the DataTable. It's been a while since I used one.

[IanRyder]

Hi,

Maybe I am missing something or maybe this is just the default behaviour of the Elegant.UI.ComboBox but a standard ComboBox does not have anything selected when you use its Add or AddRange Methods and even if it did then to Deselect Something after Items have been added you would just set the SelectedIndex Property to -1. Is this not possible with the Elegant.UI.ComboBox control?

On another note, that bit of code you posted is a little inefficient since it checks a DataReader for Rows after it Starts to read it and you set the ComboBox properties multiple times while reading the reader which you should not do. Finally you only need to set the DisplayMember and ValueMember Properties when you set the DataSource Property of the ComboBox but again this may not be the case with this Custom Control. Have a try with this and depending on how many items you are loading you should see a significant increase in Speed when loading the Control:-

If FeeFreqDr.HasRows Then
  With CBName
    .AutoCompleteMode = AutoCompleteMode.SuggestAppend
    .AutoCompleteSource = AutoCompleteSource.ListItems
    While FeeFreqDr.Read
      .Items.Add(FeeFreqDr("FullName"))
    End While
    .SelectedIndex = -1
  End With
End If

Hope that helps.

Cheers,

Ian

[BrailleSchool]

Yes, by default, the option is blank; however, if the end user selects an option from the list of available options, they are not able to specify a blank option should they want to. This is the reason I wanted to add a blank line to the ComboBox. Only way I can do that as far as I can tell is to add the blank line/record/row to the SqlDataReader. I just haven't been able to figure that out. I can do it on an SqlDataSet. Someone also suggested to use a DataTable and do it that way. Either way, the options for my ComboBoxes are pulled from a database, so that all the ComboBoxes that use the same options are not hard coded and can be added or removed and then all ComboBoxes using the database records would be affected. The Elegant ComboBox is based off the WinForms ComboBox, but the coding is a little different. It's a great product and it makes my WinForms apps look like Microsoft products with ease.

[Shaggy Hiker]

You can't add a blank line to a datareader, that would pretty much defeat the purpose of a datareader. However, you don't need to. The way you were populating the control is by looping through the reader and adding each item in turn. You never needed the valuemember or displaymember, because you weren't actually binding the control to the reader. So, just add a blank line to the control prior to populating it from the datareader.

[BrailleSchool]

I made a mistake in my programming and fixed the issue. The ComboBox is SuggestAppend, but was not editable, so changed it from False to True and can just delete the item selected and I am all good for now.

[wes4dbt]

If you really want to use a reader then don't bind the combobox. Load the items using a loop. Like IanRydar shown but just add the blank line before you enter the loop.

If FeeFreqDr.HasRows Then
  With CBName
    .AutoCompleteMode = AutoCompleteMode.SuggestAppend
    .AutoCompleteSource = AutoCompleteSource.ListItems
    .Items.Add(" ")
    While FeeFreqDr.Read
      .Items.Add(FeeFreqDr("FullName"))
    End While
    .SelectedIndex = -1
  End With
End If

[BrailleSchool]

You can't add a blank line to a datareader, that would pretty much defeat the purpose of a datareader. However, you don't need to. The way you were populating the control is by looping through the reader and adding each item in turn. You never needed the valuemember or displaymember, because you weren't actually binding the control to the reader. So, just add a blank line to the control prior to populating it from the datareader.

I did try adding a blank line, but the blank line was showing the same amount as the others, so if I had five options, there were five blank lines, too. I didn't know you can't add blank lines to DataReaders. Learned something new there. Thanks for the info. I will have to read up more about ValueMember and DisplayMember to learn exactly what they do.

[BrailleSchool]

If you really want to use a reader then don't bind the combobox. Load the items using a loop. Like IanRydar shown but just add the blank line before you enter the loop.

If FeeFreqDr.HasRows Then
  With CBName
    .AutoCompleteMode = AutoCompleteMode.SuggestAppend
    .AutoCompleteSource = AutoCompleteSource.ListItems
    .Items.Add(" ")
    While FeeFreqDr.Read
      .Items.Add(FeeFreqDr("FullName"))
    End While
    .SelectedIndex = -1
  End With
End If

Thanks for the code snippet.

[Shaggy Hiker]

You were adding the blank line inside the loop, then. Do it as wes4dbt showed: Add one blank line, THEN fill the control with the loop.

However, I'd suggest that you not add a line that is truly blank. Blank lines in comboboxes are EASILY overlooked by the user. If the purpose of the blank line is to indicate "no selection", then put something like "<No Selection>" in the control. An actual blank line just doesn't make it clear to the user what impact selecting it will have.

[techgnome]

I did try adding a blank line, but the blank line was showing the same amount as the others, so if I had five options, there were five blank lines, too. I didn't know you can't add blank lines to DataReaders. Learned something new there. Thanks for the info. I will have to read up more about ValueMember and DisplayMember to learn exactly what they do.

Probably because you were adding the blank inside hte loop... had you had it on the outside, it would have only added it once.

ValueMember and DisplayMember are useful when binding the combobox to a list of somethings... could be a List(Of), or a DataTable, or even an array I think. Basically anything that implements IEnumerable - I think that's right. It wouldn't help in this case since you're not actually binding to anything - which is why then adding the blank manually at the start is all you should need.

-tg

[BrailleSchool]

You were adding the blank line inside the loop, then. Do it as wes4dbt showed: Add one blank line, THEN fill the control with the loop.

However, I'd suggest that you not add a line that is truly blank. Blank lines in comboboxes are EASILY overlooked by the user. If the purpose of the blank line is to indicate "no selection", then put something like "<No Selection>" in the control. An actual blank line just doesn't make it clear to the user what impact selecting it will have.

That's what I usually do, but I also check for blanks before committing to a database.

[BrailleSchool]

Probably because you were adding the blank inside hte loop... had you had it on the outside, it would have only added it once.

ValueMember and DisplayMember are useful when binding the combobox to a list of somethings... could be a List(Of), or a DataTable, or even an array I think. Basically anything that implements IEnumerable - I think that's right. It wouldn't help in this case since you're not actually binding to anything - which is why then adding the blank manually at the start is all you should need.

-tg

I removed the DisplayMember and ValueMember from the code and nothing changed. It displays as intended.
I refer to MSDN and VBF all the time for different things. Always try to research myself before posting as it may have been answered before. Thanks for the clarification.

[techgnome]

I removed the DisplayMember and ValueMember from the code and nothing changed. It displays as intended.
I refer to MSDN and VBF all the time for different things. Always try to research myself before posting as it may have been answered before. Thanks for the clarification.

Of course it did... you weren't using binding, so the two properties have no effect.

-tg