Returning Specific data from a database

A

anpalmer

Newcomer
Joined
Sep 23, 2004
Messages
17
Location
Manchester, UK
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 :)
 
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 & "'"
 
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 :(
 
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
 
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.
 
Back
Top