how to test for empty result of query

[Marivic]

hi,

im using datareader for my select statement. How will I test if this select stmt output empty results?

thanks in advance,
marivic

[Marivic]

im using sqlcommand rather not datareader.

[Edneeis]

Its not the best but you can use a Try..Catch..Fail and try to read it, if it fails its empty. Most of the time you can use a while and so it doesn't matter really if its empty.

While myReader.Read()
        str.Add(myReader.Item(0).Value)
    End While
    If Str.Length=0 then Msgbox "Read returned empty"

[Marivic]

hi edneeis,

actually im testing if the record exists. so im issuing an sql select via sqlcommand.

If recordexist() Then
UpdateRecord()
Else
AddRecord()
MessageBox.Show("record not exist")
End If

and my recordexist function:

Function recordexist()
Dim cnSQL As SqlConnection
Dim cmSQL As SqlCommand
Dim drSQL As SqlDataReader
Dim strSQL As String
Dim x As Integer
Try
strSQL = "select grpcode, grpdesc, monday, tuesday," & _
"thursday, friday from calendar2 " & _
"where grpcode=" & PrepareStr(CType(cbgroup.Items(cbgroup.SelectedIndex), ListItem).ID)
cnSQL = New SqlConnection(ConnectionString)
cnSQL.Open()

cmSQL = New SqlCommand(strSQL, cnSQL)
drSQL = cmSQL.ExecuteReader()

***' Here I want to test if the result set is empty so i could return true if empty******

If drSQL.Read Then
Return True
Else
Return False
End If


' Close and Clean up objects
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
refreshgrid()

Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
End Try
End Function

Any ideas? What alternative approach shld I use?

Thanks again.

[Edneeis]

Why not just get a counr of the query since you don't actually need any of the select info?

Try
strSQL = "select [b]Count(*)[/b] from calendar2 " & _
"where grpcode=" & PrepareStr(CType(cbgroup.Items(cbgroup.SelectedIndex), ListItem).ID)
cnSQL = New SqlConnection(ConnectionString)
cnSQL.Open()
 
cmSQL = New SqlCommand(strSQL, cnSQL)
 
[b]'you don't even need the reader
dim count as integer=cmSQL.ExecuteScalar()
 
If count>0 then[/b]
Return True
Else
Return False
End If
 
 
' Close and Clean up objects
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
refreshgrid()
 
Catch Exp As SqlException

Also to make your post look like code use the [ vbcode ] [ /vbcode ] without the spaces in the brackets.

[Marivic]

Thanks!! You're a big help.