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

 
Product_ID

Product_Name

Product_Price

Product_Quantity

Product_Picture

Code for asp file which contain the list box and text box.

Expand|Select|Wrap|Line Numbers

 
<form>

<select name="selOrder1">

<option value="Chocolate Cheese Cake" < if (rs("Product_Price") == "Chocolate Cheese Cake" then {Response.Write("selected"))};</option>

</select>
 
<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">

</form>

Oct 11 '09 #1

Follow Post Reply

5983

puT3

145

100+

And I want to make it dynamic....the code im using

Expand|Select|Wrap|Line Numbers

 
<select name="selOrder1"><%while(!rs.EOF){%>

<option value="<%=rs("Product_ID")%>"> <%(rs("Product_Name") %></option><%rs.MoveNext()}%> 

</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

 
<%@language="Javascript"%>

<html>
 
<%

var ordID = Request.QueryString.item("orderID");

var DSN = "C:/Inetpub/wwwroot/MyBakery.mdb";

var Conn = Server.CreateObject("ADODB.Connection");

Conn.Provider = "Microsoft.Jet.OLEDB.4.0";

Conn.Open(DSN);
 
sql="SELECT * FROM Products";
 
rs=Server.CreateObject("ADODB.Recordset");

rs.Open(sql,Conn);
 
if (Request("btn_submit")=="SUBMIT")

{

    var orderHidden_ID = Request.Form("orderID");

    var order_date = Request.Form("orderDate");

    var ICHidden = Request.Form("custIC");

    var ProIDHidden = Request.Form("productID");

    var DivIDHidden = Request.Form("deliveryID");

    var selection_One = Request.Form("selOrder1");

    var selection_Two = Request.Form("selOrder2");

    var selection_Three = Request.Form("selOrder3");

    var selection_Four = Request.Form("selOrder4");

    var selection_Five = Request.Form("selOrder5");

    var quantity_One = Request.Form("txtQ1");

    var quantity_Two = Request.Form("txtQ2");

    var quantity_Three = Request.Form("txtQ3");

    var quantity_Four = Request.Form("txtQ4");

    var quantity_Five = Request.Form("txtQ5");

    var price_One = Request.Form("txtP1");

    var price_Two = Request.Form("txtP2");

    var price_Three = Request.Form("txtP3");

    var price_Four = Request.Form("txtP4");

    var price_Five = Request.Form("txtP5");

    var radio_Delivery = Request.Form("radioDelivery");

    var radio_Status = Request.Form("radioStatus");
 
    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+"')";
 
    Conn.Execute(sql_Update);

    rs.Close();

    rs = null;

    Conn.Close();

    Conn = null;

    Response.Redirect("Home.asp");
 
}
 
%>

<body>

<form name="test">
 
    <h3>ORDER</h3><br>
 
    Name : <input type =" text" name="custName" VALUE ="">&nbsp;&nbsp;&nbsp;Order Date:

    <input type="text" name="orderDate" VALUE =Date()><br><br>
 
<table>

<tr>

    <td>Order 1 : 

        <select name="selOrder1"><% while (!rs.EOF ){ %>

    <option value="<%=rs("Product_ID")%>"><%=rs("Product_Name")%></option><% rs.MoveNext()}%>

  </select></td>
 
    <td>Quantity : <input type="text" name="txtQ1"></td>
 
    <td>Price : </td>

    <td><input type =" text" name="txtP1"></td>

</tr>

</table>

<table>

<tr>

    <td>Order 2 :

    <select name="selOrder2">

          <% while (!rs.EOF ){ %>

    <option value="<%=rs("Product_ID")%>"><%=rs("Product_Name")%></option><% rs.MoveNext()}%>

        </select></td>
 
    <td>Quantity : <input type="text" name="txtQ2"</td>
 
    <td>Price : </td>

    <td><input type =" text" name="txtP2"></td>

</tr>

</table>

<table>

<tr>

    <td>Order 3 : 

    <select name="selOrder3"><% while (!rs.EOF ){ %>

          <option value="<%=rs("Product_ID")%>"><%=rs("Product_Name")%></option>

          <% rs.Move(1)}%>

  </select></td>
 
    <td>Quantity : <input type="text" name="txtQ3"</td>
 
    <td>Price : </td>

    <td><input type =" text" name="txtP3"></td>

</tr>

</table>

<table>

<tr>

    <td>Order 4 : 

    <select name="selOrder4"><% while (!rs.EOF ){ %>

          <option value="<%=rs("Product_ID")%>"><%=rs("Product_Name")%></option>

          <% rs.Move(1)}%>

  </select></td>
 
    <td>Quantity : <input type="text" name="txtQ4"</td>
 
    <td>Price : </td>

    <td><input type =" text" name="txtP4"></td>

</tr>

</table>

<table>

<tr>

    <td>Order 5 : 

        <select name="selOrder5" size="1">

          <% while (!rs.EOF ){ %>

          <option value="<%=rs("Product_ID")%>"><%=rs("Product_Name")%></option>

          <% rs.Move(1)}%>

  </select></td>
 
    <td>Quantity : <input type="text" name="txtQ5"</td>
 
    <td>Price : </td>

    <td><input type =" text" name="txtP5"></td>

</tr>
 
<tr>

    <td colspan ="3" align="right"><b>Total Price :</b> </td>

    <td><input type =" text" name="txtTotal"></td>

</tr>
 
</table>

<%rs.Close();%>

    <h6> * available for orders RM 50 and above only!</h6>
 
    Delivery : &nbsp&nbsp<input type = "radio" name ="radioDelivery" value ="Y">Yes &nbsp&nbsp

                                              <input type = "radio" name ="radioDelivery" value ="N">No<br><br> 
 
                     Area : &nbsp&nbsp <input type = "radio" name ="radioArea" value ="KL">Kuala Lumpur &nbsp&nbsp

                                         <input type = "radio" name ="radioArea" value ="Selangor">Selangor<br><br><br>
 
             &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp

             <input type = "submit" name ="btn_submit" value ="SUBMIT">&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp

             <input type = "submit" name ="btn_calc" value ="CALCULATE">&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp

                            <input type = "reset"  value ="RESET">&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp

             <input type = "submit" name ="btn_cancel" value ="CANCEL">
 
</form>
 
</body>

</html>

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

 
<% 

    Set adoCon = Server.CreateObject("ADODB.Connection")  

    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Relative path to your Access database")   

    Set rsData = Server.CreateObject("ADODB.Recordset")  
 
    If (Request.ServerVariables("REQUEST_METHOD") = "POST") Then

        SQL = "INSERT INTO Order (Order_ID, Product_ID, Order_Quantity, Order_Date, Customer_ICNumber, Total_Price, Delivery_ID) "

        SQL = SQL & "VALUES ('"

        SQL = SQL & Request.Form("orderID") & "', '"

        SQL = SQL & Request.Form("custIC") & "', '"

        SQL = SQL & Request.Form("productID") & "', '"

        SQL = SQL & Request.Form("deliveryID") & "', '"

        SQL = SQL & Request.Form("selOrder1") & "', '"

        SQL = SQL & Request.Form("selOrder2") & "', '"

        SQL = SQL & Request.Form("selOrder3") & "', '"

        SQL = SQL & Request.Form("selOrder4") & "', '"

        SQL = SQL & Request.Form("selOrder5") & "', '"

        SQL = SQL & Request.Form("txtQ1") & "', '"

        SQL = SQL & Request.Form("txtQ2") & "', '"

        SQL = SQL & Request.Form("txtQ3") & "', '"

        SQL = SQL & Request.Form("txtQ4") & "', '"

        SQL = SQL & Request.Form("txtQ5") & "', '"

        SQL = SQL & Request.Form("radioDelivery") & "', '"

        SQL = SQL & Request.Form("radioStatus") & "', '"

        SQL = SQL & Request.Form("orderDate") & "')"
 
        adoCon.Execute SQL 

        Response.Redirect("Home.asp")

    End If
 
    SQL = "SELECT * FROM Products"  

    rsData.CursorType = 1

    rsData.LockType = 3

    rsData.Open SQL, adoCon

%> 

<html>

    <head>

        <title>Test Page</title>

        <script language="JavaScript">

            var UnitPriceArray = new Array(<%=rsData.RecordCount%>);

                <%i = 0%>

                <%Do Until (rsData.EOF)%>

                    UnitPriceArray[<%=i%>]='<%=rsData("Product_Price").value%>';

                    <%i = (i + 1)%>

                    <%rsData.MoveNext%>

                <%Loop%>
 
            function UpdateValues()

            {

                var TheSelectedValue = document.xForm.xItem.options[document.xForm.xItem.selectedIndex].value;

                document.xForm.xUnitPrice.value = UnitPriceArray[TheSelectedValue];

            }

        </script>

    </head>

    <body>

        <form name="xForm" method="post" action="#">

            <table> 

                <tr> 

                    <td>

                        Item:  

                        <select name="xItem" name="xItem" onchange="UpdateValues();">

                            <option value="-1">Select One</option>

                            <%

                                i = 0

                                rsData.MoveFirst

                                Do Until (rsData.EOF)

                                  Response.Write("<option value='" & i & "'>" & rsData("Product_Name") & "</option>")

                                  i = (i + 1)

                                  rsData.MoveNext

                                Loop

                            %>

                        </select>

                    </td> 

                    <td>Unit Price : <input type="text" name="xUnitPrice" id="xUnitPrice"></td> 

                    <td>Quantity : <input type="text" name="xQuantity" id="xQuantity"></td> 

                    <td>Total : <input type =" text" name="xTotal" id="xTotal"></td> 

                </tr> 

            </table> 

        </form>

    </body>

</html>

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

 
<%@language="javascript"%>

<% 

    var DSN = "C:/Inetpub/wwwroot/MyBakery.mdb";

      var Conn = Server.CreateObject("ADODB.Connection");

Conn.Provider = "Microsoft.Jet.OLEDB.4.0";

Conn.Open(DSN);
 
    SQL = "SELECT * FROM Products"  

 rs=Server.CreateObject("ADODB.Recordset");

rs.Open(SQL,Conn);
 
%> 

<html>

    <head>

        <title>Test Page</title>

        <script language="JavaScript">

            var UnitPriceArray = new Array(<%=rsData.RecordCount%>);

                <%i = 0%>

                <%While (rsData.EOF)%>

                    UnitPriceArray[<%=i%>]='<%=rsData("Product_Price").value%>';

                   <%i = (i + 1)%>

                    <%rsData.MoveNext%>
 
         function UpdateValues()

            {

                var TheSelectedValue = document.xForm.xItem.options[document.xForm.xItem.selectedIndex].value;

                document.xForm.xUnitPrice.value = UnitPriceArray[TheSelectedValue];

            }

        </script>

    </head>

    <body>

        <form name="xForm" method="post" action="#">

            <table> 

                <tr> 

                    <td>

                        Item:  

                        <select name="xItem" name="xItem" onchange="UpdateValues();">

                            <option value="-1">Select One</option>

                            <%

                                i = 0

                                rsData.MoveFirst

                                While (rsData.EOF)

                                  Response.Write("<option value='" & i & "'>" & rsData("Product_Name") & "</option>")

                                  i = (i + 1)

                                  rsData.MoveNext
 
                            %>

                        </select>

                    </td> 

                    <td>Unit Price : <input type="text" name="xUnitPrice" id="xUnitPrice"></td> 

                    <td>Quantity : <input type="text" name="xQuantity" id="xQuantity"></td> 

                    <td>Total : <input type =" text" name="xTotal" id="xTotal"></td> 

                </tr> 

            </table> 

<input type="submit" name="submit" value="SUBMIT">

        </form>

    </body>

</html>

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

 
<%  

    Set adoCon = Server.CreateObject("ADODB.Connection")   

    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Relative path to your Access database")    

    Set rsData = Server.CreateObject("ADODB.Recordset")   
 
    SQL = "SELECT * FROM Products"   

    rsData.CursorType = 1 

    rsData.LockType = 3 

    rsData.Open SQL, adoCon 

%>  

<html> 

    <head> 

        <title>Test Page</title> 

        <script language="JavaScript"> 

            var UnitPriceArray = new Array(<%=rsData.RecordCount%>); 

                <%i = 0%> 

                <%Do Until (rsData.EOF)%> 

                    UnitPriceArray[<%=i%>]='<%=rsData("Product_Price").value%>'; 

                    <%i = (i + 1)%> 

                    <%rsData.MoveNext%> 

                <%Loop%> 
 
            function UpdateValues() 

            { 

                var TheSelectedValue = document.xForm.xItem.options[document.xForm.xItem.selectedIndex].value; 

                document.xForm.xUnitPrice.value = UnitPriceArray[TheSelectedValue]; 

            } 

        </script> 

    </head> 

    <body> 

        <form name="xForm" method="post" action="#"> 

            <table>  

                <tr>  

                    <td> 

                        Item:   

                        <select name="xItem" name="xItem" onchange="UpdateValues();"> 

                            <option value="-1">Select One</option> 

                            <% 

                                i = 0 

                                rsData.MoveFirst 

                                Do Until (rsData.EOF) 

                                  Response.Write("<option value='" & i & "'>" & rsData("Product_Name") & "</option>") 

                                  i = (i + 1) 

                                  rsData.MoveNext 

                                Loop 

                            %> 

                        </select> 

                    </td>  

                    <td>Unit Price : <input type="text" name="xUnitPrice" id="xUnitPrice"></td>  

                    <td>Quantity : <input type="text" name="xQuantity" id="xQuantity"></td>  

                    <td>Total : <input type =" text" name="xTotal" id="xTotal"></td>  

                </tr>  

            </table>  

        </form> 

    </body> 

</html>

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

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

Post your reply

Similar topics