Web Develop Wolf Web Develop Wolf - 11 months ago 58
Vb.net Question

Conversion failed when converting the nvarchar value '%' to data type int

I have an Object Data Source which pulls information from the database where if we don'd have a value for this particular parameter then we want to get all records. So I have a LIKE statement and I'm using a wildcard to return all entries, but this is giving me the error message

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value '%' to data type int.

The table structure for the relevant column is:

[columnName] VARCHAR(50)

The SQL is something similar to:

SELECT [columns] from [table] where [column] LIKE @param

Then in VB I add the parameter:

Dim sessionParam As New Parameter
sessionParam.Name = "param"
sessionParam.DefaultValue = "%"
sessionParam.Type = TypeCode.String

So far I've tried casting the parameter value, casting the column, using dbType for the parameter instead of type, but nothing seems to work and I just get the same error. I suspect the column is reading as an int as this column is a mix of values so some are numbers, some are text, therefore SQL is making the 'educated guess' that that value needs to be an int

Answer Source

Instead of using LIKE if the parameter is NULL, try this instead.

SELECT [columns] from [table] where @param is null or [column] = @param

If you pass in a NULL parameter everything is returned. If it isn't null, then only where the column matches the parameter will be returned.