search criteria builder

[chowchow123]

Hi

I trying to formulate a search query using fillby - the query executes fine but I get 'Schema returned by the new query differs from the base query' - does this mean I need to create a view of the fields which is part of a larger table before I can formulate the query?

Thanks

 

[jmcilhinney]

A TableAdapter is always paired with a DataTable. That means that every query in the TableAdapter has to return the same columns because the results are going to go into the same DataTable. If you want result sets with different schema then you have to use different TableAdpters/DataTables. Note that if some queries don't need to return all columns then you can do something like this:

SELECT Column1, Column2, Column3 FROM MyTable

SELECT Column1, Null AS Column2, Column3 FROM MyTable WHERE Column4 = @Column4

That second query will now return a result set with the same schema as the first but without returning any values for Column2.

 

[chowchow123]

Hi

Thanks for the explanation - I tried the query below and I got the same message 'Schema returned by the new query differs from the base query'. The reason why I don't want to create another table is because the search box actually needs to search from the main table but do the search based on 5 fields mem_id, name, surname, DOB, street and postcode

SELECT mem_id, null as title, forename, surname, null as gender, DOB, null as age, street, postcode, null as city, null as tel, null as mobile, null as [e-mail], null as mem_type, null as current_date_joined, null as expiry_date, null as total_visits, null as last_visit, null as next_of_kin, null as profession, null as relationship, null as k_street, null as k_city, null as k_postcode, null as [k_tel/mobile], null as [doctor's_name], null as surgery_name, null as d_street, null as d_city, null as d_postcode, null as d_phone
FROM trial_membership
where (mem_id=@mem_id) AND (forename=@forename) AND (surname=@surname) AND (DOB=@DOB)
AND (street=@street) AND (postcode=@postcode)

 

[cjard]

Hi

Thanks for the explanation - I tried the query below and I got the same message 'Schema returned by the new query differs from the base query'. The reason why I don't want to create another table is because the search box actually needs to search from the main table but do the search based on 5 fields mem_id, name, surname, DOB, street and postcode

Youre probably making a select that returns columns with a different name or type.
If youre looking to create a fillby that takes 5 arguments and returns all matching records, then just create it the same way you made the original query:


SELECT *
FROM trial_membership
where (mem_id=@mem_id) AND (forename=@forename) AND (surname=@surname) AND (DOB=@DOB)
AND (street=@street) AND (postcode=@postcode)

Dont want to use or show the postcode? Then dont show it or use it, but dont bother trying to make it null at time of selection, just simply DONT SHOW IT in your UI

 

[chowchow123]

Thanks for that cjard.

Is there anyway I can get the colum headers in a combobox and the matching data to search in a text box rather than a toolbarfillby which the query creates


Cheers

 

[jmcilhinney]

A DataTable has a Columns collection containing DataColumns. Each DataColumn has a ColumnName property. Loop through the collection and get the name of each column, then put them in a ComboBox.

 

[cjard]

Thanks for that cjard.

Is there anyway I can get the colum headers in a combobox and the matching data to search in a text box rather than a toolbarfillby which the query creates


Cheers

I dont quite understand the question sorry. Can you fake up a screenshot or 2 of how you want your search form to look/behave when the user is using it?

 

[chowchow123]

I have a datagrid and a fillby toolbar in a dialogbox - how can I open a record in a form selecting one of the record in the dialogbox?

 

[cjard]

Retrieve the ID typed by the user, into the dialogbox and ues it to fill the datatable bound to the grid

 

[chowchow123]

thanks for that

how do I retrieve the id?

 

[cjard]

Huh? The user jsut typed it into your dialog box? I dont know how your dialog box works!

If it were an OpenFileDialog i'd retrieve it like this:

Dim filename as String myOpenFileDialog.Path

Or whatever. Only you can know how your dialog box works!