Returning Specific data from a database

[anpalmer]

Hi,

 

I am trying to return data from a database using an SQL query. I want the data to be displayed in a datagrid. The SQL query Is formatted in the usual way with SELECT .... FROM ... WHERE

 

The where is getting an input from a textbox on the screen. I want the datagrid to be populated with the only the data that matches the search criteria upon the click of a button. No matter what I have tried it has not worked and my head is starting to get sore from scatching it!

 

The code looks like this:

 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

If Not Page.IsPostBack Then

DataGridMaster.SelectedIndex = 0

 

'BindMaster()

Surname = txtSurname.Text

Dim strQuery As String = "Select Surname, Other_Names, Matter, Client_Number, Department, [Fee Earner], Work_Type, HTR_Reference, Initiating_Fee_Earner, Archive_Date, Destroy_Date, Box_Number, Comments, House_Number, Address_1, Address_2, Town, Postcode FROM tblEYWStest WHERE Surname = '" & Surname & "'"

Dim objConn As New SqlConnection(strConnection)

Dim dataAdapter As New SqlDataAdapter(strQuery, objConn)

Dim ds As New DataSet()

dataAdapter.Fill(ds)

DataGridMaster.DataSource = ds

DataGridMaster.DataBind()

End If

End Sub

 

I have only been able to get the datagrid to populate upon the page_load method so far and it only loads the whole database, which is a problem as the database has about 16,000 entries!

 

I am sure that searching a database for a specific entry is quite easy and that it is something realy stupid that I am doing wrong.

 

Any help would be greatly appreciated.

 

Cheers

 

Ashley :)

[Robby]

First off it's useless to check for IsPostBack in the Button click event because it will always be a post back. You should be doing the hit to the database in your page load event the first time only (If Not IsPostBack Then).

 

The other thing you should do is create a variable of type Dataview at the top of the page. something like this....

 

Private dv as DataView

 

Then do something like this with our existing code...

 

dv = ds.Tables(0).DefaultView

DataGridMaster.DataSource = dv

DataGridMaster.DataBind()

 

You can save the dv to the session object, then in the button click do this...

 

dv.rowfilter = "Surname = '" & txtSurname.Text & "'"

[anpalmer]

Thanks

 

Thanks for the quick response. I am going to give it a try now and hopefully this should solve the problem.

 

You don't know if it is possible to have an input form and have the ability to previwe the contents of the input (like a print preview) do you. I want people to be able to preview their input before actually submitting to the database.

 

Cheers

 

I'll ket you know how it worked.

 

Ashley :(

[anpalmer]

It Works!

 

Thanks man!

 

I used this code and it works, thanks very much!

 

Dim strQuery As String = "Select Surname, Other_Names, Matter, Client_Number, Department, [Fee Earner], Work_Type, HTR_Reference, Initiating_Fee_Earner, Archive_Date, Destroy_Date, Box_Number, Comments, House_Number, Address_1, Address_2, Town, Postcode FROM tblEYWStest"

Dim objConn As New SqlConnection(strConnection)

Dim dataAdapter As New SqlDataAdapter(strQuery, objConn)

Dim ds As New DataSet()

dataAdapter.Fill(ds)

dv = ds.Tables(0).DefaultView

dv.RowFilter = "Surname = '" & txtSurname.Text & "'"

DataGridMaster.DataSource = dv

DataGridMaster.DataBind()

 

Cheers

 

Ashley :D

[Robby]

As I stated in my previous reply, you should avoid hitting the database more times then needed, you can apply the RowFilter without the rest of the code. Give it shot.