Please change the SQL to the one without JOIN

kaz · December 9th, 2003, 04:42 AM

Since ASP.NET doesn't support "JOIN" keyword, I have to change the following SQL to the one not using "JOIN". It's very difficult for me to re-express it. Could you suggest me the SQL?

Thank you,

SELECT Project.ProjectName, Project.BeginningTime, Project.EndingTime, Project.DateOfEvent, Location.LocationName, Location.LocationAddress, ProjectType.ProjectTypeName, Registration.RegistrationPwd, UserType.UserTypeName
FROM (((((Location INNER JOIN Project ON Location.LocationID = Project.LocationID) INNER JOIN ProjectType ON Project.ProjectTypeCode = ProjectType.ProjectTypeCode) INNER JOIN Registration ON Project.ProjectID = Registration.ProjectID) INNER JOIN UserProject ON Project.ProjectID = UserProject.ProjectID) INNER JOIN Users ON UserProject.UserID = Users.UserID) INNER JOIN UserType ON Registration.UserTypeCode = UserType.UserTypeCode;

Imar · December 9th, 2003, 05:31 AM

ASP.NET does not support the keyword JOIN? What are you talking about?
From an ASP.NET page it's perfectly legal to execute SQL statements that contain the keyword JOIN. After all, the database performs the JOIN, not ASP.NET.

What exactly are you trying to accomplish? If you show us some of your code, we may be able to recommend something for you that works OK.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

owain · December 9th, 2003, 05:34 AM

The JOIN keyword has nothing to do with ASP.NET, it is to do with the database engine you are using. If you are using (for example) SQL Server 6.0 then no matter what language you use you will not be able to use the JOIN keyword. On the other hand, if you are using a more recent version of SQL Server, Oracle, DB2, Access, etc. then you will be able to use the JOIN keyword (even in ASP.NET).

Anyway, that is not what you asked. If you do not want to use the new JOIN syntax, then simply place all the tables you are interested in after the FROM clause, then where you would say ON BlahBlah = BlahBlah (using the new JOIN syntax) simply move the expression to the WHERE clause, so it says WHERE BlahBlah = BlahBlah. It is as easy as that. Here is your query with the new JOIN syntax replaced with the old JOIN syntax.

Code:

SELECT Project.ProjectName,
       Project.BeginningTime,
       Project.EndingTime,
       Project.DateOfEvent,
       Location.LocationName,
       Location.LocationAddress,
       ProjectType.ProjectTypeName,
       Registration.RegistrationPwd,
       UserType.UserTypeName
  FROM Location,
       Project,
       ProjectType,
       Registration,
       UserProject,
       Users,
       UserType
 WHERE Location.LocationID = Project.LocationID
   AND Project.ProjectTypeCode = ProjectType.ProjectTypeCode
   AND Project.ProjectID = Registration.ProjectID
   AND Project.ProjectID = UserProject.ProjectID
   AND UserProject.UserID = Users.UserID
   AND Registration.UserTypeCode = UserType.UserTypeCode

And that is all there is to it ;).

Regards
Owain Williams

kaz · December 9th, 2003, 05:39 PM

Thank you for replying to me. I changed the SQL statement to the one without JOIN. Then, it became to work, but I have a further problem that it can't "DataBind." Could you explain what the problem is and what to do for it.

Thanks,

This is the error message that I got:

Server Error in '/MyProject' Application.
--------------------------------------------------------------------------------

DataBinder.Eval: 'System.Data.Common.DbDataRecord' does not contain a property with the name ProjectID.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: DataBinder.Eval: 'System.Data.Common.DbDataRecord' does not contain a property with the name ProjectID.

Source Error:

Line 69: myProjects.DataSource = objProjectList.ViewProjects(intUserID);
Line 70:
Line 71: myProjects.DataBind();Line 72: }
Line 73:

Source File: c:\myproject\viewmyprojects.aspx.cs Line: 71

Stack Trace:

[HttpException (0x80004005): DataBinder.Eval: 'System.Data.Common.DbDataRecord' does not contain a property with the name ProjectID.]
   System.Web.UI.DataBinder.GetPropertyValue(Object container, String propName)
   System.Web.UI.WebControls.DataGrid.CreateControlHi erarchy(Boolean useDataSource)
   System.Web.UI.WebControls.BaseDataList.OnDataBindi ng(EventArgs e)
   System.Web.UI.WebControls.BaseDataList.DataBind()
   MyProject.ViewMyProjects.BindGrid() in c:\myproject\viewmyprojects.aspx.cs:71
   MyProject.ViewMyProjects.Page_Load(Object sender, EventArgs e) in c:\myproject\viewmyprojects.aspx.cs:30
   System.Web.UI.Control.OnLoad(EventArgs e)
   System.Web.UI.Control.LoadRecursive()
   System.Web.UI.Page.ProcessRequestMain()

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

kaz · December 9th, 2003, 05:51 PM

Thank you for reading my new question. I realized that I didn't include the primary key in the SQL statement.

Thanks!