Form and SQL Query 1

[Corneliu]

I have an Update Page basically to update the record with new data, etc.
The drop down list shows the Selected information from within the table, and the options from another table. The main table with the data to be updated has the information as a #ID from the second table. Basically a Join Query.
What I want to do is to list the SELECTED OPTION as the Name instead of the ID#. When you look at the Drop Down, it will first show the ID#, and if you drop down the list, it will show the names.
My question is how can I make it so that it will not show the Records ID#, but the name itself. Here is my Query and Form Info:

Query:
<%

recnum = Request.QueryString(&quot;recnum&quot

recnum = TRIM(recnum)
sqlString2 = &quot;SELECT Projects.ProjID, Projects.ProjName, Projects.ProjDate, Projects.Department, Projects.Tech, Projects.TechC, Projects.Hours, Projects.Site, Projects.Week, Projects.ProjCount, Projects.Descript, Projects.Severity&quot;
sqlString2 = sqlString2 & &quot; FROM ((Projects INNER JOIN Technicians ON Projects.Tech = Technicians.TechID) INNER JOIN Department ON Projects.Department = Department.DepartmentID)&quot; &_
&quot;INNER JOIN Site ON Projects.Site = Site.SiteID&quot;
sqlString2 = sqlString2 & &quot; WHERE Projects.ProjID =&quot; & recnum

SET RS2 = objConn.Execute( sqlString2 )

%>



Form:
<select name=&quot;Department&quot; size=&quot;1&quot; class=&quot;inputBox&quot; id=&quot;Department&quot;>
<option selected value=&quot;<%=RS2(&quot;Department&quot%>&quot;><%=RS2(&quot;Department&quot%></option>
<% WHILE NOT RS5.EOF %>
<option value=&quot;<%=RS5(&quot;DepartmentID&quot%>&quot;><%=RS5(&quot;Department&quot%></option>
<%
RS5.MoveNext
WEND
%>
</select>

So that the query can run faster and easier, I made it so that it will JOIN the ID instead of the names, which it will take a long time to query in a big database.

Department Table
DepartemntID Department

Project Table
Department, ProjectName, etc.
Department JOINS with DepartmentID.

When I drop down the list to update the Record, I get the # from the actual main table and below it the optional data from the other table.
Anyone can help me with this please?
Thank You Very Much.

 

[JJ26]

I guess it's better to use the #ID, instead of department's name.
What u're trying to do here is to fill in your Drop Down list with the 1st option which is from the actual main table, and the rest options come from other table, isn't it ??

Perhaps u could try to use

LEFT JOIN

or

RIGHT JOIN

.
By this kind of Join, u can include all records in your other table and the one which match with your main table.

Say like this :

sqlString2 = SELECT Department.DepartmentID, Department.Department, Projects.ProjID, Projects.ProjName, Projects.ProjDate, Projects.Department, Projects.Tech, Projects.TechC, Projects.Hours, Projects.Site, Projects.Week, Projects.ProjCount, Projects.Descript, Projects.Severity"
sqlString2 = sqlString2 & " FROM (Projects RIGHT JOIN Department ON Projects.Department = Department.DepartmentID)" 
sqlString2 = sqlString2 & "RIGHT JOIN Site ON Projects.Site = Site.SiteID"
sqlString2 = sqlString2 & " WHERE Projects.ProjID =" & recnum

The 1st RIGHT JOIN --> will include all records in Department (match or not match with Projects Table).

The 2nd RIGHT JOIN --> is for joining the Query with Site Table (includes all records in 1st Query).

Let me know if you're failed.

)*JJ26*)

 

[Corneliu]

No, it didnt work. It still returns the # instead of the actual name.
What I want to do is to display the name instead of the #. I know that the main table has the ID instead of the name, and it returns the right information, BUT what I would like to show is the name when I try to update the record. So the selected case is the actual Record's field information, which is tru, but the optional information comes from the other table which will give the name.
Basically like this:
The record gets returned and the drop down would look like this:
49
IT
Intranet
Quality Development
Facilities
etc
etc

Basically, the first one (49) is the actual data from within the table's record, and that 49 actually represents a department, and the names below comes from the main department table, which has those names. I hope I explain this good.
Been trying all kinds of ways to do this, but no luck.
Anyways if anyone can help me with this I would appreciate it very much.

 

[Tarwn]

Why not just loop through your RS5 to create the options and if rs5's department id = rs2's deprtment id put a &quot;selected&quot; in your option?

<select name=&quot;Department&quot; size=&quot;1&quot; class=&quot;inputBox&quot; id=&quot;Department&quot;>
	<%
	rs2.MoveFirst
	WHILE NOT RS5.EOF
		
		If RS5(&quot;DepartmentID&quot;) = RS2(&quot;DepertmentID&quot;) Then 
			Response.Write &quot;<option value='&quot; & RS5(&quot;DepartmentID&quot;) & &quot;' selected>&quot; & RS5(&quot;Department_Name&quot;) & &quot;</option>&quot;
		Else
			Response.Write &quot;<option value='&quot; & RS5(&quot;DepartmentID&quot;) & &quot;'>&quot; & RS5(&quot;Department&quot;) & &quot;</option>&quot;
		End If
		<%
		RS5.MoveNext 
	WEND 
	%>
</select>

This will display the full list of departments in your dropdown with the one from the db becoming the default selection.

In order to get the department name with your sql, you need to include it int your select portion:
sqlString2 = &quot;SELECT Projects.ProjID, Projects.ProjName, Projects.ProjDate, Projects.Department, Projects.Tech, Projects.TechC, Projects.Hours, Projects.Site, Projects.Week, Projects.ProjCount, Projects.Descript, Projects.Severity, Department.Department as Department_Name&quot;
sqlString2 = sqlString2 & &quot; FROM ((Projects INNER JOIN Technicians ON Projects.Tech = Technicians.TechID) INNER JOIN Department ON Projects.Department = Department.DepartmentID)&quot; &_
&quot;INNER JOIN Site ON Projects.Site = Site.SiteID&quot;
sqlString2 = sqlString2 & &quot; WHERE Projects.ProjID =&quot; & recnum

Hope I read your needs correctly and that helps,
-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
For my next trick I will pull a hat out of a rabbit (if you think thats bad you should see how the pigeon feels...)

 

[Corneliu]

I think is getting there, but I think is missing something that I just cant put my hand on:
I get an error:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/Reports/Updates/UpdateProject.asp, line 292

Here is the big Query:
<%

recnum = Request.QueryString(&quot;recnum&quot

recnum = TRIM(recnum)
sqlString2 = &quot;SELECT Projects.ProjID, Projects.ProjName, Projects.ProjDate, Projects.Department, Projects.Tech, Projects.TechC, Projects.Hours, Projects.Site, Projects.Week, Projects.ProjCount, Projects.Descript, Projects.Severity, Department.Department as Department_Name&quot;
sqlString2 = sqlString2 & &quot; FROM ((Projects INNER JOIN Technicians ON Projects.Tech = Technicians.TechID) INNER JOIN Department ON Projects.Department = Department.DepartmentID)&quot; &_
&quot;INNER JOIN Site ON Projects.Site = Site.SiteID&quot;
sqlString2 = sqlString2 & &quot; WHERE Projects.ProjID =&quot; & recnum

SET RS2 = objConn.Execute( sqlString2 )

%>

Here is the Department actual Table:
<%

Set RS5 = Server.CreateObject(&quot;ADODB.Recordset&quot
SqlString = &quot;SELECT Department.DepartmentID, Department.Department as Department_Name FROM Department&quot;

Set RS5 = objConn.Execute ( SqlString )
%>

And the Form Field:
<select name=&quot;Department&quot; size=&quot;1&quot; class=&quot;inputBox&quot; id=&quot;Department&quot;>
<%
RS2.MoveFirst
WHILE NOT RS5.EOF
If RS5(&quot;DepartmentID&quot = RS2(&quot;Department&quot Then
Response.Write &quot;<option value='&quot; & RS5(&quot;DepartmentID&quot & &quot;' selected>&quot; & RS5(&quot;Department_Name&quot & &quot;</option>&quot;
Else
Response.Write &quot;<option value='&quot; & RS5(&quot;DepartmentID&quot & &quot;'>&quot; & RS5(&quot;Department&quot & &quot;</option>&quot;
End If
RS5.MoveNext
WEND
%>
</select>

What am I missing here? I know its probably something dumb, but I just cant put my hand on it.

Again, Thank You ALL for your help...

 

[Corneliu]

Sorry, Line 292 being this:

Response.Write &quot;<option value='&quot; & RS5(&quot;DepartmentID&quot & &quot;'>&quot; & RS5(&quot;Department&quot & &quot;</option>&quot;

 

[JJ26]

sorry for the delay, but if you couldn't find the answer, then i think i get the answer, i.e
you're trying to retrieve rs2(&quot;department&quot in order to validate in creating dropdown list , isn't it ??

u should change to rs2(&quot;Department_Name&quot .

this is your recordset query :

sqlString2 = "SELECT Projects.ProjID, Projects.ProjName, Projects.ProjDate, Projects.Department, Projects.Tech, Projects.TechC, Projects.Hours, Projects.Site, Projects.Week, Projects.ProjCount, Projects.Descript, Projects.Severity, Department.Department as Department_Name"
sqlString2 = sqlString2 & " FROM ((Projects INNER JOIN Technicians ON Projects.Tech = Technicians.TechID) INNER JOIN Department ON Projects.Department = Department.DepartmentID)" &_
"INNER JOIN Site ON Projects.Site = Site.SiteID"
sqlString2 = sqlString2 & " WHERE Projects.ProjID =" & recnum

SET RS2 = objConn.Execute( sqlString2 )

then in your drop-down validating :

<select name=&quot;Department&quot; size=&quot;1&quot; class=&quot;inputBox&quot; id=&quot;Department&quot;>
    <%
    RS2.MoveFirst
    WHILE NOT RS5.EOF   
        If RS5(&quot;DepartmentID&quot;) = RS2(&quot;Department_Name&quot;) Then 
            Response.Write &quot;<option value='&quot; & RS5(&quot;DepartmentID&quot;) & &quot;' selected>&quot; & RS5(&quot;Department_Name&quot;) & &quot;</option>&quot;
        Else
            Response.Write &quot;<option value='&quot; & RS5(&quot;DepartmentID&quot;) & &quot;'>&quot; & RS5(&quot;Department&quot;) & &quot;</option>&quot;
        End If
        RS5.MoveNext 
    WEND 
    %>
</select>

Regards,
*JJ26*