I need Help in ASP, urgent!

[stud3]

hi,
I need urgent Help in ASP, I can't lose my problem since february, I can't write my codes everytime, sometimes I don't receive answers.
My Topic was Dropdown filled with a second table, I don't find the solution to my problem. If somebody there has an experience with formulars in ASP and worked with more than a table for dropdowns (I dont't use ASP.NET, I hate it!), I will really appreciate if he or she helps me, if necessary ( I prefer to do this, it's better) I can send per Mail my Files to take a better look, what I intend to do.
Please, help me!

[Easy_Coder]

I don't know what to tell you but your all over the place.

Why don't you start by describing your issue without code. If you can't describe it to us then we can't understand what your needs are.

I'm certain that if you're 'clear & consise' you'll get loads of help from this forum.

[stud3]

sometimes I need much time to describe a problem and I have no answers, that disturbs me.
But, ok. hier the explanation:
I make a databaseapplication, this aplication shows a table from a database.
1. The functions, that I use to manipulate each row from the table in the database are: Insert, Update and Delete.
2. Each row in the table has two buttons at the left side: "Delete" and "Update". A button "Insert" is at the bottom of the application.
3. When I click "Update" at a row, the application shows me a popup formular (form?) to the row. The table that I use for this application has the name "LU_Product". Its columns are: Product_id, Service_ID, Product and Report.
4. The Product_id is the primary key and I use it as a Reference to each row, but it isn't showed on the formular (it doesn't mean that is a problem). The textfelds Report ad Product are there, and the Combobox for the Service.
5. So, the problem for me is the combobox. A column Service doesn't exist in the table LU_Product, that's why I use a second table, it is the table LU_Service with columns Service_ID and Service.
6. The values of the table LU_Service must fill the combobox in this way:
The formular must show the combobox with the corresponding value Service of the column Service_ID of the row where I clicked "Update". (Is it clear?), it means: for example if the value at the row for the column is 1, the combobox must show me the value Old Contracts as selected, and the user must have the option to select another values in the combobox too, if he decides to replace the value Old Contracts, and so for every row in the table. (is it clear?)

My problem is, the textfeld Product is ok, it is showed with the corresponding value of the column Product of the row, but the combobox either shows me the value Service to the Service_ID in the row, but the user can't select another values, or it shows me 29x the same value Service or nothing. And the textfeld Report can't be showed with the corresponding value at the row.

What happens?, how do you do in this case?, did you have the same experience, so you must fill a combobox with a second table for Updating a row? I would really appreciate, if you help me..

[Easy_Coder]

OK stud3... I think I'm begining to follow you.

It sounds like this is your foreign key to services --> Service_ID. So you just need to join on the key. This will get you the service for an individual item while in *edit* mode.

select s.service, s.Service_ID  
from LU_Service as s 
inner join LU_Product as p on s.Service_ID = p.Service_ID 
where p.Product_id = 'YOUR-prod-id-here' 

You'll need to fire this above query and return the Service_ID. This sample code below assumes that the service id will be stored in this variable...
iServiceID = s.Service_ID

But you want to show all services and auto-set the one which was previously assigned. So you'll need a ComboBox Selector Function. Here's how you can accomplish this.

Function SetSelectedService(controlValue, persistedValue) 
 If Trim(lcase(controlValue)) = Trim(lcase(persistedValue)) Then 
 retVal = " value='" & controlValue & "' selected " 
 Else 
 retVal = " value='" & controlValue & "'" 
 End If 
 SetSelectedService = retVal 
End Function 

That function would get fired while you're unwinding your services recordset which is built from
select Service_ID, Service from LU_Service.

Set rstServices = Server.CreateObject("adodb.recordset") 
With rstServices 
 .Open "your command text", "your connection", adOpenForwardOnly, adLockReadOnly  
 If Not .EOF Then 
 aServices = .GetRows() 
 Else 
 aServices = Null 
 End If 
 .Close 
End With 
Set rstServices = Nothing 

If Not IsNull(aServices) Then 
 Response.Write "<select name='servicesCollection'>" 
 For y = LBound(aServices,2) To UBound(aServices,2) 
 Response.Write "<option " & SetSelectedService(aServices(0,y), iServiceID) & ">" & aServices(1,y) & "</option>" 
 Next 
 Response.Write "</select>" 
Else 
 Response.Write "No Services Exist in the Services Repository" 
End If 

This code is meant to get you going in the right direction so assume that it's not tested. Chime back in if you need more assistance.

[stud3]

hi Easy-Coder,
thanks for the answer. Because I'm a Beginner in ASP, I wanted to ask you: is your code in ASP.NET?, I can't ASP.NET. I couldn't follow your code correctly, in the code below I show you what I did, is that what you mean?

<%private function ShowChangeForm()

%>
<%
SQLquery="SELECT * FROM LU_Product WHERE Product_id=" & Request("Product_id")
Set objRS=Conn.execute(SQLquery)
%>

<head>
<title>Table LU_PRODUCT</title>
<link rel="stylesheet" type="text/css" href="format.css">
</head>
<body bgcolor="#ffffea">
<form action="change.asp" method="post">
<input type="hidden" id="form_action" name="form_action" value="chg_save">

<input type="hidden" id="Product_id" name="Product_id" value="<%Response.write(Request("Product_id")) %>">
<div>
<table align=center cellspacing=1 cellpadding=0 width="100%" border=0>
<tr><td height=25 class="headback"><font size=2><b>LU_PRODUCT - SAVE CHANGES</b></font></td></tr>
<tr><td height=15 class="textback" valign=middle style="font-family:Verdana, Arial, Helvetica, sans-serif; font-size:13px">Here you can make changes:</td>
</tr>
</table>
</div>
<br><br>
<div>
<table border=0 width=100% align="center" height="200">

<tr><td class="text"><b>Product:</b></td></tr>
<tr><td class="text"><input type="text" name="Product" size="40" maxlength="50" value="<%Response.write objRS("Produkt") %>"></td></tr>
<tr><td class="text" ><b>Service:</b></td></tr>
<tr><td class="text">
<select name="Service" id="Service">
<% SQLquery="select s.Service, s.Service_ID from LU_Service as inner join LU_Product as p on s.Service_ID = p.Service_ID where p.Product_id =" & Request("Product_id)
Set objRS=Conn.execute(SQLquery)

Function SetSelectedService(controlValue, persistedValue)
If Trim(lcase(controlValue)) = Trim(lcase(persistedValue)) Then
retVal = " value='" & controlValue & "' selected "
Else
retVal = " value='" & controlValue & "'"
End If
SetSelectedService = retVal
End Function

Set rsServices = Server.CreateObject("adodb.recordset")
With rsServices
.Open "your command text", "your connection", adOpenForwardOnly, adLockReadOnly
If Not .EOF Then
aServices = .GetRows()?aServices?
Else
aServices = Null
End If
.Close
End With
Set rsServices = Nothing

If Not IsNull(aServices) Then
Response.Write "<select name='servicesCollection'>"
For y = LBound(aServices,2) To UBound(aServices,2) 'What does the "2" mean?
Response.Write "<option " & SetSelectedService(aServices(0,y), iServiceID) & ">" & aServices(1,y) & "</option>"
Next
Response.Write "</select>"
Else
Response.Write "No Services Exist in the Services Repository"
End If %>

</td></tr>
...
<%End Function%>

[Easy_Coder]

That was all asp code (no .net). Your getting close.... this needs to change:

Oh, and I'm not sure what your scope is on the conn connection but if it's open then you can use it for the rsServices Recordset too.

Set rsServices = Server.CreateObject("adodb.recordset")  
With rsServices  
 .Open "select Service_ID, Service from LU_Service", conn, adOpenForwardOnly, adLockReadOnly, adCmdText  
 If Not .EOF Then  
 aServices = .GetRows()  
 Else  
 aServices = Null  
 End If  
 .Close  
End With  
Set rsServices = Nothing  

[stud3]

ok., I will write and test it, but not now, it's 01:45 am here in Germany. I will write you later. Thanks!Regards.

[stud3]

hi Easy-Coder,
I tested the code, but I have Errors, I had to correct some mistakes of mine, I have the following Error :

Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/_rootverzeichnis/change.asp, line 259
Function SetSelectedService(controlValue, persistedValue)

Here is the code:

<select name="Service" id="Service">
<% SQLquery="select s.Service, s.Service_ID from LU_Service as s inner join LU_Product as p on s.Service_ID = p.Service_ID where p.Product_id =" & Product_id
Set rsServices=Conn.execute(SQLquery)
iServiceID = s.Service_ID
Function SetSelectedService(controlValue, persistedValue)
If Trim(lcase(controlValue)) = Trim(lcase(persistedValue)) Then
retVal = " value='" & controlValue & "' selected "
Else
retVal = " value='" & controlValue & "'"
End If
SetSelectedService = retVal
End Function

Set rsServices = Server.CreateObject("adodb.recordset")
With rsServices
.Open "select Service_ID, Service from LU_Service", Conn, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not .EOF Then
aServices = .GetRows() '?aServices?
Else
aServices = Null
End If
.Close
End With
Set rsServices = Nothing

If Not IsNull(aServices) Then
Response.Write "<select name='servicesCollection'>"
For y = LBound(aServices,2) To UBound(aServices,2) 'What does the "2" mean?
Response.Write "<option " & SetSelectedService(aServices(0,y), iServiceID) & ">" & aServices(1,y) & "</option>"
Next
Response.Write "</select>"
Else
Response.Write "No Services Exist in the Services Repository"
End If

%>

[Easy_Coder]

You need to change this code. Remember my original code was just to get you thinking (not to be taken literally). So the service ID is actually going to come from rsServices Recordset Object:

Set rsServices=Conn.execute(SQLquery)

iServiceID = s.Service_ID <-- No worky, this was pseudo...

iServiceID = rsServices.Fields("Service_ID").Value <-- You want to do something like this.

[stud3]

sorry, I don't understand your code, I wrote a Comment where I don't understand, thanks for writing me, regards:

<% SQLquery="select s.Service, s.Service_ID from LU_Service as s inner join LU_Product as p on s.Service_ID = p.Service_ID where p.Product_id =" & Product_id
Set rsServices=Conn.execute(SQLquery)
iServiceID = rsServices.Fields("Service_ID").Value

'What is controlValue and persistedValue?
Function SetSelectedService(controlValue, persistedValue)
If Trim(lcase(controlValue)) = Trim(lcase(persistedValue)) Then
retVal = " value='" & controlValue & "' selected "
Else
retVal = " value='" & controlValue & "'"
End If
SetSelectedService = retVal
End Function

Set rsServices = Server.CreateObject("adodb.recordset")
SQLquery="select Service_ID, Service from LU_Service"
rsServices.Open SQLquery, Conn, adOpenForwardOnly, adLockReadOnly

With rsServices
If Not rsServices.EOF Then
aServices = rsServices.GetRows() 'why this?
Else
aServices = Null
End If
rsServices.Close
End With
Set rsServices = Nothing

If Not IsNull(aServices) Then
Response.Write "<select name='servicesCollection'>"
For y = LBound(aServices,2) To UBound(aServices,2) 'What does the "2" mean?
Response.Write "<option " & SetSelectedService(aServices(0,y), iServiceID) & ">" & aServices(1,y) & "</option>"
Next
Response.Write "</select>"
Else
Response.Write "No Services Exist in the Services Repository"
End If

%>

[Easy_Coder]

'What is controlValue and persistedValue?

These are input parameters to the SetSelectedService routine. What you’re basically doing is unwinding the collection of services and auto-selecting the item up for edit.

So for every service in your collection you fire the SetSelectedService routine. For example, say you have these 3 services in your database and you want to edit 'Tire Rotation'.

id service
--------------
1 Oil Change
2 Fuel Injection System Cleaning
3 Tire Rotation

For every service in the collection this routine gets called and the values will actually get passed in like this:

Loop iteration 1: SetSelectedService(1, 3) <-- no match
Loop iteration 2: SetSelectedService(2, 3) <-- no match
Loop iteration 3: SetSelectedService(3, 3) <-- bingo

The third time through you get a match so the 'selected' attribute gets passed back thereby auto-setting the Tire Rotation service in the ComboBox control.

[stud3]

hi Easy-Coder,
thanks for the help, meanwhile I found a solution, it was something difficult for me as a Beginner in ASP, so this is my solution (I mean, it works):
I add an object recordset for the table LU_Service too, that was your advice (thanks!), but somewhere in your code I did a change, I thought this can maybe work. The object recordset for the table LU_Product is objRS, so I make a comparison with help of an "if" condition between the Service_ID from both tables, the rest is easy, it works!. Regards.
<%

set rsService = Conn.execute("select * from LU_Service")
%>

<select id="Service" name="Service">
<% While not rsService.eof
if rsService("Service_ID") = objRS("Service_ID") then %>
<option SELECTED value="<%=rsService("Service_ID")%>"><%=rsService("Service")%>
<% else %>
<option value="<%=rsService("Service_ID")%>"><%=rsService("Service")%>
<% end if
rsService.movenext

Wend %>
</select>

[Easy_Coder]

Good job. Glad you got it working.