How to change the SQL query behind a GridView

[Robert]

I have a gridview that I have bound to a DataSet that displays a customer's address information. (Select FNAME, LNAME, STREET, CITY, STATE, ZIP FROM CUSTOMERS)

I have a textbox that a user will type a search criteria into and click a button. What I am trying to do is run the sql again with that criteria applied and return the results to the user by updating this GridView.

This should be simple and I have done it before in Access. But, I am stumped right now and could use some help.

 

Table adapter is named CUSTOMERSTableAdapter

Binding source is named CUSTOMERSBindingSource

Data set is named ECOMLIVEDataSet

If my button was named btnCustomerSearch, what code should execute on the _click event to revise the SQL?

 

Thanks in advance. Maybe somebody can point to a tutorial online somewhere or help explain this.

[Robert]

PS: I am using Visual Basic but can translate if samples are provided in c#

[Dimitar]

Hi Robert,

Thank you for writing.

This is not a good approach for this because retrieving the data is an expensive operation. Moreover, you already have retrieved all the data. In addition, you will have to rebind the grid, which will recreate all columns and rows again and this is an expensive operation as well. 

It would be better to use the grid default filtering functionality. In this case, you will need to add filter descriptor when the button is pressed. Detailed information is available in the following articles:

• Basic Filtering
• Setting Filters Programmatically (simple descriptors)

In addition, I believe that the following resources are containing what you are looking for:

• ADO.NET Code Examples
• .net - How connect to database from C#? - Stack Overflow
  

I hope this will be useful. 

Regards,
Dimitar
Telerik by Progress

[Robert]

Thank you for the reply. The issue here is that the table that has the data contains about 2,500,000 customer records.

I can't see selecting all 2.5MM records and then filtering..... Not to mention, if we have 20 users using this program. 

With that in mind, do you have any other thoughts?

[Dimitar]

Hello Robert,

You can use RadVirtualGrid for such scenarios. It is designed to load only part of the data and it supports filtering as well:

• VirtualGrid
• Filtering Overview 
  

Let me know if you have additional questions.

Regards,
Dimitar
Telerik by Progress

[Robert]

Great, Thanks. I have been exploring the radVirtualGrid examples.

 

When I take the example source and adapter it to my SQL database, the application force-quits without any notification.  It always happens at the same point without any notification or errors thrown.

 

        If e.RowIndex = RadVirtualGrid.HeaderRowIndex Then
            e.Value = columnNames(e.ColumnIndex)
        End If

 

I set the ColumnCount and RowCount on the grid properties.... 

 

Any thoughts?

[Robert]

I got it. I was using the wrong provider.....

[Hristo]

Hello Robert,

Thank you for writing back.

I am glad that you are having the project working as expected now.

Please let me know if you need further assistance.

Regards,
Hristo Merdjanov
Telerik by Progress