Access Database Query

Results 1 to 2 of 2

Thread: Access Database Query

  1. #1
    Join Date
    Dec 1969

    Default Access Database Query

    Hello All<BR>I hope that everyone is hacing a nice weekend.<BR>My question is this: I have created a query using access, I can run the query against the db in access directly adn get back the results that I expect. However, I have copied and pasted the query into an ASP page, it it will not run. I keep getting an unspecified error on the Execute(string) line. Is there anything special that I should be doing to run the query from ASP? Here is my query, this exact query will run and return the expected results in Access but not when calling it in ASP.<BR><BR>DIM StrSQL<BR>DIM oRSeu<BR>SET StrSQL=Server.CreateObject("ADODB.RecordSet")<BR>< BR>StrSQL = " SELECT UserInfo.User_PKey, Title.TitleName, UserInfo.FirstName, UserInfo.MiddleName, UserInfo.LastName, " &_<BR>"UserInfo.Sex, UserInfo.BirthDate, UserInfo.HomeAddress, UserInfo.HomeSuite, UserInfo.HomeCity, State.StateName, " &_<BR>"UserInfo.EmailAddress, State.StateName, Country.CountryName, UserInfo.HomeAreaCode, UserInfo.HomePhone, " &_<BR>"UserInfo.CellAreaCode, UserInfo.CellPhone, UserInfo.FaxAreaCode, UserInfo.Fax, UserInfo.WebsiteAddress, " &_<BR>"UserInfo.EmailAddress, UserInfo.AltEmailAddress,UserInfo.ReceiveEmail, UserInfo.Communique, UserInfo.Interview, " &_<BR>"UserInfo.Prayer, UserInfo.Friend, UserInfo.Leader, UserInfo.Sponser, UserInfo.Seminar, UserInfo.SeminarLocation, " &_<BR>"UserInfo.SeminarAttendance, UserInfo.DateCreated, UserInfo.DateModified,UserWorkInfo.CompanyName, " &_<BR>"UserWorkInfo.BusinessType, UserWorkInfo.Position, UserWorkInfo.WorkAddress, UserWorkInfo.WorkSuite, " &_<BR>"UserWorkInfo.WorkCity, WorkState.WorkStateName, WorkCountry.WorkCountryName, UserWorkInfo.WorkPostalCode, " &_<BR>"UserWorkInfo.WorkAreaCode, UserWorkInfo.WorkPhone, UserWorkInfo.WorkPhoneExt, Comment.Comment1, Comment.Comment2, " &_<BR>"Sponser.Donation, Sponser.Comment " &_<BR>"FROM WorkState " &_<BR>"INNER JOIN (WorkCountry " &_<BR>"INNER JOIN ((Country " &_<BR>"INNER JOIN ((State " &_<BR>"INNER JOIN (Title " &_<BR>"INNER JOIN (UserWorkInfo " &_<BR>"INNER JOIN UserInfo " &_<BR>"ON UserWorkInfo.User_PKey = UserInfo.User_PKey) " &_<BR>"ON (UserInfo.Title_PKey = Title.Title_PKey) " &_<BR>"AND (Title.Title_PKey = UserInfo.Title_PKey)) " &_<BR>"ON (UserInfo.State_PKey = State.State_PKey) " &_<BR>"AND (State.State_PKey = UserInfo.State_PKey)) " &_<BR>"INNER JOIN Sponser " &_<BR>"ON (UserInfo.User_PKey = Sponser.User_PKey) " &_<BR>"AND (UserInfo.User_PKey = Sponser.User_PKey)) " &_<BR>"ON (UserInfo.Country_PKey = Country.Country_PKey) " &_<BR>"AND (Country.Country_PKey = UserInfo.Country_PKey)) " &_<BR>"INNER JOIN Comment " &_<BR>"ON (UserInfo.User_PKey = Comment.User_PKey) " &_<BR>"AND (UserInfo.User_PKey = Comment.User_PKey)) " &_<BR>"ON (UserWorkInfo.WorkCountry_PKey = WorkCountry.WorkCountry_PKey) " &_<BR>"AND (WorkCountry.WorkCountry_PKey = UserWorkInfo.WorkCountry_PKey)) " &_<BR>"ON (UserWorkInfo.WorkState_PKey = WorkState.WorkState_PKey) " &_<BR>"AND (WorkState.WorkState_PKey = UserWorkInfo.WorkState_PKey) " &_<BR>"WHERE (((UserInfo.User_PKey)=" & RecordID & "));"<BR><BR>Set oRSeu = DBConn.Execute(StrSQL)<BR><BR>The error that the browser is returning is the line above, "DBConn.Execute(StrSQL)" I am simply trying to write the resultset to the screen. Please tell me why it is failing.<BR><BR>Please keep in mind that in my editor the code is formatted correctly, it may show differently here dur to width constraints. I realize that when using SQL query in ASP that you do not have to include the tablename.fieldname naming convention, but It will give the same error without the table names appended. Anyone have any idea what I am doing incorrectly here? Thank you in advnace for your assistance<BR><BR>Andrew<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Access Database Query

    now, there are a lot of joins there, and I&#039;m not 100% sure this is correct, but I think Jet has a little trouble with wide-ranging joins like this....<BR><BR>the ADO docs will know more...<BR><BR><BR>j<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