SQl Statement Hell

Results 1 to 3 of 3

Thread: SQl Statement Hell

  1. #1
    Join Date
    Dec 1969

    Default SQl Statement Hell

    I have a VERY complicated SQL statement involving several different tables, I have looked at it all day and everything seems to make sense as far as there is no typos in the table or column names. I am suspicious of the Date gathering because everything is being retured with the alert pop-up box "No Records Found Between xxxx and xxxx dates.<BR><BR>Here is how the date code works followed by the SQL statement from Hell:<BR><BR> &#039Mike O&#039Connor 05/04/2000 -- Gather Date this way for now --<BR> &#039 sReportDate = Date()<BR> sDateFrom = Request.Form("lstBeginMonth") & "/" & Request.Form("lstBeginDay") & "/" & Request.Form("lstBeginYear")<BR> sDateTo = Request.Form("lstEndMonth") & "/" & Request.Form("lstEndDay") & "/" & Request.Form("lstEndYear")<BR> if len(sDateFrom)=0 then<BR> sDateFrom=Date()<BR> end if<BR> if len(sDateTo) = 0 then<BR> sDateTo = date()<BR> end if<BR><BR>&#039 Top of report information including Names and Degree and Total Credits<BR><BR>&#039 First line selects the Institutions Table and so on till<BR>&#039 Line 3 adds Hours of credits together<BR>&#039 FROM is used to mean FROM those columns grab codes<BR><BR>sSQL= "SELECT tblInstitutions.InstitutionName, [LastName] & &#039, &#039 & [FirstName] & &#039 &#039 & [MiddleInitial] AS Attendee," _<BR>& "tblInstitutionMembers.Degree, tblInstitutionMembers.Specialty, tblInstitutionMembers.DepartmentName," _<BR>& "tblInstitutionMembers.IsPhysician, Sum(tblTrans460Data.HoursAttended) AS TotalCredits " _<BR>& "FROM (tblTrans460Data INNER JOIN tblInstitutionMembers ON (tblTrans460Data.CardNumber = tblInstitutionMembers.MemberID) "_<BR>& "AND (tblTrans460Data.ProviderID = tblInstitutionMembers.InstitutionCode)) INNER JOIN tblInstitutions ON " _<BR>& "tblInstitutionMembers.InstitutionCode = tblInstitutions.InstitutionCode " _<BR>& "WHERE (tblTrans460Data.ConferenceDate Between #" _<BR>& FormatDateTime(sDateFrom) & "# And #" & FormatDateTime(sDateTo) & "#) AND tblTrans460Data.LectureID&#060;&#062;&#039&#039 AND " _<BR>& "tblTrans460Data.ProviderID=&#039" & sInstitutionCode & "&#039 " _<BR>& "GROUP BY tblInstitutions.InstitutionName, [LastName] & &#039, &#039 & [FirstName] & &#039 &#039 & [MiddleInitial], tblInstitutionMembers.Degree, " _<BR>& "tblInstitutionMembers.Specialty, tblInstitutionMembers.DepartmentName, tblInstitutionMembers.IsPhysician, tblTrans460Data.ProviderID " _<BR>& "ORDER BY [LastName] & &#039, &#039 & [FirstName] & &#039 &#039 & [MiddleInitial];" <BR> rs.Open sSql, DbConn, 1, 3, 1<BR><BR> If rs.BOF And rs.EOF Then<BR> rs.Close<BR> DbConn.Close<BR> sTempStr = "No records exist for the date range: " & sDateFrom & " through " & sDateTo<BR>%&#062;<BR>&#060;script language="javascript"&#062;<BR> alert("&#060;% =sTempStr %&#062;");<BR> history.back();<BR>&#060;/script&#062;<BR><BR>Can I get help on this please?<BR>

  2. #2
    sm549 Guest

    Default Post SQL statement

    Suggestion: insert a Response.Write "&ltBR&gt" & sSQL before the Open() and post the SQL string constructed.<BR> <BR>It looks like some of the string appends are malformed because of missing double quotes but it&#039s hard to say.<BR><BR>Views expressed are not necessarily those of my employer.

  3. #3
    Join Date
    Dec 1969

    Default RE: Post SQL statement

    Thank you for reminding me yet again of the importance of Response.Write back the sSQL statement.<BR><BR>At first glance I still cannot see anything wrong with the sql statement other than it is not returning records that do exist for the query. I am definately not used to writing this heavy of T-SQL using INNER JOINS and such.. <BR><BR>Can anybody tell me what ON does?<BR><BR>I am sorry I do not have a book on T-SQL still because I have so many Wrox ASP books I did not think I would need one on T-SQL.. <BR>Should I get a book on T-SQL and any good recomendations on which title?<BR><BR>Thanks,<BR>Daniel<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts