Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

syntax error in select distinct Expand / Collapse
Author
Message
Posted Wednesday, October 08, 2008 10:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 17, 2009 1:31 PM
Points: 1, Visits: 14
Hi, I'm relatively new to sql server and asp, and am having an issue with a particular piece of code.
basically I'm getting a syntax error at line 498 of the code below, if someone could take a glance and tell me if they see the error that would be great, if I need to provide more info please mention it and I will submit a more descriptive post

SQLstmt=("SELECT DISTINCT Sale_Prodname.part_name, Sale_partnumber.*, SCompanies.Customer,SCompanies.customerID,Regions.Region, Regions.RegionID,"_
& " Territories.Territories, Territories.ID, Territories.Abbreviation, " _
& " E.First, E.Last, " & Table & ".*, SCompanies.Account, Sale_Prodname.Category, Distributor.Name AS DName, " _
& " " & SplitsTable & ".TicketID AS Spl_TicketID, " & SplitsTable & ".Percentage AS Spl_Percentage, ES.First AS Spl_First, ES.Last AS Spl_Last, OEMCustomers.OEMName " _
& " FROM Sale_Prodname INNER JOIN Sale_Partnumber ON Sale_Prodname.id = Sale_Partnumber.prodid"_
& " INNER JOIN " & Table & " ON Sale_Partnumber.ID = " & Table & ".ProductID"_
& " INNER JOIN Territories ON " & Table & ".TerritoryID = Territories.ID"_
& " INNER JOIN SCompanies ON SCompanies.CustomerID = " & Table & ".DivisionID"_
& " INNER JOIN Regions ON Regions.RegionID = Territories.RegionID "_
& " LEFT JOIN txhelpdesk.dbo.Engineers E ON E.EngineerID = " & Table & ".EngID "_
& " LEFT JOIN OEMCustomers ON OEMCustomers.OEMID = SCompanies.OEMID " _
& " LEFT JOIN Distributor ON Distributor.ID = " & Table & ".Distributor "_
& " LEFT JOIN " & SplitsTable & " ON DesignWin.TicketID = " & SplitsTable & ".TicketID " _
& " LEFT JOIN txhelpdesk.dbo.Engineers ES ON ES.EngineerID = " & SplitsTable & ".EngID " _
& " " & InnerJoinCond & " " & MyDate & " " & MyCondition & " " & MyStatus & " " & MyCustomer & " " & MyAccess & " " & RSM_Condition & " " & MyDistributor & MyOEMCustomer & MyTimeFrame & MyTier & MySptf & " ORDER BY " & Table & ".TicketID" )

'response.write Disposition & "

"
'response.write SQLStmt & "

"
'response.write AccessLevel & "

"

if Len(SavedSearch) > 0 then
set rs = conntemp.execute("SELECT SQLStmt, DateEntered FROM SavedSearch WHERE ID=" & SavedSearch)
SQLStmt = rs(0)
SavedDate = rs(1)
SQLStmt = Replace(SQLStmt, SavedDate, Date)
end if

session("SaveSQL") = SQLstmt
session("InnerJoinCond") = InnerJoinCond

set rstemp = conntemp.execute(SQLstmt) <--SYNTAX ERROR at ")"
'if EngineerID=154 then response.write SQLStmt

IF rstemp.eof then
'if viewfrom=1 then
response.write "no matches found"
response.end
'end if

Thanks in advance fro any help
Post #582768
Posted Wednesday, October 08, 2008 11:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, March 15, 2014 4:31 AM
Points: 2,808, Visits: 7,173
From a quick look it looks like this statement may be casuing you some problems
set rs = conntemp.execute("SELECT SQLStmt, DateEntered FROM SavedSearch WHERE ID=" & SavedSearch)

As you SQLstmnt already has the FROM Clause it will error when you try and combine it with another FROM clause.

Are you to provide the SQL statement that is sent to the sever? (with actual values and not variables in it)


Post #582793
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse