Find out if there is any rows in SQLCommand query

Question

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

AdaTheDev · Accepted Answer · 2010-11-09 18:30:26Z

2

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 ;)

edited Nov 9, 2010 at 18:30

answered Nov 9, 2010 at 18:19

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jamie Taylor Over a year ago

This works without the dgBookings.DataSource = query.Executereader when i put that bit in it says There is already an open DataReader associated with this Command which must be closed first

AdaTheDev Over a year ago

Sorry, copy n paste error! corrected now - should have replaced that to use rdr

Brandon Montgomery · Accepted Answer · 2010-11-09 18:19:13Z

2

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

answered Nov 9, 2010 at 18:19

Brandon Montgomery

6,9853 gold badges50 silver badges71 bronze badges

Collectives™ on Stack Overflow

Find out if there is any rows in SQLCommand query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related