Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts

    SQL Statement error.

    dear,

    strSQL = "SELECT * FROM TaskMaster where month(FTASKASSIGNON)='"&navmonth&"' AND Instr(FTASKASSIGNTO,',')='"&Session("LHHBUSERID")&"'"

    and i get error:
    Microsoft OLE DB Provider for SQL Server error '80040e14'

    'Instr' is not a recognized function name.


    what happen to this SQL Statement?
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #2
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    10,950
    Thanks
    0
    Thanked 236 Times in 233 Posts

    Re: SQL Statement error.

    i'm still here NinjaTurtle!

    I have used Instr in an SQL statement but the db is Access, not sure if it's working in SQL server.

    But Instr returns an integer (just like in vbscript) not a string as what you did.



    Originally posted by NinjaTurtle
    dear,

    strSQL = "SELECT * FROM TaskMaster where month(FTASKASSIGNON)='"&navmonth&"' AND Instr(FTASKASSIGNTO,',')='"&Session("LHHBUSERID")&"'"

    and i get error:
    Microsoft OLE DB Provider for SQL Server error '80040e14'

    'Instr' is not a recognized function name.


    what happen to this SQL Statement?

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts

    hi, glenngv

    dear glenngv,

    1st i hav to say thanks to u, i learn ASP just 2 months... so some stupid questions may post regular.... any way thanks thanks and thanks...

    just now the problem i solve then next problem is happend... hahhahaha... i will post the code to the particular question...
    but it is bcos u r the best tutor... hihiihihi



    get back to this question is :
    i just want to retrieve the event description from the table that which people r involved in the event. Yap, i'm using SQL SERVER... how? any idea 4 that?
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #4
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    10,950
    Thanks
    0
    Thanked 236 Times in 233 Posts

    Re: hi, glenngv

    you can first try executing the SQL statement with sample values in the Query Analyzer to see if any record is retrieved. This will help you ensure that the SQL statement has no syntax error before putting in the ASP page.

  • #5
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yeah, Query Analyzer will let you know whether or not your query will work.

    If you're still running into problems, Kockwhie () post your ASP script I'm sure someone can help!
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    dear,

    i know the Query analyzer is helpful, yes but i try a lot of sql statement already on it... it still not working.... bcos dunno how to retrieve it with not using Instr.

    ok, in the database a field called "User_Involved"... it store all the users name who involved in the event, and all the records(User name) is join togather and separate out with ","(commas)
    Example :
    record 1 : event 1 | Johnny,Allan,Tom
    record 2 : event 2 | Tom
    record 3 : event 3 | Allan,Alice,Patrick ....

    what i want is when user login to my system
    i will show the event who is involved in all the event, example i login as "Tom", i will get a record display like this:

    Welcome, Tom
    Event 1 : involved
    Event 2 : involved
    Event 3 : NOT involved

    my SQL Statement is look like :
    strSQL = "SELECT * FROM Event where Instr(USER_INVOLVED,',')='"&Session("USERID")&"'"
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #7
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    i found the solution already. use LIKE '%bbbbb%' in the SQL Statement
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #8
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    10,950
    Thanks
    0
    Thanked 236 Times in 233 Posts
    you could have used instr like this:

    strSQL = "SELECT * FROM Event where Instr(USER_INVOLVED,'"&Session("USERID")&"')>0"

  • #9
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Not to mention if you run a query such as:

    LIKE '%bbbbb%'

    on a very large database (on a machine that may not be top of the line), it can take forever (and in some cases will result in a Server Timeout).

    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #10
    New to the CF scene
    Join Date
    Jun 2002
    Location
    Illinois
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Take it from me.

    Here it is. In SQL there is no instr that I know of and I have been administering a database for the last 2.5 years. What you can do is use the substring function. substring(string, start position, end position). In order to retrieve the end position you are looking for, try patindex('%,%', string). This will return the location of the first occurrence of a comma within the string.

    Peace.

  • #11
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    10,950
    Thanks
    0
    Thanked 236 Times in 233 Posts

    Re: Take it from me.

    So it's an MS Access function only.
    I've used it in my ASP page accessing Access db:

    sSQL = "SELECT SUM(datediff('n',#08:30 AM#,[timein])) as minlate FROM tblAttendance WHERE datediff('n',#08:30 AM#,[timein])>10 AND datediff('n',#08:30 AM#,[timein])<60 AND (" & monthClause & ") and (instr([otpurpose],'not late')=0 or [otpurpose] is null) AND weekday([attdate])=" & d & " AND instr([otpurpose],'half-day')=0"

    Originally posted by Gozirra
    Here it is. In SQL there is no instr that I know of and I have been administering a database for the last 2.5 years. What you can do is use the substring function. substring(string, start position, end position). In order to retrieve the end position you are looking for, try patindex('%,%', string). This will return the location of the first occurrence of a comma within the string.

    Peace.

  • #12
    New Coder
    Join Date
    Jun 2002
    Location
    Pilipinas
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can also use the WHERE . . . IN clause, which works for both SQL Server and Access.

    When the WHERE filter condition includes IN, the field must contain one of the values in a given set of values before the record is included in the query results.

    For example, to select only the records related to Camping products, you can set a filter condition in which the ProductType can be found IN a comma-separated list of values:

    strSQL="SELECT . . . WHERE ProductType IN ('Backpack', 'SleepingBag', 'Supplies', 'Tent')"

    You can also use the NOT operator to reverse the outcome of the expression. Here, the field must NOT contain any of the values found IN the set before its record is included in the query results.

    strSQL="SELECT . . . WHERE Customer.PostalCode NOT IN ('98052', '98072', '98034')

    Using the SQL WHERE . . . IN Clause

  • #13
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I didn't think to point that out - I use WHERE IN() all the time!
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •