Retrieve Results From SQL Database

Nov 26, 2009 at 3:29 PM

I would like to implement the Flexbox on my web pages.  It is so flexible and the samples really show potential.  I need a helpful shove to move beyond the samples to dynamic data.

How do I connect the FlexBox up to the SQL Server for the search results in ASP.Net, so that the user enters a number and the results show the number and corresponding text as shown in the two column example?  Using the classic SQL Pubs sample database for example, it would allow the user to enter the Product Id, and the FlexBox would show the Product Id and Product Name narrowing down the results as they continued to type the numbers.  I would appreciate it if someone would point me in the right direction; perhaps some simple code using the SQL Pubs database, site links, or a book referral.

Thank you in advance.

Dec 28, 2009 at 4:41 PM

Found what I needed.

Mar 27, 2010 at 3:27 PM

Hi,

Could you tell me how you did it?

Or maybe it is possible to do with an existing html select list?

Mar 30, 2010 at 2:26 PM

Hi Vinny,

The flexbox is so felxible that I probably really got confused by the variety of features and their properties.  I am also new to jquery.  I am using ASP.Net 3.5 and C# in a three tier application so I was trying to link my data tier to the flexbox using a generic list.  I have since moved on and went back to my test app for some sample code.  Unfortuantely I tried to rerun the old test project; but, something in it is not quite correct and I am not sure exactly where the bug is.  I think if you start with a flexbox demo that is working based on the test array data provided on the site and then plug in the code below it will probably work.  Let me know if it does not and I will try to debug further.

/*** Search Page Designer code start ***/

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="BookSearchResults.aspx.cs" Inherits="BookSearchResults" %>

<%-- This minimal code is here to prevent the following error:
     "Using themed css files requires a header control on the page"
     Please see http://www.west-wind.com/WebLog/posts/4662.aspx for more information --%>
<head id="Head1" runat="server" visible="false" />

/*** Search Page Designer code end ***/

/*** Search Page Code Behind start ***/
using System;
using System.Collections.Generic;
using System.Web;
using System.Text;


    public partial class BookSearchResults : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            // Clear out the buffer
            Response.ClearHeaders();
            Response.ClearContent();
            Response.Clear();

            // Do not cache response
            Response.Cache.SetCacheability(HttpCacheability.NoCache);

            // Set the content type and encoding for JSON
            Response.ContentType = "application/json";
            Response.ContentEncoding = Encoding.UTF8;

            string query = Request["q"];
            Session["flexBoxSearchTerm"] = query;
            int page = int.Parse(Request["p"]);
            int size = int.Parse(Request["s"]);

            int total = 0;
            List<string> results = new List<string>();

            Test_App.Books bookSearchList = new Test_App.Books();
            List<Test_App.BookTitle> returnData = bookSearchList.getTitle(query, size);

            StringBuilder sb = new StringBuilder();
            sb.Append("{\"results\":[");

            foreach (Test_App.BookTitle resultItem in returnData)
            {
                results.Add("{\"id\":" + resultItem.TitleId.ToString() + ",\"name\":\"" + resultItem.TitleId.ToString() + " -- " + resultItem.Title.ToString() + "\"}");
            }
            total = returnData.Count;

            string[] resultsArray = results.ToArray();
            List<string> pagedResults = new List<string>();

            if (size > 0)
            {
                int start = (page - 1) * size + 1;
                int end = (start > (total - size)) ? total : start + size - 1;
                for (int i = start - 1; i < end; i++)
                {
                    pagedResults.Add(resultsArray[i]);
                }
            }

            if (pagedResults.Count > 0)
            {
                sb.Append(string.Join(",", pagedResults.ToArray()));
            }
            else if (resultsArray.Length > 0)
            {
                sb.Append(string.Join(",", resultsArray));
            }
            sb.Append("],\"total\":\"" + total + "\"}");

            Response.Write(sb.ToString());

            // Flush the response buffer
            Response.Flush();

            // Complete the request.  NOTE: Do not use Response.End() here,
            // because it throws a ThreadAbortException, which cannot be caught!
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }

    }

/*** Search Page Code Behind end ***/
/*** Search Function Books Class ***/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Test_App
{
    public class Books
    {
        public Books ()
        { }

        public List<Title> getTitle(string prefixText, int count)
        {
            string connString = "Data Source=.;Initial Catalog=Pubs;Integrated Security=true";
            if (prefixText.IndexOf(" ") > 0)
            {
                prefixText = prefixText.Split(new Char[] { ' ' })[0].Trim();
            }
            string cmdString = "SELECT dbo.Titles.Title_Id, dbo.Titles.Title FROM dbo.Titles WHERE dbo.Titles.Title_Id LIKE '%" + prefixText + "%";
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(cmdString, conn);
            conn.Open();

            SqlDataReader rdr;
            List<Title> returnData = new List<Title>();

            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            while (rdr.Read())
            {
                Title title = new Title();
                title.TitleId = rdr["Title_Id"].ToString();
                title.Title = rdr["Title"].ToString();
                returnData.Add(title);
            }
            //if no results then try again using prefixText - 1 until zero length.
            if (returnData.Count == 0)
            {
                Title title = new Title();
                title.Title = "No data found.";
                returnData.Add(title);
            }
            return returnData;

        }

    }

    public class Title
    {
        public Title()
        {
            _titleId = string.Empty;
            _title = string.Empty;
        }

        private string _titleId;
        public string TitleId
        {
            get { return _titleId; }
            set { _titleId = value; }
        }

        private string _title;
        public string Title
        {
            get { return _title; }
            set { _title = value; }
        }
    }
}

/*** Test Page with flexbox ***/


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="TestPage.aspx.cs" Inherits="Test_App.TestPage" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title></title>
    <link rel="stylesheet" type="text/css" href="../css/jquery.flexbox.css" />
    <script type="text/javascript" src="../js/jquery.min.js"></script>
    <script type="text/javascript" src="../js/jquery.flexbox.js"></script>     
    <link rel="stylesheet" type="text/css" href="../css/default.css" />

    <script language="javascript" type="text/javascript">

        $().ready(function() {
            $('#ffb9').flexbox('BookSearchResults.aspx', {
                autoCompleteFirstMatch: false,
                noResultsText: 'No results found',
                watermark: 'Enter Title Id',
                showArrow: false,
                showResults: true,
                maxVisibleRows: 0,
                minChars: 0,
                paging: false,
                width: 120, 
                onSelect: function() {
                    setHiddenValue(this.getAttribute('hiddenValue'));
                    //$('#ffb9-result').html('You selected "' + this.value + '", ' + 'which has a hidden value of ' + this.getAttribute('hiddenValue'));
                }
            });
            $('#ffb9_input').blur(function() {
                setHiddenValue(this.value);
                //$('#ffb9-result').html('The value passed when the form submitted is "' + this.value + '"');
            });
        });

        function setHiddenValue(itemValue) {
            try {
                var pageObject = document.getElementById("hidffb9Value");
                if (pageObject != null) { pageObject.value = itemValue; }
            }
            catch (err) { alert(err + "could not find object [hidffb9Value] on page."); }
        }

        
    </script>

</head>
<body>
    <form id="form1" runat="server">
	<div id="ffb9"></div>
	<div id="ffb9-result"></div>
	<asp:HiddenField ID="hidffb9Value" runat="server" Value="" />
    </form>
</body>
</html>


Mar 30, 2010 at 4:30 PM

Wow, thanks for the code!

I'll try to implement it to my site, because FlexBox looks very nice!

Jul 12, 2010 at 5:12 AM

There's also an easy way to do this straight from a datatable (or similar result set).  For example:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        Response.ClearHeaders()
        Response.ClearContent()
        Response.Clear()

        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Response.ContentType = "application/json"
        Response.ContentEncoding = Encoding.UTF8

        Dim query As String = Request("q")
        Dim page As Integer = Integer.Parse(Request("p"))
        Dim size As Integer = Integer.Parse(Request("s"))

        Dim dtCities As New DataTable
        dtCities = odata.ReturnDT("sp_AutoComplete", "@searchterm", query)
        Dim cnt As Integer
        cnt = dtCities.Rows.Count

        Dim sbmod As New StringBuilder
        sbmod.Append("{""results"":[")
        For i = 0 To cnt - 1
            If Not i = cnt - 1 Then
                sbmod.AppendLine("{""id"":" & i & ",""name"":""" & dtCities.Rows(i).Item("city") & """},")
            Else
                sbmod.AppendLine("{""id"":" & i & ",""name"":""" & dtCities.Rows(i).Item("city") & """}")
            End If
        Next
        sbmod.Append("],""total"":""" & cnt & """}")

        Response.Write(sbmod.ToString())
        Response.Flush()

        HttpContext.Current.ApplicationInstance.CompleteRequest()
    End Sub


This allows you to skip doing string arrays, etc. Works perfectly. Just setup your SQL query/procedure to accept a parameter such as:

SELECT city

FROM tbl_Cities

WHERE city LIKE @searchterm + '%' 

Hope this saves someone some time. I know this thread is quite old, but if I came here looking for answers, I'm guessing someone else might in the future.

-Nick

 

Jul 24, 2010 at 2:41 PM

Hi, i am new in flexbox but i had implemented all the things the problem arise when i want to get the Name in flexbox at the time of returning ID from database as like

dropdown selectedvalue. I had ID-Name pair in Flexbox.

 

-Omkar

Jul 26, 2010 at 3:24 PM

Are you perhaps looking at setting the first item in the list that matches the input as selected?  If so, then autoCompleteFirstMatch: true, will do that.

Jul 31, 2010 at 6:25 AM

Hi ET_0520,

Thanks for sending answer, but this is not what i want. Actually i had populated my Flexbox and it is working properly but my problem is that when i fetch the record to my .aspx page for

updation than i want to auto select the "CityName" in my Flexbox when passing the "CityID" to Flexbox just like we do in DropDown control (In the case of DropDown we do DropDown1.SelectedValue="CityID"

and the "CityName" is auto selected). Now, i hope u understand my problem. If u get it than send the code as i completely stucked at this position and i am unable proceed furthur.

Aug 2, 2010 at 5:39 PM
public string SelectedCityId
{ 

{ One caveat: I have not tried any of the code I present here.

My first thought is that you might try using the initialValue property on load of the aspx page. This should be the equivalent of using SelectedValue = for a dropdown or combo box control. For example if the City Id was 21 the script code might look like this:

<script type="text/javascript">
        $().ready(function() {
            $('#ffb9').flexbox('BookSearchResults.aspx', {
                autoCompleteFirstMatch: false,
                noResultsText: 'No results found',
                watermark: 'Enter Title Id',
                showArrow: true,
                showResults: true,
                maxVisibleRows: 0,
                minChars: 0,
                paging: false,
                width: 120, 
                initialValue: '<%# SelectedCityId.ToString() %>', 
                onSelect: function() {
                    setHiddenValue(this.getAttribute('hiddenValue'));
                }
            });
            $('#ffb9_input').blur(function() {
                setHiddenValue(this.value);
            });
        });

You could also try using the setValue function with client script like:

<script language="javascript" type="text/javascript">
     $('ffb9').setValue('<%# SelectedCityId.ToString() %>');
</script>


Both of these assume that on your aspx page you had a property named SelectedCityId and in the code behind you set that property value to a cityId such as (C# code):

private int _mCityId;

public string SelectedCityId
{
	get { return (_mCityId > 0) ? _mCityId.ToString() : ""; }
}

Aug 2, 2010 at 5:41 PM

Sorry ignore the

"public string SelectedCityId
{ 

{ "

at the top of the previous response.

Jan 1, 2011 at 12:24 AM

download sample code for flexbox -- > http://innosoftpos.com/blog/?p=33

--marty

Apr 12, 2011 at 7:49 PM

Hi

well I am new in FlexBox and copied most of the source. But now I have a little problem. If I start the site with Visual Studio 2010 my page looks like this:

{"results":[{"id":AD,"name":"test1"},{"id":CS,"name":"test2"},{"id":DG,"name":"test3"},{"id":GL,"name":"test4"},{"id":GQ,"name":"test5"},{"id":GQS,"name":"test6"},{"id":MB,"name":"test7"},{"id":MS,"name":"test8"},{"id":NP,"name":"test9"},{"id":VF,"name":"test10"},{"id":VO,"name":"test11"},{"id":VOB,"name":"test12"}],"total":"12"}<html>
    <head>
        <title>Es wurde eine Sitzungs-ID erstellt, die jedoch nicht gespeichert werden kann, da die Antwort bereits von der Anwendung gel&#246;scht wurde.</title>
        <style>
         body {font-family:"Verdana";font-weight:normal;font-size: .7em;color:black;} 
         p {font-family:"Verdana";font-weight:normal;color:black;margin-top: -5px}
         b {font-family:"Verdana";font-weight:bold;color:black;margin-top: -5px}
         H1 { font-family:"Verdana";font-weight:normal;font-size:18pt;color:red }
         H2 { font-family:"Verdana";font-weight:normal;font-size:14pt;color:maroon }
         pre {font-family:"Lucida Console";font-size: .9em}
         .marker {font-weight: bold; color: black;text-decoration: none;}
         .version {color: gray;}
         .error {margin-bottom: 10px;}
         .expandable { text-decoration:underline; font-weight:bold; color:navy; cursor:hand; }
        </style>
    </head>
	....
Well I think there is something wrong... So here you get my Code Snipped
protected void Page_Load(object sender, EventArgs e)
        {
            //Test FlexBox Start

            virtualTestTwo.TestTwo_Service Objectcode = new virtualTestTwo.TestTwo_Service();
            Objectcode.UseDefaultCredentials = false;
            Objectcode.Credentials = new System.Net.NetworkCredential("KNK1", "12345aA", "KNKVERLAG");

            Response.ClearHeaders();
            Response.ClearContent();
            Response.Clear();

            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            
            Response.ContentType = "application/json";
            Response.ContentEncoding = Encoding.UTF8;

            string query = Request["q"];
            Session["flexBoxSearchTerm"] = query;

            int page = 0; //int.Parse(Request["p"]);
            int size = 0;//int.Parse(Request["s"]);

            int total = 0;

            List<string> results = new List<string>();
            List<virtualTestTwo.TestTwo> returnData = new List<virtualTestTwo.TestTwo>();

            returnData.AddRange(Objectcode.ReadMultiple(new virtualTestTwo.TestTwo_Filter[] { }, "", 0));

            StringBuilder sb = new StringBuilder();
            sb.Append("{\"results\":[");

            foreach (virtualTestTwo.TestTwo resultItem in returnData)
            {
                results.Add("{\"id\":" + resultItem.Code.ToString() + ",\"name\":\"" + resultItem.Description.ToString() + "\"}");
            }
            total = returnData.Count;

            string[] resultsArray = results.ToArray();
            List<string> pagedResults = new List<string>();

            if (size > 0)
            {
                int start = (page - 1) * size + 1;
                int end = (start > (total - size)) ? total : start + size - 1;
                for (int i = start - 1; i < end; i++)
                {
                    pagedResults.Add(resultsArray[i]);
                }
            }

            if (pagedResults.Count > 0)
            {
                sb.Append(string.Join(",", pagedResults.ToArray()));
            }
            else if (resultsArray.Length > 0)
            {
                sb.Append(string.Join(",", resultsArray));
            }
            sb.Append("],\"total\":\"" + total + "\"}");

            Response.Write(sb.ToString());

            // Flush the response buffer
            Response.Flush();

            // Complete the request.  NOTE: Do not use Response.End() here,
            // because it throws a ThreadAbortException, which cannot be caught!
            HttpContext.Current.ApplicationInstance.CompleteRequest();


            //Test FlexBox End





        }

<script src="Scripts/nicEdit.js" type="text/javascript"></script>
    <script src="Scripts/jquery-1.4.1.js" type="text/javascript"></script>
    <script type="text/javascript" src="Scripts/jquery.flexbox.js"></script>
    <script type="text/javascript" src="Scripts/jquery.flexbox.min.js"></script>


    <asp:ScriptManager ID="ScriptManager" runat="server" EnablePageMethods="true" />

    <script type="text/javascript">

        $().ready(function () {
            $('#ffb9').flexbox('Default.aspx', {
                autoCompleteFirstMatch: false,
                noResultsText: 'No results found',
                watermark: 'Enter Title Id',
                showArrow: false,
                showResults: true,
                maxVisibleRows: 0,
                minChars: 0,
                paging: false,
                width: 120,
                onSelect: function () {
                    setHiddenValue(this.getAttribute('hiddenValue'));
                    //$('#ffb9-result').html('You selected "' + this.value + '", ' + 'which has a hidden value of ' + this.getAttribute('hiddenValue'));
                }
            });
            $('#ffb9_input').blur(function () {
                setHiddenValue(this.value);
                //$('#ffb9-result').html('The value passed when the form submitted is "' + this.value + '"');
            });
        });

        function setHiddenValue(itemValue) {
            try {
                var pageObject = document.getElementById("hidffb9Value");
                if (pageObject != null) { pageObject.value = itemValue; }
            }
            catch (err) { alert(err + "could not find object [hidffb9Value] on page."); }
        }

    </script>

    <div id="ffb9"></div>
	<div id="ffb9-result"></div>
	<asp:HiddenField ID="hidffb9Value" runat="server" Value="" />

    

    </asp:Content>
Thank you for your help!

Apr 12, 2011 at 7:51 PM

Sorry i forgot to translate the text written in <title>

Here is the translation: It has created a session id, but can not be saved because the answer was already deleted by the application.

Apr 18, 2011 at 12:47 PM

Hi guys, I am having problem using flexbox.

I have did everything like above but my drop down is not filling.

 

could you please any one help me on this.

My Result.aspx page looks like below when i request a Result.aspx

Result.aspx

{"results":[{"id":15,"name":"Prior to Employment"},{"id":16,"name":"During Employment"},{"id":17,"name":"Termination or Change of Employment"},{"id":24,"name":"Define a Statagic IT Plan"},{"id":25,"name":"Manage Quality"},{"id":26,"name":"Manage the IT Investment"},{"id":31,"name":"During Employment"},{"id":18,"name":"Roles and Responsibilities"},{"id":19,"name":"Screening"},{"id":20,"name":"Terms and Conditions of employment"},{"id":21,"name":"Management Responsibilities"},{"id":22,"name":"Deciplinary Proces"},{"id":23,"name":"Return of Assets"},{"id":30,"name":"Profile filter -Test"},{"id":27,"name":"Quality Management System"},{"id":28,"name":"Customer Focus"},{"id":32,"name":"Deciplinary process"}],"total":"17"}

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head><title>
    Untitled Page
</title><link href="../App_Themes/Standard/Default.css" type="text/css" rel="stylesheet" /></head>
<body>
    <form name="form1" method="post" action="result.aspx" id="form1">
<div>
<input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="/wEPDwUJNzgzNDMwNTMzZGSp9KoU4y5oC6C3ej13TNzhPmdVkQ==" />
</div>

    <div>
   
    </div>
    </form>
</body>
</html>

 

Flexbox page:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Home.aspx.cs" Inherits="Core_Home"
    Title=" Home" %>

<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="Server">
    <link rel="stylesheet" type="text/css" href="../FlexBox/css/main.css" />
    <link href="../FlexBox/css/dp.syntaxhighlighter.css" rel="stylesheet" type="text/css" />
    <link rel="stylesheet" type="text/css" href="../FlexBox/css/jquery.flexbox.css" />

    <script type="text/javascript" src="../FlexBox/js/jquery/1.4.2/jquery.min.js"></script>

    <script type="text/javascript" src="../FlexBox/js/jquery.flexbox.js"></script>

    <script type="text/javascript" src="../FlexBox/js/dp.SyntaxHighlighter/shCore.js"></script>

    <script type="text/javascript" src="../FlexBox/js/dp.SyntaxHighlighter/shBrushCSharp.js"></script>

    <script type="text/javascript" src="../FlexBox/js/dp.SyntaxHighlighter/shBrushJScript.js"></script>

    <script type="text/javascript" src="../FlexBox/js/dp.SyntaxHighlighter/shBrushXml.js"></script>

    <script type="text/javascript" src="../FlexBox/js/largedataset.js"></script>

    <script type="text/javascript">
            $(function() {
                dp.SyntaxHighlighter.ClipboardSwf = '../FlexBox/js/dp.SyntaxHighlighter/clipboard.swf';
                dp.SyntaxHighlighter.HighlightAll('code');
           
                // Give each example an anchor tag
                var count = 1;
                $.each($('.demo'), function() {
                    var $example = $(this);
                    $example.before('<a name="demo' + count++ + '" />');
                });

                // Set up our example table of contents
                count = 1; // reset our counter
                var $toc = $('#toc');
                $.each($('.example > td'), function() {
                    var $td = $(this);
                    var anchor = $('<a></a>').attr('href', '#demo' + count++);

                    $(document.createElement('li'))
                        .html($td.html())
                        .wrapInner(anchor)
                        .appendTo($toc);

                    // Create link back to top (floated right)
                    $(document.createElement('div'))
                        .html('<a href="#toc">Top</a>')
                        .css('float', 'right')
                        .appendTo($td);
                });
               
                // Default, when the number of results exceeds the paging threshold
                 
$('#ddlSomu').flexbox('Result.aspx', {
                autoCompleteFirstMatch: false,
                noResultsText: 'No results found',
                onSelect: function() {
                    setHiddenValue(this.getAttribute('hiddenValue'));
                   // $('#ddlSomu-result').html('You selected "' + this.value + '", ' + 'which has a hidden value of ' + this.getAttribute('hiddenValue'));
                }
            });


                 
            });
              function setHiddenValue(itemValue) {
            try {
          
                var pageObject = document.getElementById('<%=hidddlSomuValue.ClientID%>');
                if (pageObject != null) { pageObject.value = itemValue; }
             
            }
            catch (err) { alert(err + "could not find object [hidddlSomuValue] on page."); }
        }

    </script>

    somu:
    <div id="ddlSomu">
    </div>
    <div id="ddlSomu-result">
    </div>
    <asp:HiddenField ID="hidddlSomuValue" runat="server" Value="" />
    <h1>
        This is home page.</h1>
    <asp:Button ID="bt" runat="server" Text="click" />
</asp:Content>

 

 

Sep 23, 2011 at 6:39 AM

HI ,

 I am new to jquery and I want to implement fexbox in asp.net ,how can I connected to sqlserver ? I seen above codes but some problems I am geeting please give sample code to do.

Thank you for your help