Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

wildcard in paramaterized querie

With this code:

                'set up search string
                strLike = "%" & txtSerNo.Text & "%"
               
                'check our file, current
                Set cm1 = New ADODB.Command
                cm1.ActiveConnection = c1
                cm1.CommandType = adCmdText
                cm1.CommandText = "SELECT ord_type [t] ,ord_no [n],line_no [l]FROM ast_GDSSN WHERE ssn_no_1 LIKE ? Or ssn_no_2 LIKE ? or ssn_no_3 LIKE ? or ssn_no_4 LIKE ?"
                cm1.Parameters.Append cm1.CreateParameter("SN1", adChar, adParamInput, 20, strLike)
                cm1.Parameters.Append cm1.CreateParameter("SN2", adChar, adParamInput, 20, strLike)
                cm1.Parameters.Append cm1.CreateParameter("SN3", adChar, adParamInput, 20, strLike)
                cm1.Parameters.Append cm1.CreateParameter("SN4", adChar, adParamInput, 20, strLike)
               
                Set rs1 = New ADODB.Recordset
                rs1.Open cm1.Execute


the % is being treated as a 1 character wildcard instead of a multiple character wildcard.  If we are searching for the string "234" this query returns any of these:

234, 1234, 2345

but not  01234 or 23456

If we add wildcard characters it picks up corresponding results, e.g.   strLike = "%%" & txtSerNo.Text & "%" will return 012345 but not 0123456

0
g_johnson
Asked:
g_johnson
  • 7
  • 6
1 Solution
 
Leigh PurvisDatabase DeveloperCommented:
What is your database?

Is ast_GDSSN a query?  But it has parameters in it?
What does it look like?
0
 
g_johnsonAuthor Commented:
MS SQL 2000

ast_GSSN is a table

ord_type char 1
ord_no char 8
line_no smallint
ssn_no_1 char 20
ssn_no_2 char 20
ssn_no_3 char 20
ssn_no_4 char 20
RowId identity
0
 
Leigh PurvisDatabase DeveloperCommented:
I'm not clear on what you're doing then...

What parameters are you evaluating?
SN1, SN2, SN3, SN4 ??

Where do they feature?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
g_johnsonAuthor Commented:
e.g., I am trying to return any record where ssn_no_1, ssn_no_2, ssn_no_3, OR ssn_no_4 contains a '234'

if any of those columns are populated with any of these choices (and others, of course) then it should return the record.

234,1234,01234,888888234888888,2341234,13242341111111, etc., etc., etc.

under normal circumstances   LIKE ('%234%') should work, but it doesn't from my program

thanks.
0
 
Leigh PurvisDatabase DeveloperCommented:
Sorry about not getting back to you sooner - a bit of time away has taken it's toll on keeping on top of things.
If it's still any use to you - do you want to try opening your recordset something like

    strLike = "'%" & txtSerNo.Text & "%'"
    cm1.CommandText = "SELECT ord_type [t] ,ord_no [n], line_no [l] FROM ast_GDSSN " & _
              "WHERE ssn_no_1 LIKE " & strLike & " OR ssn_no_2 LIKE " & strLike & _
              " OR ssn_no_3 LIKE " & strLike & " OR ssn_no_4 LIKE " & strLike
    Set rs1 = New ADODB.Recordset
    rs1.Open strLike, c1

See if that also doesn't work?
0
 
g_johnsonAuthor Commented:
still no good
i kept getting syntax errors, so tweaked it until cm1.commandtext looks like this:

SELECT ord_type [t] ,ord_no [n], line_no [l] FROM ast_GDSSN WHERE ssn_no_1 LIKE ('%210%') OR ssn_no_2 LIKE ('%210%') OR ssn_no_3 LIKE ('%210%') OR ssn_no_4 LIKE ('%210%')

but get "Incorrect syntax near '%'

this latest one works fine in QA, btw
0
 
Leigh PurvisDatabase DeveloperCommented:
Hmm I'm not sure exactly what's getting used - so let's be explicit

    strLike = "'%" & txtSerNo.Text & "%'"
    strLike = "SELECT ord_type [t] ,ord_no [n], line_no [l] FROM [ast_GDSSN] " & _
              "WHERE [ssn_no_1] LIKE " & strLike & " OR [ssn_no_2] LIKE " & strLike & _
              " OR [ssn_no_3] LIKE " & strLike & " OR [ssn_no_4] LIKE " & strLike
    Set rs1 = New ADODB.Recordset
    rs1.Open strLike, c1

Does that (and just that - i.e. no command object) execute?
What is the data source?
0
 
Leigh PurvisDatabase DeveloperCommented:
Ah - QA, Query Analyzer - so it's a SQL Server db.
:-)

See if the above runs?
0
 
g_johnsonAuthor Commented:
Finally got a chance to test, and yes this works.  But it doesn't address the question of why it doesn't work with parameters.
0
 
Leigh PurvisDatabase DeveloperCommented:
In what way - the example you had above?
That wasn't (as far as I could see) a proper use of ADO parameters - and I couldn't tell what you were trying to get it to do.

The above should allow you to provide a search string - and search on that (effectively providing a SQL parameter) but ADO parameters as exampled (SN1 etc) weren't in the SQL string - and so had nothing to evaluate.
0
 
g_johnsonAuthor Commented:
I finally got this figured out.  The problem was the size of the string being sent.  The size indicated in the parameter statement needs to be set as the size of the parameter string being sent (or larger).  I had it set to the size of the field in the table.
0
 
Leigh PurvisDatabase DeveloperCommented:
If you've managed to get your command text (or SP to which it points etc) sorted then however you're then passing parameters - if you're doing it as above then

cm1.Parameters.Append cm1.CreateParameter("SN1", adChar, adParamInput, 20, strLike)
will require the exact length.
But if you used  adVarChar  instead of  adChar  - then you'd have been better off as originally passing the field size in all likelihood?
0
 
g_johnsonAuthor Commented:
[But if you used  adVarChar  instead of  adChar  - then you'd have been better off as originally passing the field size in all likelihood?]

Yep, that makes sense, too.  If I'd realized that the parameter size was referring to source instead of target, this wouldn't have been a problem, I bet!  :)

0
 
CetusMODCommented:
Closed, 500 points refunded.
CetusMOD
Community Support Moderator
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now