locked
parameterized query using SQL LIKE operator? RRS feed

  • Question

  • User-1057062 posted

    Hello,

         How do you do a parameterized query with a LIKE operator, this is what I have? Please help

    set command = Server.CreateObject("ADODB.Command")

    command.ActiveConnection= con command.Prepared = true command.CommandType = adCmdText

    if alpha = "A" then command.CommandText = "SELECT pid,crcod,crloc,coname FROM CRDCUST WHERE coname LIKE ? or isnumeric(substring(coname,1,1)) =1 ORDER BY coname"

    else command.CommandText = "SELECT pid,crcod,crloc,coname FROM CRDCUST WHERE coname LIKE ? ORDER BY coname"

    end if

    command.Parameters.Append command.CreateParameter("@coname",  adVarWChar, adParamInput, 250, alpha +"%")

    set rsSort = command.Execute

    Thursday, February 9, 2017 3:56 PM

All replies

  • User-460007017 posted

    Hi bryanmurtha,

    This link has provided the instruction to use like operator in parmeterized queries:

    https://weblogs.asp.net/morteza/using-quot-like-quot-operator-in-parameterized-queries

    Best Regards,

    Yuk Ding

    Friday, February 10, 2017 3:08 AM
  • User-1057062 posted

    Hi Yuk,

         That is for .NET I have a Classic ASP parameterized query. I've tried:

    cmd1.Parameters.Append cmd.CreateParameter("@CONAME",  adVarWChar, adParamInput, 35, strN+'%'))

    cmd1.Parameters.Append cmd.CreateParameter("@CONAME",  adVarWChar, adParamInput, 35, strN+"%"))

    cmd1.Parameters.Append cmd.CreateParameter("@CONAME",  adVarWChar, adParamInput, 35, strN&"%")

    Please help,

    Bryan

    Friday, February 10, 2017 8:37 PM
  • User-1057062 posted

    I gave up on the parameterized query and just changed it to a stored procedure. I figured an easy way around. However now my asp page times out. I ran SQL Server Profiler. It's executing the proc, but it just hangs. When I look in the IIS logs I get this:

     POST /creditdb/wiz_step2.asp |-|ASP_0113|Script_timed_out 80

    I just changed this to this:

    set rs = Server.CreateObject("ADODB.Recordset") rs.CursorLocation = 3  ' adUseClient rs.Open "select PID,CONAME FROM CONAMES WHERE CONAME LIKE '%"&strN&"%' ORDER BY CONAME", con

    'set cmd1 = Server.CreateObject("ADODB.Command") 'cmd1.ActiveConnection = con 'cmd1.CommandType = "adCmdStoredProc" 'cmd1.CommandText= "sp_CoNames" 'cmd1.Parameters.Append cmd.CreateParameter("@CONAME",  adVarWChar, adParamInput, 35, strN) 'set rs = cmd1.Execute

    I tried SSMS and logged in as the database user, the stored procedure worked instantly. I'm not sure what the problem is?

    Monday, February 13, 2017 9:13 PM
  • User-1057062 posted

    I thought I found it: I was missing cmd1 on the parameter but that wasn't it

    'set cmd1 = Server.CreateObject("ADODB.Command") 'cmd1.ActiveConnection = con 'cmd1.CommandType = "adCmdStoredProc" 'cmd1.CommandText= "sp_CoNames" 'cmd1.Parameters.Append cmd1.CreateParameter("@CONAME",  adVarWChar, adParamInput, 35, strN) 'set rs = cmd1.Execute

    Monday, February 13, 2017 9:20 PM
  • User-1057062 posted

    If I just feed the stored procedure directly it returns in seconds. If I use the parameter it times out?

    set rs = Server.CreateObject("ADODB.Recordset")
    rs.CursorLocation = 3  ' adUseClient
    rs.Open "Exec sp_CoNames "&strN&"", con

    Tuesday, February 14, 2017 8:34 PM
  • User-460007017 posted

    Hi bryanmurtha,

    It could be  a sample to use Like in classic asp:

    https://vikaskanani.wordpress.com/2012/05/07/classic-asp-sql-injection-prevention-by-using-query-parameter/

    Best Regards,

    Yuk Ding

    Thursday, February 16, 2017 9:15 AM