468,544 Members | 1,753 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,544 developers. It's quick & easy.

How to retrieve record fro database based on the selected option.

145 100+
I want to get the value from Product_Price field which it will display it record on a text box based on a product that is selected from a list box.

Product Table
Expand|Select|Wrap|Line Numbers
  1. Product_ID
  2. Product_Name
  3. Product_Price
  4. Product_Quantity
  5. Product_Picture
  6.  
Code for asp file which contain the list box and text box.

Expand|Select|Wrap|Line Numbers
  1. <form>
  2. <select name="selOrder1">
  3. <option value="Chocolate Cheese Cake" < if (rs("Product_Price") == "Chocolate Cheese Cake" then {Response.Write("selected"))};</option>
  4. </select>
  5.  
  6. <input type="text" name="txtPrice" value="Here I do not know how to retrieve record from database("Product_Price") based on selected item on list box "selOrder1">
  7. </form>
  8.  
Oct 11 '09 #1
10 5872
puT3
145 100+
And I want to make it dynamic....the code im using
Expand|Select|Wrap|Line Numbers
  1. <select name="selOrder1"><%while(!rs.EOF){%>
  2. <option value="<%=rs("Product_ID")%>"> <%(rs("Product_Name") %></option><%rs.MoveNext()}%> 
  3. </select>
the problem is i make multiple list box in the same form only for selOrder1 are display but for other selorder2,3,4,5 it didnt display
Oct 12 '09 #2
CroCrew
564 Expert 512MB
Please Post all the code so we can help.

CroCrew~
Oct 12 '09 #3
puT3
145 100+
The overall code for order.asp...
Expand|Select|Wrap|Line Numbers
  1. <%@language="Javascript"%>
  2. <html>
  3.  
  4. <%
  5. var ordID = Request.QueryString.item("orderID");
  6. var DSN = "C:/Inetpub/wwwroot/MyBakery.mdb";
  7. var Conn = Server.CreateObject("ADODB.Connection");
  8. Conn.Provider = "Microsoft.Jet.OLEDB.4.0";
  9. Conn.Open(DSN);
  10.  
  11. sql="SELECT * FROM Products";
  12.  
  13. rs=Server.CreateObject("ADODB.Recordset");
  14. rs.Open(sql,Conn);
  15.  
  16. if (Request("btn_submit")=="SUBMIT")
  17. {
  18.     var orderHidden_ID = Request.Form("orderID");
  19.     var order_date = Request.Form("orderDate");
  20.     var ICHidden = Request.Form("custIC");
  21.     var ProIDHidden = Request.Form("productID");
  22.     var DivIDHidden = Request.Form("deliveryID");
  23.     var selection_One = Request.Form("selOrder1");
  24.     var selection_Two = Request.Form("selOrder2");
  25.     var selection_Three = Request.Form("selOrder3");
  26.     var selection_Four = Request.Form("selOrder4");
  27.     var selection_Five = Request.Form("selOrder5");
  28.     var quantity_One = Request.Form("txtQ1");
  29.     var quantity_Two = Request.Form("txtQ2");
  30.     var quantity_Three = Request.Form("txtQ3");
  31.     var quantity_Four = Request.Form("txtQ4");
  32.     var quantity_Five = Request.Form("txtQ5");
  33.     var price_One = Request.Form("txtP1");
  34.     var price_Two = Request.Form("txtP2");
  35.     var price_Three = Request.Form("txtP3");
  36.     var price_Four = Request.Form("txtP4");
  37.     var price_Five = Request.Form("txtP5");
  38.     var radio_Delivery = Request.Form("radioDelivery");
  39.     var radio_Status = Request.Form("radioStatus");
  40.  
  41.     sql_Update = "INSERT INTO Order (Order_ID,Product_ID,Order_Quantity,Order_Date, Customer_ICNumber, Total_Price, Delivery_ID) VALUES ('"+(orderHidden_ID)+"', '"+(ICHidden)+"',  '"+(proIDHidden)+"', '"+(DivIDHidden)+"', '"+(selection_One)+"','"+(selection_Two)+"','"+(selection_Three)+"','"+(selection_Four)+"','"+(selection_Five)+"','"+(quantity_One)+"','"+(quantity_Two)+"','"+(quantity_Three)+"','"+(quantity_Four)+"','"+(quantity_Five)+"','"+(radio_Delivery)+"','"+(radio_Status)+"','"+order_Date+"')";
  42.  
  43.     Conn.Execute(sql_Update);
  44.     rs.Close();
  45.     rs = null;
  46.     Conn.Close();
  47.     Conn = null;
  48.     Response.Redirect("Home.asp");
  49.  
  50. }
  51.  
  52.  
  53. %>
  54. <body>
  55. <form name="test">
  56.  
  57.     <h3>ORDER</h3><br>
  58.  
  59.     Name : <input type =" text" name="custName" VALUE ="">&nbsp;&nbsp;&nbsp;Order Date:
  60.     <input type="text" name="orderDate" VALUE =Date()><br><br>
  61.  
  62.  
  63. <table>
  64. <tr>
  65.     <td>Order 1 : 
  66.         <select name="selOrder1"><% while (!rs.EOF ){ %>
  67.     <option value="<%=rs("Product_ID")%>"><%=rs("Product_Name")%></option><% rs.MoveNext()}%>
  68.   </select></td>
  69.  
  70.     <td>Quantity : <input type="text" name="txtQ1"></td>
  71.  
  72.     <td>Price : </td>
  73.     <td><input type =" text" name="txtP1"></td>
  74. </tr>
  75. </table>
  76. <table>
  77. <tr>
  78.     <td>Order 2 :
  79.     <select name="selOrder2">
  80.           <% while (!rs.EOF ){ %>
  81.     <option value="<%=rs("Product_ID")%>"><%=rs("Product_Name")%></option><% rs.MoveNext()}%>
  82.         </select></td>
  83.  
  84.     <td>Quantity : <input type="text" name="txtQ2"</td>
  85.  
  86.     <td>Price : </td>
  87.     <td><input type =" text" name="txtP2"></td>
  88. </tr>
  89. </table>
  90. <table>
  91. <tr>
  92.     <td>Order 3 : 
  93.     <select name="selOrder3"><% while (!rs.EOF ){ %>
  94.           <option value="<%=rs("Product_ID")%>"><%=rs("Product_Name")%></option>
  95.           <% rs.Move(1)}%>
  96.   </select></td>
  97.  
  98.     <td>Quantity : <input type="text" name="txtQ3"</td>
  99.  
  100.     <td>Price : </td>
  101.     <td><input type =" text" name="txtP3"></td>
  102. </tr>
  103. </table>
  104. <table>
  105. <tr>
  106.     <td>Order 4 : 
  107.     <select name="selOrder4"><% while (!rs.EOF ){ %>
  108.           <option value="<%=rs("Product_ID")%>"><%=rs("Product_Name")%></option>
  109.           <% rs.Move(1)}%>
  110.   </select></td>
  111.  
  112.     <td>Quantity : <input type="text" name="txtQ4"</td>
  113.  
  114.     <td>Price : </td>
  115.     <td><input type =" text" name="txtP4"></td>
  116. </tr>
  117. </table>
  118. <table>
  119. <tr>
  120.     <td>Order 5 : 
  121.         <select name="selOrder5" size="1">
  122.           <% while (!rs.EOF ){ %>
  123.           <option value="<%=rs("Product_ID")%>"><%=rs("Product_Name")%></option>
  124.           <% rs.Move(1)}%>
  125.   </select></td>
  126.  
  127.     <td>Quantity : <input type="text" name="txtQ5"</td>
  128.  
  129.     <td>Price : </td>
  130.     <td><input type =" text" name="txtP5"></td>
  131. </tr>
  132.  
  133. <tr>
  134.     <td colspan ="3" align="right"><b>Total Price :</b> </td>
  135.     <td><input type =" text" name="txtTotal"></td>
  136. </tr>
  137.  
  138. </table>
  139. <%rs.Close();%>
  140.     <h6> * available for orders RM 50 and above only!</h6>
  141.  
  142.     Delivery : &nbsp&nbsp<input type = "radio" name ="radioDelivery" value ="Y">Yes &nbsp&nbsp
  143.                                               <input type = "radio" name ="radioDelivery" value ="N">No<br><br> 
  144.  
  145.  
  146.  
  147.                      Area : &nbsp&nbsp <input type = "radio" name ="radioArea" value ="KL">Kuala Lumpur &nbsp&nbsp
  148.                                          <input type = "radio" name ="radioArea" value ="Selangor">Selangor<br><br><br>
  149.  
  150.  
  151.              &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
  152.              <input type = "submit" name ="btn_submit" value ="SUBMIT">&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
  153.              <input type = "submit" name ="btn_calc" value ="CALCULATE">&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
  154.                             <input type = "reset"  value ="RESET">&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
  155.              <input type = "submit" name ="btn_cancel" value ="CANCEL">
  156.  
  157. </form>
  158.  
  159. </body>
  160. </html>
  161.  
  162.  
Oct 13 '09 #4
CroCrew
564 Expert 512MB
Hello puT3,

You got a lot going on in that code of yours!

Let me start off by letting you know that you’re “Insert” statement will not work. You are passing to many values into your sql string. You can see it better in the code that I did for you below.

Also, you should move up the block of code that does the “Insert” and “Redirect” before you go and run the “Select” statement. The reason is why have that overhead on the web server to go out and hit the database when you have no intentions on displaying the data?

OK, with that said I have provided you an “example” to the solution that you are looking for.

Expand|Select|Wrap|Line Numbers
  1. <% 
  2.     Set adoCon = Server.CreateObject("ADODB.Connection")  
  3.     adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Relative path to your Access database")   
  4.     Set rsData = Server.CreateObject("ADODB.Recordset")  
  5.  
  6.     If (Request.ServerVariables("REQUEST_METHOD") = "POST") Then
  7.         SQL = "INSERT INTO Order (Order_ID, Product_ID, Order_Quantity, Order_Date, Customer_ICNumber, Total_Price, Delivery_ID) "
  8.         SQL = SQL & "VALUES ('"
  9.         SQL = SQL & Request.Form("orderID") & "', '"
  10.         SQL = SQL & Request.Form("custIC") & "', '"
  11.         SQL = SQL & Request.Form("productID") & "', '"
  12.         SQL = SQL & Request.Form("deliveryID") & "', '"
  13.         SQL = SQL & Request.Form("selOrder1") & "', '"
  14.         SQL = SQL & Request.Form("selOrder2") & "', '"
  15.         SQL = SQL & Request.Form("selOrder3") & "', '"
  16.         SQL = SQL & Request.Form("selOrder4") & "', '"
  17.         SQL = SQL & Request.Form("selOrder5") & "', '"
  18.         SQL = SQL & Request.Form("txtQ1") & "', '"
  19.         SQL = SQL & Request.Form("txtQ2") & "', '"
  20.         SQL = SQL & Request.Form("txtQ3") & "', '"
  21.         SQL = SQL & Request.Form("txtQ4") & "', '"
  22.         SQL = SQL & Request.Form("txtQ5") & "', '"
  23.         SQL = SQL & Request.Form("radioDelivery") & "', '"
  24.         SQL = SQL & Request.Form("radioStatus") & "', '"
  25.         SQL = SQL & Request.Form("orderDate") & "')"
  26.  
  27.         adoCon.Execute SQL 
  28.         Response.Redirect("Home.asp")
  29.     End If
  30.  
  31.     SQL = "SELECT * FROM Products"  
  32.     rsData.CursorType = 1
  33.     rsData.LockType = 3
  34.     rsData.Open SQL, adoCon
  35. %> 
  36. <html>
  37.     <head>
  38.         <title>Test Page</title>
  39.         <script language="JavaScript">
  40.             var UnitPriceArray = new Array(<%=rsData.RecordCount%>);
  41.                 <%i = 0%>
  42.                 <%Do Until (rsData.EOF)%>
  43.                     UnitPriceArray[<%=i%>]='<%=rsData("Product_Price").value%>';
  44.                     <%i = (i + 1)%>
  45.                     <%rsData.MoveNext%>
  46.                 <%Loop%>
  47.  
  48.             function UpdateValues()
  49.             {
  50.                 var TheSelectedValue = document.xForm.xItem.options[document.xForm.xItem.selectedIndex].value;
  51.                 document.xForm.xUnitPrice.value = UnitPriceArray[TheSelectedValue];
  52.             }
  53.         </script>
  54.     </head>
  55.     <body>
  56.         <form name="xForm" method="post" action="#">
  57.             <table> 
  58.                 <tr> 
  59.                     <td>
  60.                         Item:  
  61.                         <select name="xItem" name="xItem" onchange="UpdateValues();">
  62.                             <option value="-1">Select One</option>
  63.                             <%
  64.                                 i = 0
  65.                                 rsData.MoveFirst
  66.                                 Do Until (rsData.EOF)
  67.                                   Response.Write("<option value='" & i & "'>" & rsData("Product_Name") & "</option>")
  68.                                   i = (i + 1)
  69.                                   rsData.MoveNext
  70.                                 Loop
  71.                             %>
  72.                         </select>
  73.                     </td> 
  74.                     <td>Unit Price : <input type="text" name="xUnitPrice" id="xUnitPrice"></td> 
  75.                     <td>Quantity : <input type="text" name="xQuantity" id="xQuantity"></td> 
  76.                     <td>Total : <input type =" text" name="xTotal" id="xTotal"></td> 
  77.                 </tr> 
  78.             </table> 
  79.         </form>
  80.     </body>
  81. </html>
  82.  
Hope this helps,
CroCrew~
Oct 13 '09 #5
puT3
145 100+
thanks i will try it...
Oct 14 '09 #6
puT3
145 100+
I already try your code but i eliminate the insert sql part because i want to see whether the price of the selected item from the list box will display on the text box, but when i try to open the asp file on the browser it does not open....

the changes i made:
Expand|Select|Wrap|Line Numbers
  1. <%@language="javascript"%>
  2. <% 
  3.     var DSN = "C:/Inetpub/wwwroot/MyBakery.mdb";
  4.       var Conn = Server.CreateObject("ADODB.Connection");
  5. Conn.Provider = "Microsoft.Jet.OLEDB.4.0";
  6. Conn.Open(DSN);
  7.  
  8.  
  9.  
  10.     SQL = "SELECT * FROM Products"  
  11.  rs=Server.CreateObject("ADODB.Recordset");
  12. rs.Open(SQL,Conn);
  13.  
  14. %> 
  15. <html>
  16.     <head>
  17.         <title>Test Page</title>
  18.         <script language="JavaScript">
  19.             var UnitPriceArray = new Array(<%=rsData.RecordCount%>);
  20.                 <%i = 0%>
  21.                 <%While (rsData.EOF)%>
  22.                     UnitPriceArray[<%=i%>]='<%=rsData("Product_Price").value%>';
  23.                    <%i = (i + 1)%>
  24.                     <%rsData.MoveNext%>
  25.  
  26.  
  27.          function UpdateValues()
  28.             {
  29.                 var TheSelectedValue = document.xForm.xItem.options[document.xForm.xItem.selectedIndex].value;
  30.                 document.xForm.xUnitPrice.value = UnitPriceArray[TheSelectedValue];
  31.             }
  32.         </script>
  33.     </head>
  34.     <body>
  35.         <form name="xForm" method="post" action="#">
  36.             <table> 
  37.                 <tr> 
  38.                     <td>
  39.                         Item:  
  40.                         <select name="xItem" name="xItem" onchange="UpdateValues();">
  41.                             <option value="-1">Select One</option>
  42.                             <%
  43.                                 i = 0
  44.                                 rsData.MoveFirst
  45.                                 While (rsData.EOF)
  46.                                   Response.Write("<option value='" & i & "'>" & rsData("Product_Name") & "</option>")
  47.                                   i = (i + 1)
  48.                                   rsData.MoveNext
  49.  
  50.                             %>
  51.                         </select>
  52.                     </td> 
  53.                     <td>Unit Price : <input type="text" name="xUnitPrice" id="xUnitPrice"></td> 
  54.                     <td>Quantity : <input type="text" name="xQuantity" id="xQuantity"></td> 
  55.                     <td>Total : <input type =" text" name="xTotal" id="xTotal"></td> 
  56.                 </tr> 
  57.             </table> 
  58. <input type="submit" name="submit" value="SUBMIT">
  59.         </form>
  60.     </body>
  61. </html>
  62.  
  63.  
Oct 16 '09 #7
CroCrew
564 Expert 512MB
What error do you get?
Oct 16 '09 #8
CroCrew
564 Expert 512MB
Try this:

Expand|Select|Wrap|Line Numbers
  1. <%  
  2.     Set adoCon = Server.CreateObject("ADODB.Connection")   
  3.     adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Relative path to your Access database")    
  4.     Set rsData = Server.CreateObject("ADODB.Recordset")   
  5.  
  6.     SQL = "SELECT * FROM Products"   
  7.     rsData.CursorType = 1 
  8.     rsData.LockType = 3 
  9.     rsData.Open SQL, adoCon 
  10. %>  
  11. <html> 
  12.     <head> 
  13.         <title>Test Page</title> 
  14.         <script language="JavaScript"> 
  15.             var UnitPriceArray = new Array(<%=rsData.RecordCount%>); 
  16.                 <%i = 0%> 
  17.                 <%Do Until (rsData.EOF)%> 
  18.                     UnitPriceArray[<%=i%>]='<%=rsData("Product_Price").value%>'; 
  19.                     <%i = (i + 1)%> 
  20.                     <%rsData.MoveNext%> 
  21.                 <%Loop%> 
  22.  
  23.             function UpdateValues() 
  24.             { 
  25.                 var TheSelectedValue = document.xForm.xItem.options[document.xForm.xItem.selectedIndex].value; 
  26.                 document.xForm.xUnitPrice.value = UnitPriceArray[TheSelectedValue]; 
  27.             } 
  28.         </script> 
  29.     </head> 
  30.     <body> 
  31.         <form name="xForm" method="post" action="#"> 
  32.             <table>  
  33.                 <tr>  
  34.                     <td> 
  35.                         Item:   
  36.                         <select name="xItem" name="xItem" onchange="UpdateValues();"> 
  37.                             <option value="-1">Select One</option> 
  38.                             <% 
  39.                                 i = 0 
  40.                                 rsData.MoveFirst 
  41.                                 Do Until (rsData.EOF) 
  42.                                   Response.Write("<option value='" & i & "'>" & rsData("Product_Name") & "</option>") 
  43.                                   i = (i + 1) 
  44.                                   rsData.MoveNext 
  45.                                 Loop 
  46.                             %> 
  47.                         </select> 
  48.                     </td>  
  49.                     <td>Unit Price : <input type="text" name="xUnitPrice" id="xUnitPrice"></td>  
  50.                     <td>Quantity : <input type="text" name="xQuantity" id="xQuantity"></td>  
  51.                     <td>Total : <input type =" text" name="xTotal" id="xTotal"></td>  
  52.                 </tr>  
  53.             </table>  
  54.         </form> 
  55.     </body> 
  56. </html> 
  57.  
Oct 16 '09 #9
CroCrew
564 Expert 512MB
Or if you want to keep working in JavaScript look at your looping...

When I code in Classic ASP I like to keep server side code in VB. That way when I see JavaScript in my code I know it is client side only. Just looks more elegant too.

Hope that helps,
CroCrew~
Oct 16 '09 #10
puT3
145 100+
ok i tried it the code based on the code i send to you (Post#7)
and there is problem on (<%=rsData.RecordCount%>) part if i run it the page will be blank...
but if i deleted that part and leave the array part empty...the text box that show the price based on the selected item display "undefined"....
is it maybe because im using language javascript it cannot identified it?if it is, can you tell me what it is javascript language....Thanks
Oct 17 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Paolo | last post: by
19 posts views Thread by davidgordon | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by UniDue | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.