Archive for October, 2016

An “IN” clause for jQuery

I wanted to use jQuery to determine if a code selected by a user (the “code” variable below) was in a static list of codes. So basically, I wanted it to act like a SQL “IN” clause. To do that, I used jQuery’s .inArray operator:

var codeList = ['1', '2', '3', '4'];
if (code.length > 0 && $.inArray(code, codeList) > -1) { ... }

Leave a comment

Calling an Oracle function from .NET

At some point in every man’s life, he asks himself “How do I call an Oracle function from C# code?” Well, time to answer that question.

I have an Oracle package, and in the package header, I have declared my function:

FUNCTION ValidateId(pId IN VARCHAR) RETURN VARCHAR;

In the package body, I have my function implementation:

    FUNCTION ValidateId(pIdIN VARCHAR) RETURN VARCHAR IS
    
        pMessage VARCHAR(100);
        mId VARCHAR(10);
    
    BEGIN
        pMessage := 'OK';
        
        SELECT COUNT(*)
        INTO mId
        FROM MyTable
        WHERE id = pId;
        
        IF mId = 0 THEN
            pMessage := 'The ID supplied is not valid';
        END IF;

        RETURN pMessage;
    END;

It’s a simple function that verifies that the ID in question exists in the database, and returns a status based on that.

In my C# code, I use some fun and exciting ADO.NET code to call said function:

     public static string ValidateId(string id) {

         string message = String.Empty;

         using (OracleConnection conn = new OracleConnection(connString)) {
             using (OracleCommand comm = new OracleCommand("pplant.cool_package.ValidateId", conn)) {
                 comm.CommandType = CommandType.StoredProcedure;
                 comm.Parameters.Add(new OracleParameter() {
                   Direction = ParameterDirection.ReturnValue, OracleDbType = OracleDbType.Varchar2,
                     Size = 150
                 });
                 comm.Parameters.Add(":pId", OracleDbType.Varchar2, 10).Value = id;

                 conn.Open();
                 comm.ExecuteNonQuery();
                 message = comm.Parameters[0].Value.ToString();
             }
         }

         return message;
     }

A couple of things to note about returning a value from the function. The return value is declared as the first parameter, and has a direction of “ReturnValue”. Also notice that the return parameter does not have a name.

Leave a comment

Object doesn’t support property or method ‘autocomplete’

I’ve seen this message before and it usually occurs for one of two reasons:

1) The jquery-ui.js file (which is required to get autocomplete working) wasn’t included in the page
2) The script file that contains your implementation of autocomplete occurs BEFORE the jquery-ui.js file

So if I have the following code in the script.js file to attach the autocomplete code to a textbox:

$("#User").autocomplete({
    source: function (request, response) {
        if (request.length < 4) {
            return;
        }
        var customer = new Array();
        $.ajax({
            async: false,
            cache: false,
            type: "POST",
            url: "http://localhost:1234/MyController/Autocomplete",
            data: { "term": request.term },
            success: function (data) {
                for (var i = 0; i < data.length ; i++) {
                    customer[i] = { label: data[i].Value, Id: data[i].Key };
                }
            }
        });
        response(customer);
    },
    select: function (event, ui) {
        $("#UserId").val(ui.item.Id);
    }
});

Your web page or view with the autocomplete functionality should look something like this. Note our script file is after the jquery-ui.js file:

@model UserVM

@{
    ViewBag.Title = "Cool Page";
}

http://~/Scripts/jquery-ui.js
http://~/Scripts/script.js

@using (Html.BeginForm("UserStuff", "MyController")) {
 @Html.EditorFor(model => model.User)
}

Leave a comment