Welcome !
         

Problem with instr in a select statement acessing a MySQL DB

Author Message
witgrefe

  • Total Posts : 2
  • Scores: 0
  • Reward points : 0
  • Joined: 12/3/2014
  • Status: offline
Problem with instr in a select statement acessing a MySQL DB Wednesday, December 03, 2014 1:52 AM (permalink)
0
[Helpful answer received] / [List Solutions Only]
Hello,
The second argument of instr(description, kwStr) is not being correctly interpreted.  the str is dimensioned as such and it does have a value.

When I use the following code:
rsOpenStr = "select * from inventory where instr(description," &  kwStr &")>0"
rs.Open (rsOpenStr) 
 
   
If I enter a string in the form's text box -- birds for example, I get the following error:
Unknown column 'birds' in 'where clause'.  
If I directly enter 'birds' in the instr function the select statement executes.

Can anyone help please?
 
#1
    59cobalt

    • Total Posts : 2632
    • Scores: 253
    • Reward points : 0
    • Joined: 7/17/2011
    • Status: offline
    Re:Problem with instr in a select statement acessing a MySQL DB Wednesday, December 03, 2014 6:40 AM (permalink)
    0
    [This post was marked as helpful]
    The value of the variable is inserted into the query, so you need to put it between quotes to mark it as a string literal. Replace this:
    rsOpenStr = "select * from inventory where instr(description," &  kwStr &")>0" 
    with this:
    rsOpenStr = "select * from inventory where instr(description,'" &  kwStr &"')>0" 

     
    #2
      witgrefe

      • Total Posts : 2
      • Scores: 0
      • Reward points : 0
      • Joined: 12/3/2014
      • Status: offline
      Re:Problem with instr in a select statement acessing a MySQL DB Wednesday, December 03, 2014 7:27 PM (permalink)
      0
      [This post was marked as helpful]
      Perfect, thank you
       
      #3
        Online Bookmarks Sharing: Share/Bookmark

        Jump to:

        Current active users

        There are 0 members and 1 guests.

        Icon Legend and Permission

        • New Messages
        • No New Messages
        • Hot Topic w/ New Messages
        • Hot Topic w/o New Messages
        • Locked w/ New Messages
        • Locked w/o New Messages
        • Read Message
        • Post New Thread
        • Reply to message
        • Post New Poll
        • Submit Vote
        • Post reward post
        • Delete my own posts
        • Delete my own threads
        • Rate post

        2000-2017 ASPPlayground.NET Forum Version 3.9