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