Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Robert_Ryan
Starting Member
1 Post |
 Posted - 2006-06-26 : 06:58:22
|
| Hello all,I'm not sure if this is the right place to post the question but hopefully I'll receive some help.Basically I have to create a webportal using ASP to access an Access database. I have a few queries working but unfortunately more that don't work.One in particular that is causing me grief is this:SQL_Query8 = "SELECT Count(Message.MsgName) AS [No of Mails Sent], [fromuser] & '@' & [fromdomain] AS [From], Message.Arrival as Arrival, Message.Subject as Subject FROM Message WHERE (((Message.Arrival) Between '"&startDate&"' And '"&endDate&"'))GROUP BY [fromuser] & '@' & [fromdomain], Message.Arrival, Message.Subject, Message.FromDomain, Message.FromUser HAVING (((Message.FromDomain) Like '*algoodbody*' And (Message.FromDomain) Not Like 'charon*' And (Message.FromDomain) Not Like 'marshal*') AND ((Message.FromUser) Not Like 'ithelpdesk' And (Message.FromUser) Not Like 'postmaster' And (Message.FromUser) Not Like 'mailmarshal')) ORDER BY [fromuser] & '@' & [fromdomain];"and the error I get is:Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid operator for data type. Operator equals boolean AND, type equals varchar./emailstats/allUsersSentMail.asp, line 196Line 196 is where the query is called from:rs5.Open SQL_query8,MyConnso the error must be in the query. The query runs fine within access so its just in the taking it out of access and putting it into ASP that something is going wrong. I have never used access before and just took the query from the SQL view, did a little bit of tidying up on it (there was a dbo_ before every tablename which caused hassle {anybody know what dbo is about?}) and also there were complaints about ' and " which I solved (hopefully properly).Anyway, any help/suggestions would be greatly appreciated,Thanks,Rob |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-26 : 07:45:48
|
| When using MS SQL Server in favor of MS Access, thou shall1. Use % instead of * as wildcard character2. Use + instead of & when concatenating stringsPeter LarssonHelsingborg, Sweden |
 |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
|
|
|
|
|
|
|
SQL and ASP