James Andrew Robins February 2016

Using wildcards with Parameters in vb.net ADO

I have the following code which works perfectly fine as part of my program:

.CommandText = .CommandText & " and [Name] = ?"

I then add the parameter using:

.Parameters.Append(SearchCom.CreateParameter("@Name", ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 100, Name.Text))

This works perfectly, but i really want to search for everything containing the value in Name.Text. I searched dozens of articles which say you can do the following, but it always returns 0 results even though there are definilty data matches:

.CommandText = .CommandText & " and [Name] like '%' + ? + '%'"
.Parameters.Append(SearchCom.CreateParameter("@Name", ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 100, Name.Text))

I have now tried the suggestion below, but still the recordset returns 0 results:

.CommandText = .CommandText & " and [Name] like ?"
.Parameters.Append(SearchCom.CreateParameter("@Name", ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 100, "%" + Name.Text + "%"))

I am using Microsoft Visual Studio 2015, writing in vb.net using ADODB to connect to a SQL Server 2014 instance.

A SQL Trace of CodingGorilla's suggestion reveals the following SQL runs at runtime. I do not know enough to understand why it is not working!

exec sp_executesql N'SELECT * FROM qFormsAndName Where [FormType] Like ''R1%'' and [Name] like @P1 ORDER BY [CurrentStatus], [DateReceived];',N'@P1 char(100)','%JONES%

Please can anyone help?

Answers


CodingGorilla February 2016

I would try a slight variation on that:

.CommandText = .CommandText & " and [Name] like ?"
.Parameters.Append(SearchCom.CreateParameter("@Name", ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 100, "%" + Name.Text + "%"))

This should work, although it's not a direct answer as to why your version of the code does not work.


James Andrew Robins February 2016

Answer as a comment to the original question, but for anyone who sees this, the text length should be the length of the parameter text, so: .Parameters.Append(SearchCom.CreateParameter("@Name", ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 100, tbName.Text))

to: .Parameters.Append(SearchCom.CreateParameter("@Name", ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, tbName.TextLength, tbName.Text))

or:

.Parameters.Append(SearchCom.CreateParameter("@Name", ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, len(tbName.Text), tbName.Text))


Damien_The_Unbeliever February 2016

Rather than customizing the parameter definition based on the input, it would be better to just switch to the more appropriate varchar data type, that specifies a variable length string, rather than char which specifies a fixed length string:

.CommandText = .CommandText & " and [Name] like ?"
.Parameters.Append(SearchCom.CreateParameter("@Name", ADODB.DataTypeEnum.adVarChar, _
    ADODB.ParameterDirectionEnum.adParamInput, 100, "%" + Name.Text + "%"))

(Also, if you really are, as you state in your question, using VB.Net, VS 2015 and SQL Server 2014, I'd seriously recommend a switch to using ADO.Net rather than old ADO, which dates back to about 2000)

Post Status

Asked in February 2016
Viewed 1,805 times
Voted 14
Answered 3 times

Search




Leave an answer