Link to home
Start Free TrialLog in
Avatar of indy500fan
indy500fan

asked on

How do I deal with no results from a SQL Command?

Friends,

As it stands, if my query has a result, it works correctly, but if it does not (i.e. there are no results), it means this is the start of a new record that I am going to insert into the database.

How can I do my code, so that if the result set is empty, it will actually do something.  As it stands now, if there are no results, when my code gets to the line:

While dr.Read()  

It just drops out of the loop, rather than executing the following code:

If dr.Item("LastName") Is Nothing Then
                txtLastName.Text = ""
                txtAttemptNumber.Text = "1"
            Else...


Here is the sub:


Public Sub GetDriverInfo(ByVal dsn)

        Dim data As New DataSet
        Dim r As DataRow

        Dim ColumnCount As Integer

        Dim con As New SqlClient.SqlConnection(dsn)

        data = New DataSet

        Dim dr As SqlClient.SqlDataReader

        con = New SqlClient.SqlConnection(dsn)
        con.Open()

        Dim c As New SqlClient.SqlCommand("GetDriverInfoSP " & "'" & txtCarNumber.Text & "'", con)
        dr = c.ExecuteReader(CommandBehavior.SingleResult)

        'Dim found As Boolean

        'Dim i As Integer
        'found = False

        'loop through the records

        While dr.Read()
            'Debug.WriteLine(dr.Item("LastName"))
            If dr.Item("LastName") Is Nothing Then
                txtLastName.Text = ""
                txtAttemptNumber.Text = "1"
            Else
                txtLastName.Text = dr.Item("LastName")
                txtFirstName.Text = dr.Item("FirstName")
                cmbEquipment.Text = dr.Item("Equipment")
                txtEngine.Text = dr.Item("Engine")
             End If

        End While

        con = Nothing
    End Sub

Thanks in advance!!!
ASKER CERTIFIED SOLUTION
Avatar of gregoryyoung
gregoryyoung
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of indy500fan
indy500fan

ASKER

gregoryyoung,

That makes sense.  Sorry I haven't replied sooner.  I went home ill and I have been away from my pc that has the project work.  As soon as I get my pc with the project on it I'll try it.
That worked.  Thanks for your help!
no worries. glad it worked.

Cheers!

Greg