Dreamcatchers
Posts: 120
|
| Posted: 01/14/2015, 10:19 AM |
|
I have a query that works perfect in MS SQL Server Management Studio. So I placed it in the Before Select Event in a grid and it appears to be ignored. I get all records. There is no where selection in the Visual Query Builder.
Any suggestion as to what I am doing wrong?
Sample of SQL below. I have 4 conditions with nested parenthesis to group the logic.
*** Working SQL ***
SELECT [ID]
,[Group_ID]
,[Company_id]
,[SHG_id]
,[Dept_id]
,[Message]
,[Message_type]
,[author]
,[Date_added]
,[Expiration]
FROM [SMARTLMS].[dbo].[announcements]
WHERE Expiration > GETDATE()
AND ( [Group_ID] IS NULL OR ( [Group_ID] IS NOT NULL AND [Group_ID] = '4' ))
AND ( [Dept_id] IS NULL OR ( [Dept_id] IS NOT NULL AND [Dept_id] = '13' ))
AND ( [SHG_id] IS NULL OR ( [SHG_id] IS NOT NULL AND [SHG_id] = '46' ))
AND ( [Company_id] IS NULL OR ( [Company_id] IS NOT NULL AND [Company_id] = '7' ))
ORDER BY date_added desc
*** SQL in Before Select Event ***
Dim sql, connection, errormessage
Set Connection = New clsDBSmart
Connection.Open
'sql="UPDATE roster SET exam1passfail = 1, status=6, exam1date='"&exam1date&"' where rosterid="&rosterid
sql = "SELECT ID,Group_ID,Company_id,SHG_id,Dept_id,Message,Message_type,author,Date_added,Expiration FROM announcements WHERE ( Group_ID IS NULL OR ( Group_ID IS NOT NULL AND Group_ID = '4' ))"
Sql = sql + "AND ( Dept_id IS NULL OR ( Dept_id IS NOT NULL AND Dept_id = '13' ))"
Sql = sql + "AND ( SHG_id IS NULL OR ( SHG_id IS NOT NULL AND SHG_id = '46' ))"
Sql = sql + "AND ( Company_id IS NULL OR ( Company_id IS NOT NULL AND Company_id = '7' ))"
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
_________________
Training and LMS Developer |
 |
 |
eratech
Posts: 513
|
| Posted: 01/17/2015, 5:31 PM |
|
Quote :sql = "SELECT ID,Group_ID,Company_id,SHG_id,Dept_id,Message,Message_type,author,Date_added,Expiration FROM announcements WHERE ( Group_ID IS NULL OR ( Group_ID IS NOT NULL AND Group_ID = '4' ))"
Sql = sql + "AND ( Dept_id IS NULL OR ( Dept_id IS NOT NULL AND Dept_id = '13' ))"
Sql = sql + "AND ( SHG_id IS NULL OR ( SHG_id IS NOT NULL AND SHG_id = '46' ))"
Sql = sql + "AND ( Company_id IS NULL OR ( Company_id IS NOT NULL AND Company_id = '7' ))"
Connection.Execute(SQL)
Could be the string concatenation - I usually use '&' for ASP, not '+' (which is string concatenation in MS SQL though).
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
Dreamcatchers
Posts: 120
|
| Posted: 01/19/2015, 8:30 PM |
|
Thanks for the input. As usual, I figured out a much simpler query which required less (). my end results is listed below.
SELECT *
FROM announcements
WHERE ( Group_ID IS NULL
OR Group_ID = {Expr0} )
AND ( Company_id IS NULL
OR Company_id = {user_company_id} )
AND ( SHG_id IS NULL
OR SHG_id = {user_SHG_id} )
AND ( Dept_id IS NULL
OR Dept_id = {user_dept_id} )
AND Expiration > '{Expr1}'
ORDER BY Date_added
_________________
Training and LMS Developer |
|
|
|
eratech
Posts: 513
|
| Posted: 01/19/2015, 9:48 PM |
|
Agreed - much easier and cleaner.
E
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
|
