How do i deal with SQL Queries that return nothing?

I have a problem with my program in that i am loading some data from a table, but quite often there is no data.  So i need a way to either detect if the query returns nothing, or find a way to bipass it and ignore it.

Here is my code:

        Dim dsCon As New sqlDataCon() 'this is a class i created to do all my data connections

        dsCon.SqlQuery = "SELECT * FROM SupplierList WHERE (" & locConverter(loc) & " AND PLU = " & pluTxt.Text & ")"
        dsCon.fillDs(secVendDS)
        Dim i As Integer

        For i = 0 To secVendDS.Tables(0).Rows.Count - 1
            CheckedListBox1.Items.Add(CStr(secVendDS.Tables(0).Rows(i).Item("VendorNo") & "") & " " & secVendDS.Tables(0).Rows(i).Item("VendorName"))
        Next
---------------SqlDataCon Class
Public Class sqlDataCon
    Public SqlQuery As String

    Private Con As SqlConnection
    Public Function fillDs(ByRef DS As DataSet)
        Dim IniSet As New iniSettings() 'iniset is a class i use to pull the server values from an XML file
        Con = New _
                   SqlConnection(IniSet.GenerateConString)        'sqlconnection
        Con.Open()
        Dim DataAdapter As SqlDataAdapter = New SqlDataAdapter(SqlQuery, Con)
        Try
            DataAdapter.Fill(DS)
        Catch ex As SqlException
            MessageBox.Show(ex.ToString)
            'MsgBox(ex.ToString)
        End Try
        Con.Close()
        'Return DS
    End Function
LVL 2
ethnarchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ptakjaCommented:
I believe the Fill function of the DataAdapter returns an integer indicating how many rows were added to the dataset.
Jeff CertainCommented:
Note: you don't need to open/close connections when using data adapters...

Also, this is a sub not a function at the moment...

And, finally, you can simply check ds.Tables(0).Rows.Count to determine if any rows were returned.

   Public Sub fillDs(ByRef DS As DataSet)
        Dim IniSet As New iniSettings() 'iniset is a class i use to pull the server values from an XML file
        Con = New SqlConnection(IniSet.GenerateConString)        'sqlconnection
        Dim DataAdapter As SqlDataAdapter = New SqlDataAdapter(SqlQuery, Con)
        Try
            DataAdapter.Fill(DS)
        Catch ex As SqlException
            MessageBox.Show(ex.ToString)
            'MsgBox(ex.ToString)
        End Try
    End Sub
Fernando SotoRetiredCommented:
Hi ethnarch;

The DataAdapter returns an integer of the number of rows it filled. Change your code ad follows to catch calls to fill with zero rows.

   Public Function fillDs(ByRef DS As DataSet)
        Dim IniSet As New iniSettings() 'iniset is a class i use to pull the server values from an XML file
        Con = New _
                   SqlConnection(IniSet.GenerateConString)        'sqlconnection
        Con.Open()
        Dim DataAdapter As SqlDataAdapter = New SqlDataAdapter(SqlQuery, Con)
        Try
            Dim rowCount As Integer
            rowCount = DataAdapter.Fill(DS)
            If rowCount = 0 Then
                ' No data records returned
                ' Do what you need to do here if no records.
            End If
        Catch ex As SqlException
            MessageBox.Show(ex.ToString)
            'MsgBox(ex.ToString)
        End Try
        Con.Close()
        'Return DS
    End Function

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.