Operator LIKE does not work in my app vb.net

Navigation
1

Greetings to all. I have the following problem I have a procedure stored in SQL Server that retrieves data with the like operator, but it does not work for me. 

create procedure recupera_art 
@cadena char(40) 
select * from articulo where clave like '%'+ @cadena +'%'

change the procedure like this: 

create procedure recupera_art 
    @cadena char(40) 
    select * from articulo where clave like @cadena

and from my application (vb.net) I pass the parameter in this way: 

cmd.Parameters.AddWithValue("@cadena", "%" + dts.gclave_art + "%")

but it works to me half, that is to say I have the following keys (AX101080, ..., AX101095), when wanting to make the query I do not do anything when putting A, AX, AX1 or AX10, I have to put AX101 so that I started to filter all the data in the table.

What I want is that by putting AX, A, AX1 or AX10 I can filter the data, I think that is possible.

I appreciate your help to solve the problem.

    
asked by Silvestre Silva 16.01.2018 в 22:08
source

2 answers

2

Thank you all for your cooperation, I leave the solution in case someone happens. 

create procedure recupera_art 
@cadena varchar(40) 
select * from articulo where clave like '%'+ @cadena +'%'

In the application. 

cmd.Parameters.AddWithValue("@cadena", dts.gclave_art)

The explanation of the problem is that a char(40) physically uses the whole space, this can be verified with this example: 

DECLARE @cadena CHAR(40)
SELECT @cadena  = 'BUSCA'
PRINT   '*' + @cadena + '*'

The exit: 

*BUSCA                                   *

When doing like '%'+ @cadena +'%' when @cadena is a CHAR we are actually doing: 

like '%BUSCA                                   %'

Provoking that we can not find the desired information.

    
answered by 17.01.2018 / 19:05
source
0

If your procedure works correctly in SQL you could try to execute it as if it were t-sql 

cmd.CommandText = "EXEC recupera_art 'texto para buscar'"
cmd.CommandType = CommandType.Text

Similarly, if you already have '%' in your procedure, you do not need to add it in your vb code, I would also recommend that you change that char (40) to varchar (max)

    
answered by 17.01.2018 в 18:55
Execute function when another is completed Group by month by adding a MYSQL field