Testing for data in SQL Server

I need to test to see if case numbers exist in the database or not. This is what I have so far:

VB Code:

If IsNumeric(txtSearchCase.Text.Substring(0)) Then
            cmdSqlCommand1.CommandText = "SELECT * FROM tblCapRec WHERE CaseNumber LIKE '" & txtSearchCase.Text & "%' ORDER BY CaseNumber, PartNumber, LastName, FirstName"

It works fine if the case is found in the database, but if it's not it gives me an error. How do I test to see if it exists or not?

Thanks!

Brenda

Here is more of my coding if that helps:

VB Code:

If IsNumeric(txtSearchCase.Text.Substring(0)) Then
            cmdSqlCommand1.CommandText = "SELECT * FROM tblCapRec WHERE CaseNumber LIKE '" & txtSearchCase.Text & "%' ORDER BY CaseNumber, PartNumber, LastName, FirstName"
        ElseIf txtSearchCase.Text.Length = 0 Then
            MessageBox.Show("Please Enter a Case Number or Last Name!", "Message Box", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtSearchCase.Focus()
            Exit Sub
        ElseIf Not IsNumeric(txtSearchCase.Text.Substring(0)) Then
            cmdSqlCommand1.CommandText = "SELECT * FROM tblCapRec WHERE LastName LIKE '" & txtSearchCase.Text & "%' ORDER BY LastName, FirstName, CaseNumber, PartNumber"
        Else
        End If
        cmdSqlCommand1.Connection = SqlConn
        SqlConn.Open()
        Dim drSqlDataReader1 As SqlDataReader = cmdSqlCommand1.ExecuteReader()
        drSqlDataReader1.Read()
 
        'Read in values from CapRec Table
        CaseNumber = drSqlDataReader1.Item(0)
        g_CaseNum = drSqlDataReader1.Item(0)
                               ....
End Sub

Thanks!

Maybe it has something to do with "HasRows." Have any of you ever used that before. I try to put it in my coding, but it doesn't like it.

VB Code:

If drSqlDataReader1.HasRows Then
           drSqlDataReader1.Read()
           
        Else
            Console.WriteLine("No rows returned.")
        End If

Maybe something like that? HELP!!

VB Code:

If drSqlDataReader1.Read() Then 
...
'Read in values from CapRec Table
CaseNumber = drSqlDataReader1.Item(0)
g_CaseNum = drSqlDataReader1.Item(0)
...
Else
Msgbox "No Case Numbers Matched."
End if

Or you can use the Try...Catch...Finally syntax.

TG

Thanks!

I use the HasRows method all the time and it has never failed.

HasRows is only available in .NET 1.1, is that what you mean by "it doesn't like it"? Are you getting a compile error or runtime error.

Be more specific when explaining your problems.