Seeking advice for datagrid

[Theseekers]

Hi Tekers,

I am fairly new to ASP.net and have simple question as I am trying to learn ASP.net.

I have a page where user can search for either last name, first name, or both. My ultimate goal is this: if any record found load it up with data from the recordset. If not don't show the grid.

In order to find out the # of record return I have used the code below to get the # of record returned:

   Dim SQLQryStr as string = ("Select count(*) from <table name> where fname like '" & Request.Form("TxtFname") & "%'")

   DBCommand = New SqlCommand(SQLQryStr, DBConn)
   DBConn.Open()
   RecCount = CType(DBCommand.ExecuteScalar, Integer)
   DBConn.Close()

   if (RecCount > 0) then
      response.write (reccound & " record(s) found.")
   else
      response.write ("no record found")
   endif

This is ok however, if I want to populate my grid with data; I have to make another connection to the database with a different SQLQryStr where I explicitly go after fields that I want to display on the gird. This seem to me is very inefficient and will create lots of trips between client and server.

My question is this. Is there away that i can kill 2 birds with 1 stone meaning get the total of record and at the same time get the wanted fields for the grid?

TIA for your input and suggestion

The seekers is out seeking......

 

[jbenson001]

Yes, you just need to make some simple changes to your code:

Dim SQLQryStr As String = ("Select [b]Cols You Want[/b] from <table name> where fname like '" & Request.Form("TxtFname") & "%'")
Dim DBCommand = New SqlCommand(SQLQryStr, DBConn)
Dim sda = New SqlDataAdapter(DBcomm.commandtext, DBConn)
Dim ds As New DataSet
sda.fill(ds)

'Check if you have any rows, if so, then bind the grid.
If ds.Tables(0).Rows.Count > 0 Then
   DataGrid.DataSource = ds
   DataGrid.DataBind()
End If

 

[Theseekers]

Thanks Jbenson.

I have it working but I think your solution is much more compact than mine. Will give it a try

Thanks again

 

[jshurst]

I agree with jbenson. This way is very helpful for things like functions and web methods (in web services) too. You can return some data if there is any, if not then it returns an empty dataset (or datatable).