CODEWITHSUNDEEP
jquerysql-serverasp-classicautocompletesql-server-2008-r2
tollamie
tollamie

Reputation: 117

jQuery autocomplet from database with asp

I have asp page, and I need to show a table of data from Sql Server database, I want to select the city names from 2 text areas for example(source and destination) and then show all the informations of this row. here is a copy of the autocomplete.html:

    <!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title></title>
<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.2/themes/smoothness/jquery-ui.css" />
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script src="http://code.jquery.com/ui/1.10.2/jquery-ui.js"></script>
<link rel="stylesheet" href="/resources/demos/style.css" />
<script>
$(function() {
var availableTags = [
"Shanghai",
"Shenzhen",
"Casablanca",
"Jeddah",
"Paris",
"PHP",
"London",
"Tokyo",
"Jeddah",
"Istambul"
];
$( "#tags" ).autocomplete({
source: availableTags
});
});
</script>
    </head>
    <body>
    </body>
</html>

In the page index.asp:

    ....
    <!--#include file="autocomplete.html"--> <label for="tags"> POD </label>
<input id="tags" />
    <br>

I want from the jQuery autocomplete to get the source and the destination from my database not that way:

var availableTags = [
"Shanghai",
"Shenzhen",
"Casablanca",
"Jeddah",
"Paris",
"PHP",
"London",
"Tokyo",
"Jeddah",
"Istambul"
];

How could I do this ??

Thank you for your help!

Upvotes: 0

Views: 1571

Answers (1)

user1945782
user1945782

Reputation:

You can use Classic ASP just as you would for the form. Try putting your code brackets inside the array list like so...

var availableTags = [<%=GetCities() %>];

Your GetCities() function would then output to the Response object the list of required cities.

-- Edit --

The following code has not been fully tested. You may also need to modify parts that are appropriate to your needs:

const C_NO_DATA = "NO_DATA"              'Used when no data is returned to a consuming routine
const C_ERROR   = "ERROR"                'Used when an error is generated - to be fed to the comsuming routine

'GetDataSet
'    Returns a table of data based on the supplied SQL statement and connection string.
'Parameters:
'    sqlString (string) - The SQL string to be sent.
'    connString (string) - The database connection string.
'Usage:
'    dataSet = GetDataSet(sqlString, connString)
'Description:
'    This function generates a table of information in a 2 dimensional array.  The first dimension represents the columns
'    and the second the rows.  If an error occurs while the routine is executing the array and the base index (0,0) is set 
'    to C_ERROR, (0,1) to the VBScript error index, and (0,2) to the VBScript error description.
function GetDataSet(sqlString, connString)
    'Initialise...
    dim returnVal, rsData
    on error resume next
        'Define and open the recordset object...
        set rsData = Server.CreateObject("ADODB.RecordSet")
        rsData.Open sqlString, connString, 0, 1, 1
        'Initialise an empty value for the containing array...
        redim returnVal(0,0)
        returnVal(0,0) = C_NO_DATA
        'Deal with any errors...
        if not rsData.EOF and not rsData.BOF then
            'Store the data...
            returnVal = rsData.GetRows()
            'Tidy up...
            rsData.close
            set rsData = nothing
            select case err.number
                case 3021    'No data returned
                    'Do nothing as the initial value will still exist (C_NO_DATA)
                case 0        'No error
                    'Do nothing as data has been returned
                case else
                    redim returnVal(4,0)
                    returnVal(0,0) = C_ERROR
                    returnVal(1,0) = err.number
                    returnVal(2,0) = err.description
                    returnVal(3,0) = sqlString
                    returnVal(4,0) = connString
            end select
        end if
    on error goto 0
    'Return the array...
    GetDataSet = returnVal
end function

function GetCities()
    dim sql, tCity, rc, max, rv
    sql = _
        "SELECT " & _
            "'""' + city_name + '""' AS city " & _
        "FROM  " & _
            "clkj_freight  " & _
        "WHERE  " & _
            "pol = '" & replace(request.querystring("q"), "'", "''") & "'"
    tCity = GetDataSet(sql, conn)
    if tCity(0, 0) = C_ERROR or tCity(0, 0) = C_NO_DATA then rv = tcity(0, 0)
    max = UBound(tCity, 2) '2 is the second dimension of the array
    for rc = 0 to max
        Response.Write(tCity(rc, 0))
        If rc<max then Response.Write(",")
    Next 'rc
    'If an error is encountered or no data is returned then notify the user (this behaviour may be changed if necessary)...
    GetCities = rv
end function

Upvotes: 1

Related Questions