Click here to Skip to main content
15,867,594 members
Articles / Web Development / ASP.NET
Article

AJAX Stock Symbol Drop-down List

Rate me:
Please Sign up or sign in to vote.
4.72/5 (11 votes)
15 Oct 2007GPL32 min read 72.2K   738   55   4
Look up a huge list of stock symbols from MS SQL database

Introduction

Screenshot - screenshot.gif

This ASP.NET user control can be used to assist users in selecting/picking a valid stock code from a drop-down list without effecting the webpage performance.

Users just enter the first letters of stock symbol, then the list of stock symbols with the fisrt letters will show in drop-down list. Accordingly, users can select the limited list of symbols from drop-down list.

Background

Recently, there are more than 10,000 stock symbols and it will effect the MS SQL and ASP page performance if all stock symbols are loaded and displayed in drop-down list. Users also find it diffcult to locate a stock symbol from a huge drop-down list. So, a limited list of symbols should be showed in accordance to the symbols' first letters typed by users.

Using the code

Run sample:

After downloading, unzip the StockPicker.zip to a folder"

1) Create a Database i.e BlueVision in in SQL Server Management Studio

2) Run script \App_Data\populatedata.sql in SQL Server Management Studio

3) Change the web.config

<add name="LocalSqlServer" connectionString="Data Source=(local);Integrated Security=True; database=BlueVision"/>

4) Create a Virtual Directory: StockPicker pointing to the StockPicker Folder

5) Open browser type: http://localhost/StockPicker/default.aspx

Reuse UserControls:

1. Create the database and load data as mentioned above.

2. Copy the UserControls folder to your website

2. Add the line below to the aspx page which you want to show the stockpicker control

<%@ REGISTER SRC="~/UserControls/StockPicker.ascx" TAGNAME="StockPicker" TAGPREFIX="Evisional" %>

3. Add the line below to the place you want to display the control

<Evisional:STOCKPICKER ID="StockPicker1" RUNAT="server" />

4. To get the selected value/symbol in the the submit event, you can use:

StockPicker1.ExchangeID : ExchangeID <br />StockPicker1.StockCode : Stock Code/Symbol <br />StockPicker1.StockID : Stock ID in the database

Points of Interest

You can notify that the stock symbols are filtered based on the user-typed symbols' first letters. Insteads of using ASP.NET AJAX technique, I use iframe (named stockList) to refresh the stock symbols.

When the onKeyUp events of _StockCode textbox and _ExchangeId dropdown list is triggered, the src property of stockList iframe will be change as follows:

1) Iframe control in StockPicker.ascx:

HTML
<ASP:HIDDENFIELD ID=_StockId RUNAT="server" />
<table>
<tr>
<td>
<ASP:DROPDOWNLIST id=_ExchangeId RUNAT="server" DATASOURCEID="SQLExchangeSource"
DATATEXTFIELD="ExchangeName" DATAVALUEFIELD="ExchangeID">
</ASP:DROPDOWNLIST>
<td>
<ASP:TEXTBOX id=_StockCode width=100px RUNAT="server" /><br>
<span style="position:absolute">
<iframe id="_StockList" frameborder="0" scrolling=auto src="<%=request.ApplicationPath%>/UserControls/StockPicker.aspx" 
style="position:relative;top:0; left:0; width:400px; height:150px; background: #ffffff; display:none; border: 1px solid #222222"></iframe>
</span>
</td>
</tr>
</table>

2) StockPicker.ascx's JavaScript:
ASP.NET
function onKeyUp() { 
       
    var exchangeID = document.getElementById('<%=_ExchangeId.ClientID %>').value;
    var stockCode = document.getElementById('<%=_StockCode.ClientID %>').value;
    var stockList = document.getElementById('_StockList');
                 
  if (stockCode.value!='NaN') { 
     stockList.src='http://www.yourdomain.com/UserControls/StockPicker.aspx?t=1&ExchangeId='+
     exchangeID+'&StockCode='+stockCode; stockList.style.display='block';  
  } 
   else stockList.style.display='none'; 
 
} 

3) In StockPicker.ascx.vb onload event, assign onKeyUp event for _StockCode and _ExchangeId

ASP
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
   
    SQLExchangeSource.SelectCommand = "SELECT * FROM StockExchange"
    _ExchangeId.DataBind()
    _ExchangeId.SelectedValue = 1

    _StockCode.Attributes("onkeyup") = "onKeyUp();"
    _ExchangeId.Attributes("onchange") = "onKeyUp();"


End Sub

Listed Companies/Stock Code List

For people who want to know where I get the stock code list: I searched on Google with keywords: "listed companies" and found :

US stock list: http://www.nasdaq.com/reference/comlookup.stm#viewdownload

Australian Stock List: http://www.afrsmartinvestor.com.au/tables.aspx
or http://www.asx.com.au/asx/downloadCsv/ASXListedCompanies.csv

UK: http://www.londonstockexchange.com/en-gb/pricesnews/statistics/listcompanies/

SWISS: http://www.swx.com/admission/listing/equity_market/download/issuers_equity_market_en.csv

If anyone knows other lists, please put a comment. I will update this list.

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)


Written By
Web Developer
Australia Australia
Tony is an enthusiastic Web developer and designer, specialising in web commerce development with more than 10-year experience in software development and information technology exposure. He highly expertises in ASP.NET, PHP, MS SQL, MySQL, Linux, Apache, graphics, web development, and user interface design.

Tony has worked in serveral IT projects. He has worked as the main developer of the Lonsec Ltd's website under Red Ant Technologies contract between 2005 and 2008. Currently, he is working for Nationa Bank of Australia and Serco Ltd under Lynx IT contract.

Comments and Discussions

 
GeneralA very nice Article Pin
Hemant.Kamalakar26-Oct-07 0:30
Hemant.Kamalakar26-Oct-07 0:30 
GeneralNice article! Pin
Tim Brats15-Oct-07 7:09
Tim Brats15-Oct-07 7:09 
QuestionWhy have I used iframe? Pin
Thanh Huu Nguyen (Tony)11-Jul-07 12:56
Thanh Huu Nguyen (Tony)11-Jul-07 12:56 
QuestionWhy IFrame? Pin
Michael Sync10-Jul-07 21:18
Michael Sync10-Jul-07 21:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.