Tableadaptor, fillby and wildcards

[tonywilliams]

Hi

I am trying to do find particular records from my database using a Tableadaptor and Fillby to set the parameter for the query. It all works fine and returns the correct records, however I cant seem to get a wildcard to work. I have tried both '*' and '%' but it just returns no records.

Any ideas.

Regards

 

[Arg81]

SELECT * FROM Customer WHERE CustomerName LIKE 'A%' would return all customers who start with the letter A.

There may be a proper way in SQL to do the following, but I never found it

THIS IS BASED ON A CUSTOMER TABLE - YOU NEED TO CHANGE ACCORDINGLY.

On a form have a grid, a textbox and a button. Create a TableAdapter to your Customer table and create a FillByCustomerName query that uses the parameter @CustomerName.

On the _Click event of the button, set the parameter and fill the necessary data.

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button1_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button1.Click[/SIZE]
 
[SIZE=2]Dim varCustName As String = me.textbox1.text & "%"[/SIZE]
 
[SIZE=2]me.CustomerTableAdapter.FillByCustomerName(me.dataset1.customer, varCustName)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]

The code above will take whatever is in the textbox, add a % to the end of it, and use this as the @CustomerName parameter in the SQL query.

So...if you type A in the textbox and press the button, the grid will load all customers who begin with the letter A, as the SQL parameter would be A%

HTH

 

[cjard]

Hi

I am trying to do find particular records from my database using a Tableadaptor and Fillby to set the parameter for the query. It all works fine and returns the correct records, however I cant seem to get a wildcard to work. I have tried both '*' and '%' but it just returns no records.

Any ideas.

Regards

Just put the wildcard into the parameter, but do bear in mind that you must use the LIKE operator, not the = operator.

Your tableadapter must have a FillBY query of:

SELECT * FROM table1 WHERE col1 LIKE :col1



Run it with:

table1TableAdapter.FillBy(myDataset.Table1, "smith%")



if your select query uses = instead of LIKE, the wildcard will never work

 

[tonywilliams]

Thanks all, it was a moment of madness, and tiredness... couldnt see the forest for the trees...

I was not using LIKE, but "=". Changed to LIKE all works like a dream..