Using the Query Builder to Make a Search Form

[chrisguk]

Hi,


I have been trying to create a filter search as instructed by many Youtube tutorials but I am unable to get my code to work:


This is what I have. I created a query in the Query Builder like so:

SELECT        SitesID, SiteCode, SiteName, fkStatoID, fkSimInstalledID, fkMeterInstalledID, fkPowerMaster, fkProgramActive
FROM            Sites
WHERE        (SiteCode LIKE '@SiteName' + '%')

Then I click ok but it only generates a Find button and no text box. The code that is generated is as follows:

Private Sub FillBySearchSNToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillBySearchSNToolStripButton.Click
        Try
            Me.SitesTableAdapter.FillBySearchSN(Me._EMIBE_mdbDataSet.Sites)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try


    End Sub

From the tutorials I notice that an element is missing like below:

Me.SitesTableAdapter.FillBySearchSN(Me._EMIBE_mdbDataSet.Sites, textbox1.text)

So I went back to basics and re-created the Query in Query Builder, this time I executed the query in the QB, but all of my columns are displayed as NULL when I know I have data in the tables.


I am using Visual Basic Express 2010.


Does anyone have any ideas?

 

[cjard]

if you put the parameters name inside string delimiters (apostrophe) it becomes a literal string, not a parameter - if you didnt get what I mean, open your db, and literally change one of your site names so it is the exact text: "@SiteNameBLAHBLAH" and suddenly your query will return that row

Make your query:
SELECT SitesID, SiteCode, SiteName, fkStatoID, fkSimInstalledID, fkMeterInstalledID, fkPowerMaster, fkProgramActive
FROM Sites
WHERE (SiteCode LIKE @SiteName)

do NOT put the % in the query, put it in the parameter value. This gives you more control over where the wildcarding is done

i.e. add a new query to a relevant tableadapter, using the above command text

Your tableadapter will now have:

'exact search (no percent.. like also works as equals (=)
Dim dt as MyDataSetName.Sites = mySitesTableAdapter.GetDataBySiteName("birmingham")

'wildcard search
Dim dt as MyDataSetName.Sites = mySitesTableAdapter.GetDataBySiteName("lon%")

'retrieve all rows
Dim dt as MyDataSetName.Sites = mySitesTableAdapter.GetDataBySiteName("%")

'all rows ending in ham
Dim dt as MyDataSetName.Sites = mySitesTableAdapter.GetDataBySiteName("%ham")

'all rows starting with a and ending in ville
Dim dt as MyDataSetName.Sites = mySitesTableAdapter.GetDataBySiteName("a%ville")

'all rows starting d and containing ing
Dim dt as MyDataSetName.Sites = mySitesTableAdapter.GetDataBySiteName("d%ing%")

See how much more flexible your query got by not forcing the % at the end in the sql?

Notes:
when i say GetDataBySiteName i mean whatever you called your query in the TA wizard
GetDataByXX is the general name for method that returns a new datatable. if you want to fill an existing, use fillbyxxx and pass the dt to be filled

 

[chrisguk]

Hi,

I tried that and get:

Error in WHERE clause near '@'.
Unable to parse query text.

 

[cjard]

if you're using access db, use a ? instead of @SiteName