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

Subscribe Post Reply

6222

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

Similar topics

List Box to Move to record

by: Paolo | last post by:

Friends, I have created a form named FRMNEWCLIENTS whose record source is a table named NEWCLIENTS. This table has a field named FILENUMBER. I have added on the form a combobox using the third...

Microsoft Access / VBA

Multiple Record Copy Transaction...

by: davidgordon | last post by:

Hi, I need some pointers/help on how to do the following if it possible: In my access db, I have the following: Tables: Products, Sub-Assembly, Product-Pack Table, Products

Microsoft Access / VBA

I want to create a database and store and retrieve information

by: Roy Gourgi | last post by:

Hi, I am used to working in Visual FoxPro and I would like to be able to create a database and store and retrieve information from it. What is the simplest way to do it and what should I be...

C# / C Sharp

DAO recordset append single record locks entire table (linked table)

by: RayPower | last post by:

I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4....

Microsoft Access / VBA

HELP Do Until EOF(1) drops first Record ?

by: hagar | last post by:

Hi all, I have a problem which I can not understand why this is happening! Debugging this I actually see that it grabs first record then when stepping through code to the line rsImportTo.AddNew...

Visual Basic 4 / 5 / 6

how to retrieve records submmited on a form

by: kev | last post by:

Hi all, I have created a database for equipments. I have a form to register the equipment meaning filling in all the particulars (ID, serial, type, location etc). I have two buttons at the end...

Microsoft Access / VBA

Could we retrieve the value from a populated pull down menu and reuse that value

by: giandeo | last post by:

Hello Experts. Is it possible to retrieve the value from a populated pull down menu from a database and then use that value to access the same database to get the related fields. Example: ...

ASP / Active Server Pages

Dynamically change record source/control source on a form

by: beacon | last post by:

Hi everybody, Using Access 2003. I'm trying to dynamically set the record source for the current form and set the control source for a text box to input data to one of three tables. I have a...

Microsoft Access / VBA

disable option in javascript based on selection

by: Venturini | last post by:

I am trying to put together a web page where the customer makes choices of products and is then given a total. I am extremely new to Javascript and have managed to get as far as I have from web...

Javascript

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General