ADO.NET: How to tell if a DataSet or DataRow is empty

[damogets]

After much convincing by other people, I have finally started trying to use ADO.NET instead of ADODB. Unfortunately, my lack of knowledge regarding these objects has left me broken in one area. I tried converting the area where I am checking for duplicate records to use ADO.NET by checking if a student filling out a form enters the same first name, last name, and program or major as another record in the database as per my instructions. Unfortunately, in the following code snippet, it is not behaving how I thought it should and I am unable to diagnose exactly where the problem might be just yet. I would appreciate any helpful information or pointers in the right direction on this issue that you might be able to give.

Here is the code in question:

 
        Try
            Dim orion As New SqlConnection(strConn)
            Dim sqlCmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(query, orion)
 
            sqlCmd.Parameters.Add("@fName", System.Data.SqlDbType.NVarChar).Value = firstName.Text
            sqlCmd.Parameters.Add("@lName", System.Data.SqlDbType.NVarChar).Value = lastName.Text
            sqlCmd.Parameters.Add("@progMaj", System.Data.SqlDbType.NVarChar).Value = progMajor.SelectedValue.ToString
 
            Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCmd)
            Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 
            dataAdapter.Fill(dataSet)
 
            If (dataSet.Tables(0).CreateDataReader.HasRows) Then
                'we already have this record in the database
                errBlurb.Text &= "Already have this record." & vbCrLf
                success = False
            End If
        Catch
            errBlurb.Text &= "Error checking for duplicate records." & vbCrLf
            success = False
        End Try
 

I probably should have mentioned also that it never flags an error despite there being 'duplicate' records in the database at this point. Thank you for any pointers in the right direction or information/hints you might be able to give me in this matter!

[yarbrough40]

please show your sql. It's difficult to see exactly what you are doing without it

[damogets]

Sorry that it took me so long to get back to you. I ended up with a serious case of pneumonia that put me down for 4 weeks and I'm just now getting back to working on this project. Here is the whole code excerpt you requested, including the SQL. Please let me know if you need anything more, and thanks for your time taking a peek at this.

        'now check to see if this is a duplicate record
        Dim strConn, query As String
 
        strConn = "SERVER=BSC-Orion\HomeApps; DATABASE=BSC-Survey; " & _
                "UID=nope; PWD=yeah_right;"
        'removing PROVIDER=SQLOLEDB; from the start of the string
        query = "SELECT Fname, Lname, ProgramMajor FROM [Student_Employment] WHERE " & _
                "Fname = @fName AND fName = @lName AND ProgramMajor = @progMaj"
 
        Try
            Dim orion As New SqlConnection(strConn)
            Dim sqlCmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(query, orion)
 
            sqlCmd.Parameters.Add("@fName", System.Data.SqlDbType.NVarChar).Value = firstName.Text
            sqlCmd.Parameters.Add("@lName", System.Data.SqlDbType.NVarChar).Value = lastName.Text
            sqlCmd.Parameters.Add("@progMaj", System.Data.SqlDbType.NVarChar).Value = progMajor.SelectedValue.ToString
 
            Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCmd)
            Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 
            dataAdapter.Fill(dataSet)
 
            'If (dataSet.Tables(0).CreateDataReader().HasRows) Then
            'we already have this record in the database
            '    errBlurb.Text &= "Already have this record." & vbCrLf
            '    success = False
            'End If
 
            Dim count As Integer = dataSet.Tables(0).Rows.Count
            'For Each dr As DataRow In dataSet.Tables(0).Rows.
            ' For Each it As Object In dr.ItemArray
            'count += 1
            'Next
            'Next
 
            If (count > 0) Then
                'duplicate record
                errBlurb.Text &= "Already have this record x" & count.ToString() & vbCrLf
                success = vbFalse
            End If
        Catch
            errBlurb.Text &= "Error checking for duplicate records." & vbCrLf
            success = False
        End Try

[damogets]

Alright, I found my mistake in that whole mess. Turns out I was accessing the ADO stuff correctly, I just wasn't familiar enough with VB to know that I had to ReDim the array that I was loading with the information. Whoops!