syntax error in select distinct

  • 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

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply