CODEWITHSUNDEEP
asp.netsql
Jamie Taylor
Jamie Taylor

Reputation: 3530

Find out if there is any rows in SQLCommand query

I have some code like this

Dim conn As SqlConnection = New SqlConnection("server='h'; user id='w'; password='w'; database='w'; pooling='false'") 

conn.Open() 

Dim query As New SqlCommand("DECLARE @investor varchar(10), @sql varchar(1000) Select @investor = 69836 select @sql = 'SELECT * FROM OPENQUERY(db,''SELECT * FROM table WHERE investor = ''''' + @investor + ''''''')' EXEC(@sql)", conn) 

dgBookings.DataSource = query.ExecuteReader 
dgBookings.DataBind()

I need to add in an If statement basically saying if the SQLCommand query returns no rows then show a specific label

Can this be done?

Thanks

Upvotes: 0

Views: 1970

Answers (2)

AdaTheDev
AdaTheDev

Reputation: 147324

Try:

Dim conn As SqlConnection = New SqlConnection("server='h'; user id='w'; password='w'; database='w'; pooling='false'") 

conn.Open() 

Dim query As New SqlCommand("DECLARE @investor varchar(10), @sql varchar(1000) Select @investor = 69836 select @sql = 'SELECT * FROM OPENQUERY(db,''SELECT * FROM table WHERE investor = ''''' + @investor + ''''''')' EXEC(@sql)", conn) 

Dim rdr as SqlDataReader
rdr = query.ExecuteReader()

if (rdr.HasRows) then
   dgBookings.DataSource = rdr
   dgBookings.DataBind() 
else
    .....
end if

Obviously you could do with ensuring the objects are cleaned up/disposed of, so this is just to demonstrate HasRows ;)

Upvotes: 2

Brandon Montgomery
Brandon Montgomery

Reputation: 6986

You are being returned a SqlDataReader when you call ExecuteReader(), and that SqlDataReader has a HasRows property you can check.

Dim oDR As SqlDataReader = query.ExecuteReader()

If oDR.HasRows Then
  lblNoRows.Visible = False
Else
  lblNoRows.Visible = True
End IF

Upvotes: 2

Related Questions