Searchable DataGrid

[Guest]

I am trying to implement searchable DataGrid. I am creating a DataSet object with DataRelation on form load, as follows:

Private Sub BindDataGrid()
Dim planTS As New DataGridTableStyle
Dim appTS As New DataGridTableStyle

Try
'Call ExecuteDataset static method of SqlHelper class that returns a Dataset.
myDS = SqlHelper.ExecuteDataset(strConn, CommandType.StoredProcedure, "spSearchPlanDetails")

'Create relation.
myDS.Relations.Add("Applicants", myDS.Tables(0).Columns("Client_ID"), myDS.Tables(1).Columns("Client_ID"), False)

'Set mapping name for planTS.
planTS.MappingName = "Table"
planTS.AllowSorting = False

Dim myCOL As New DataGridTextBoxColumn
With myCOL
.MappingName = "Plan_ID"
.HeaderText = "Plan_ID"
End With
planTS.GridColumnStyles.Add(myCOL)

myCOL = New DataGridTextBoxColumn
With myCOL
.MappingName = "Client_ID"
.HeaderText = "Client ID"
End With
planTS.GridColumnStyles.Add(myCOL)

myCOL = New DataGridTextBoxColumn
With myCOL
.MappingName = "PlanDate"
.HeaderText = "Plan Date"
.Format = "dd/MM/yyyy"
End With
planTS.GridColumnStyles.Add(myCOL)

'Set mapping name for appTS.
appTS.MappingName = "Table1"
appTS.AllowSorting = False

myCOL = New DataGridTextBoxColumn
With myCOL
.MappingName = "Client_ID"
.HeaderText = "Client_ID"
.Width = 0
End With
appTS.GridColumnStyles.Add(myCOL)

myCOL = New DataGridTextBoxColumn
With myCOL
.MappingName = "FirstName"
.HeaderText = "First Name"
End With
appTS.GridColumnStyles.Add(myCOL)

myCOL = New DataGridTextBoxColumn
With myCOL
.MappingName = "LastName"
.HeaderText = "Last Name"
End With
appTS.GridColumnStyles.Add(myCOL)

'Add planTS to the DataGrid.
grdDetails.TableStyles.Add(planTS)

'Add appTS to the DataGrid.
grdDetails.TableStyles.Add(appTS)

Catch ex As SqlException
Throw ex
End Try
End Sub

Here’s the code for spSearchPlanDetails stored proc:

CREATE PROCEDURE spSearchPlanDetails
AS
SELECT Plan_ID, Client_ID, PlanDate
FROM Plans

SELECT P.Client_ID, A.FirstName, A.LastName
FROM Plans P, Applicants A
WHERE P.Client_ID = A.Client_ID
GO

Now, I have three search criteria’s on my Form, Client_ID, FirstName and LastName. On click on Search button I am trying to show data in the DataGrid which matches the search criteria as follows:


Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
myDS.Tables(0).DefaultView.RowFilter = "Client_ID = " & txtClientID.Text & " AND FirstName LIKE '" & txtFirstName.Text & "%'" & " AND LastName LIKE '" & txtLastName.Text & "%'"

'Bind DataGrid.
grdDetails.DataSource = myDS.Tables(0).DefaultView
End Sub

But, I am getting an error saying "Cannot find column [FirstName]". Does anyone knows what it could be?

Thanx

 

[Cor Ligthert]

Hi Job,

It is not the most easiest one, when everything is working, than I ask
myself why is he using in the select datatable(0) while the information is
as I suspect in datatable(1).

However a real gues

Cor

 

[Guest]

A Client can have more than one applicants. Applicants have firstname and lastname. I want to show Parent/Child relationship in my DataGrid. Table(0) holds info about parent and Table(1) holds info abt child rows.

 

[Cor Ligthert]

Hi Job,

What is the question, how you can do your application or how it is possible
that you have an error?

On the first question I have no other answer than start with a more simple
solution. An answer on this goes far to far for a newsgroup in my opinion
seeing the problems/solution you have now. On the other one I thought I have
given you an answer, I assume that the items are in table1 and not in table
0 that you try to select based on the items.

Sorry, I can not think about an other answer that I can give you.

Cor

A Client can have more than one applicants. Applicants have firstname and

lastname. I want to show Parent/Child relationship in my DataGrid. Table(0)
holds info about parent and Table(1) holds info abt child rows.