Data reader has rows but not populating combobox

Question

I have this piece of code that is not populating combobox from datareader. I have done all kinds of checks and confiirmed that the database is connecting and the query is also correct. Databaseconnections is a module where I have declared all my variables including connection string and datareader(Public dr As SqlDataReader). When debbuging everything goes smoothly but when I hit the While loop it jumps to the end of the loop without any errors. I cant understand what am missing out. Please help.

Public Sub loadLocatns()

    Try
        Openconn()
        cmd.CommandText = "SELECT distinct jobs.[file] FROM [BERVSDB].[dbo].[jobQueue] as jobs where (jobs.form_status<>'c') and (jobs.CI = '" & Ccode & "') and (jobs.[file]  in( SELECT dts.[file] from [BERVSDB].[dbo].[master]  as dts where  (dts.[status] is null) or (dts.[status] <> 'X')  ))"
        cmd.Connection = conn
        Databaseconnections.dr = cmd.ExecuteReader()
        If IsNothing(dr) Then
            MessageBox.Show("No Data found")
        Else
            If dr.Read() Then
                While dr.Read
                    Me.ComboBox1.Items.Add(dr(0))
                End While
            Else
                MessageBox.Show("Not reading!")
            End If
            dr.Close()
            conn.Close()
        End If

    Catch ex As Exception
        MessageBox.Show("Error:" & ex.Message)
    End Try

End Sub

It's not the answer, but you are always at least skipping the first item because you are doing a extra `Read`. You `Read` the first item in the `If` and then read starting with the second item in the `While`. — tcarvin, Mar 20 '13 at 12:49

score 0 · Answer 1 · answered Mar 20 '13 at 12:52

0

Seems to me that you read from the reader twice, first one when u check it IF dr.Read() THEN and the second one on While dr.Read and if you have one item it will not be added.

Solution: you can use DO ... WHILE condition which allows you to read first since you called If dr.Read() Then (Sorry I'm not familiar with VB.NET so I can't write a full code for you but I hope that you got the point)

answered Mar 20 '13 at 12:52

Ahmed Magdy

5,956
8
43
75

Thanks, let me try the Do...while loop. – Vic Mar 21 '13 at 06:39

score 0 · Answer 2 · answered Mar 20 '13 at 13:38

0

Try this instead. Already hinted at by @AMgdy.

Try
    Openconn()
    cmd.CommandText = "SELECT distinct jobs.[file] FROM [BERVSDB].[dbo].[jobQueue] as jobs where (jobs.form_status<>'c') and (jobs.CI = '" & Ccode & "') and (jobs.[file]  in( SELECT dts.[file] from [BERVSDB].[dbo].[master]  as dts where  (dts.[status] is null) or (dts.[status] <> 'X')  ))"
    cmd.Connection = conn
    Databaseconnections.dr = cmd.ExecuteReader()
    If Not dr.HasRows Then
        MessageBox.Show("No Data found")

    Else
        While dr.Read()
            Me.ComboBox1.Items.Add(dr(0))
        End While
    End If

    dr.Close()
    conn.Close()

Catch ex As Exception
    MessageBox.Show("Error:" & ex.Message)
End Try

Maybe wrap your connection, command and reader in using blocks to ensure they're disposed of correctly too.

answered Mar 20 '13 at 13:38

David Osborne

6,436
1
21
35

Hi, thanks for the response. I have tried the above code but it still jumps the While loop. The datareader has rows thats why the else part of the If...Else statement is executed but then the While loop is not executed.Its confusing, any further thoughts? – Vic Mar 21 '13 at 06:38
So dr.Read() is returning False? – David Osborne Mar 21 '13 at 09:25
Are dr and Databaseconnections.dr one and the same? Do you have option explicit on? – David Osborne Mar 21 '13 at 13:29

score 0 · Answer 3 · answered Mar 21 '13 at 10:26

I’d try checking the query through SQL Server Profiler and then executing it through SSMS to see what’s going on. What AMgdy and David Osborne noticed is correct but I guess you are expecting more than just one row (otherwise you wouldn’t be using while loop).

Another suggestion is to use parameters instead of string concatenation as this is easily hacked with SQL injection and also runs slower than parameterized queries.

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT distinct jobs.[file] FROM 
[BERVSDB].[dbo].[jobQueue] as jobs where (jobs.form_status<>'c') and
(jobs.CI = @CCode) and (jobs.[file]  in( SELECT dts.[file] from
[BERVSDB].[dbo].[master]  as dts where  (dts.[status] is null) or 
(dts.[status] <> 'X')  ))";
cmd.Parameters.Add(new SqlParameter("@CCode", Ccode));

Data reader has rows but not populating combobox

3 Answers3