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 |
 |
 |
Oper
Posts: 1195
|
| Posted: 01/15/2015, 6:41 AM |
|
do this
just to make sure is going that way
.
.
.
.
.
set .... = nothing
response.write "I'm Here"
response.end
you may see an error
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Oper
Posts: 1195
|
| Posted: 01/15/2015, 6:45 AM |
|
wait
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
its just a SELECT how do you know is not Working?
4th line has a ' at the begining
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Dreamcatchers
Posts: 120
|
| Posted: 01/15/2015, 10:54 AM |
|
I got it figured out.
When I say it was not working, I experienced that all records were being selected regardless of the data changes.
I came up with a simpler query and used Brackets () to group them. That did the trick.
_________________
Training and LMS Developer |
|
|
|
|
