SQL Statement error.

[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?

[glenngv]

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?

[NinjaTurtle]

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?

[glenngv]

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.

[whammy]

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!

[NinjaTurtle]

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")&"'"

[NinjaTurtle]

i found the solution already. use LIKE '%bbbbb%' in the SQL Statement

[glenngv]

you could have used instr like this:

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

[whammy]

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).

[Gozirra]

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.

[glenngv]

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.

[ReyN]

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

[whammy]

I didn't think to point that out - I use WHERE IN() all the time!