check a table

[durilai]

I have a form with a text field that adds a record to a table, I want to check the value from that text field with a different table to see if it is valid. With the code below it will right the data if the record exists, but if it does not exist then I get an "Row Handle is Invalid" error. What am I doing wrong in the event that a record does not exist.

Private Sub btnAddprep_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddprep.Click
        If Me.txtAddPrep.Text <> String.Empty Then
            Try
                Dim ctecid1 As Integer
                oledbcon1.Open()
                strSQL1 = "Select * From Preparers WHERE ctecid = " & Me.txtAddPrep.Text
                cmd1 = New OleDbCommand(strSQL1, oledbcon1)
                objRead1 = cmd1.ExecuteReader
                objRead1.Read()
                ctecid1 = (objRead1("ctecID") & "")
                If ctecid1 = Nothing Then
                    MsgBox(ctecid1, MsgBoxStyle.OKOnly, "Invalid CTEC ID")
                Else
                    oledbcon.Open()
                    strSQL = "INSERT INTO Roster (classid, ctecid) VALUES ('" & vclassid & "', '" & ctecid1 & "')"
                    cmd = New OleDbCommand(strSQL, oledbcon)
                    cmd.ExecuteNonQuery()
                End If
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                oledbcon1.Close()
                oledbcon.Close()
                Me.txtAddPrep.Clear()
                Me.ListView1.Refresh()
            End Try
        End If
    End Sub

Thanks for any help

 

[sevenhalo]

Give this a shot, since you're not using anything your returning, ask for the number of instances in the table. That way you can always count on "something" being returned.

But don't forget to adjust it so that it's not a gaping hole for SQL injections.

If Me.txtAddPrep.Text <> String.Empty Then
Try
Dim ctecid1 As Integer
oledbcon1.Open()
strSQL1 = "Select count(ctecid) as numInstances From Preparers WHERE ctecid = " & Me.txtAddPrep.Text
cmd1 = New OleDb.OleDbCommand(strSQL1, oledbcon1)
objRead1 = cmd1.ExecuteReader(CommandBehavior.SingleResult)
While objRead1.Read
ctecid1 = (objRead1.Item("numInstances"))
If ctecid1 <= 0 Then
MessageBox.Show(ctecid1, MsgBoxStyle.OKOnly, "Invalid CTEC ID")
Else
oledbcon.Open()
strSQL = "INSERT INTO Roster (classid, ctecid) VALUES ('" & vclassid & "', '" & Me.txtAddPrep.Text.Trim & "')"
cmd = New OleDb.OleDbCommand(strSQL, oledbcon)
cmd.ExecuteNonQuery()
End If
End While
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
oledbcon1.Close()
oledbcon.Close()
Me.txtAddPrep.Clear()
End Try
End If