asked on

Table Adapter Confiuration - like '%Param%'

Hi, I am trying to use LIKE in "Enter a SQL Statement" of the configuration of Table Adapter in .NET

Fors some reason it does not like '%@Param%' The @Param is not recognized as parameter but as tekst to search using Like function. Well, I understand that but I tried to cheat a bit like in the following example:

SELECT * FROM Customers WHERE (Name_1 LIKE '%@Param%')
or
in VB code: 
 Me.CustomersTableAdapter.Fill(Me.Ds_customers.Customers, "'%" & Me.txtSearch.Text & "%'")
and in the Table Adapter:
SELECT * FROM Customers WHERE (Name_1 LIKE @Param)

Open in new window

But it does not work either..

Can anyone help please? What do I missed.??
Thanks

pateljitu

Sql would look like this:

SELECT * FROM Customers WHERE (Name_1 LIKE '%'+ @Param +'%')

dj_alik

see answer:
http://stackoverflow.com/questions/2889100/wildcards-in-t-sql-like-vs-asp-net-parameters

rkanabus

ASKER

Hi, Thank you for your answer, it was very helpful. Unfortunately it does not solve my problem.

The issue is that when I check the query from the Table Adapter configuration it works just fine. I can specify my parameter and execute the query, it returns correct number of records.

But when I do it from application form, it does not return anything unless the key I look for is exact match, then it returns values.

for some reason the operator like does not work properly when run from application form.

As you can see from above, there is a strange behavior, for some reason I cannot use like when building the query.. however it works fine on Table Adapter level.

Here is the code:

Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click

        If Not IsNothing(Me.Ds_customers.Customers) Then
            Me.Ds_customers.Customers.Clear()

        End If
        Me.CustomersTableAdapter.Fill(Me.Ds_customers.Customers, Me.txtSearch.Text)
        Me.CustomersBindingSource.DataSource = Me.Ds_customers.Customers

        log.rtbLog.Text = Me.Ds_customers.GetXml
        log.Show()
    End Sub

Open in new window

I will be very grateful for your help.

pateljitu

would you be able to run profiler and see whats SQL query gets executed when you click on "Search" button from your application and param as "nic"

rkanabus

ASKER

Good idea,

here you go,

This works fine (exact match):

exec sp_executesql N'SELECT 
ID_Customer, Creation_date, Name_1, Name_2, Name_3, Name_4, Title, Salutation, [Type of business], [Address 1], [Address 2], [Address 3], [Address 4], 
[Address 5], Post_Code, Contact_1, Contact_2, Contact_3, Contact_4, Contact_5, Contact_6, [Sales Ledger Account], ID_Number, TAX_Number, 
Exclude_from_reporting, Note, idCustomer, VAT_Excempt
FROM Customers
WHERE        (Name_1 LIKE ''%'' + @Param + ''%'') OR
(Name_2 LIKE ''%'' + @Param + ''%'') OR
(Name_3 LIKE ''%'' + @Param + ''%'') OR
(Name_4 LIKE ''%'' + @Param + ''%'') OR
([Address 1] LIKE ''%'' + @Param + ''%'') OR
([Address 2] LIKE ''%'' + @Param + ''%'') OR
([Address 3] LIKE ''%'' + @Param + ''%'') OR
([Address 4] LIKE ''%'' + @Param + ''%'') OR
([Address 5] LIKE ''%'' + @Param + ''%'') OR
(Post_Code LIKE ''%'' + @Param + ''%'') OR
(Contact_1 LIKE ''%'' + @Param + ''%'') OR
(Contact_2 LIKE ''%'' + @Param + ''%'') OR
(Contact_3 LIKE ''%'' + @Param + ''%'') OR
(Contact_4 LIKE ''%'' + @Param + ''%'') OR
(Contact_5 LIKE ''%'' + @Param + ''%'') OR
(Contact_6 LIKE ''%'' + @Param + ''%'') OR
([Sales Ledger Account] LIKE ''%'' + @Param + ''%'') OR
(ID_Number LIKE ''%'' + @Param + ''%'') OR
(TAX_Number LIKE ''%'' + @Param + ''%'')',N'@Param nchar(50)',@Param=N'nicosia'

Open in new window

And this one that does not return anything:

exec sp_executesql N'SELECT
ID_Customer, Creation_date, Name_1, Name_2, Name_3, Name_4, Title, Salutation, [Type of business], [Address 1], [Address 2], [Address 3], [Address 4], 
[Address 5], Post_Code, Contact_1, Contact_2, Contact_3, Contact_4, Contact_5, Contact_6, [Sales Ledger Account], ID_Number, TAX_Number, 
Exclude_from_reporting, Note, idCustomer, VAT_Excempt
FROM Customers
WHERE (Name_1 LIKE ''%'' + @Param + ''%'') OR
(Name_2 LIKE ''%'' + @Param + ''%'') OR
(Name_3 LIKE ''%'' + @Param + ''%'') OR
(Name_4 LIKE ''%'' + @Param + ''%'') OR
([Address 1] LIKE ''%'' + @Param + ''%'') OR
([Address 2] LIKE ''%'' + @Param + ''%'') OR
([Address 3] LIKE ''%'' + @Param + ''%'') OR
([Address 4] LIKE ''%'' + @Param + ''%'') OR
([Address 5] LIKE ''%'' + @Param + ''%'') OR
(Post_Code LIKE ''%'' + @Param + ''%'') OR
(Contact_1 LIKE ''%'' + @Param + ''%'') OR
(Contact_2 LIKE ''%'' + @Param + ''%'') OR
(Contact_3 LIKE ''%'' + @Param + ''%'') OR
(Contact_4 LIKE ''%'' + @Param + ''%'') OR
(Contact_5 LIKE ''%'' + @Param + ''%'') OR
(Contact_6 LIKE ''%'' + @Param + ''%'') OR
([Sales Ledger Account] LIKE ''%'' + @Param + ''%'') OR
(ID_Number LIKE ''%'' + @Param + ''%'') OR
(TAX_Number LIKE ''%'' + @Param + ''%'')',N'@Param nchar(50)',@Param=N'nic'

Open in new window

Any idea???

ASKER CERTIFIED SOLUTION

pateljitu

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer