Display validation message based on Table and Column availability in database using AngularJS in ASP.Net MVC

Last Reply 8 days ago By dharmendr

Posted 9 days ago

Hi,

I have one grid view with fields

Make Fields disabled based on DropDownList Selection using AngularJS

Currently my requirement is

FromTable - if i am trying type table name which is not exists in database need to display validation message at the time of entry

Ex:If table name exists on database display message Table name avaliable or else display not avaliable at the time of entry only

In the same way for DisplayCName() and ValueCName (Both names should names exist in the From Table) need to display validation message at the time of entry

If i am trying to enter any field value which not exists in Fromtable field table

Ex: If i am trying to type one name(Field) which is not exists on table(FromTable) display message based upon avaliability

I have one Employee table with relevant fields in database

EmpId EmpName

Valid O/p

..............................

FromTable DisplayCName ValueCName

Employee EmpName EmpId

Invalid o/p(Since Name and Id doesnot exists in Employee table)

FromTable DisplayCName ValueCName

Employee Name Id

Note:if everything is valid then only i need to save details to database or else display validation messages at relevant fields

Could you please check it and help me

You are viewing reply posted by: dharmendr 8 days ago.
Posted 8 days ago Modified on 6 days ago

Hi mahesh213,

Check this example. Now please take its reference and correct your code.

Note : Based on your condition change the code accordingly.

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        return View();
    }

    public JsonResult ValidateTable(string tableName)
    {
        string exist;
        SqlConnection con = new SqlConnection();
        string connectionstring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        con.ConnectionString = connectionstring;
        SqlCommand cmd = new SqlCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @Name", con);
        cmd.Parameters.AddWithValue("@Name", tableName);
        con.Open();
        exist = Convert.ToString(cmd.ExecuteScalar());
        return Json(exist, JsonRequestBehavior.AllowGet);
    }

    public JsonResult ValidateColumnName(string tableName, string columnName)
    {
        string exist;
        SqlConnection con = new SqlConnection();
        string connectionstring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        con.ConnectionString = connectionstring;
        SqlCommand cmd = new SqlCommand("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table AND COLUMN_NAME = @Column", con);
        cmd.Parameters.AddWithValue("@Table", tableName);
        cmd.Parameters.AddWithValue("@Column", columnName);
        con.Open();
        exist = Convert.ToString(cmd.ExecuteScalar());
        return Json(exist, JsonRequestBehavior.AllowGet);
    }
}

View

<html>
<head>
    <title>Index</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/css/bootstrap.min.css" />
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.5.5/angular.js"></script>
    <script type="text/javascript">
        var app = angular.module("MyApp", []);
        app.controller("MyController", function ($scope, $http) {
            $scope.Details = [{ CName: "Fromdate", CType: 'audi', FromTable: '', DisplayCName: '', ValueCName: '', Date: '' },
                            { CName: "Todate", CType: 'audi', FromTable: '', DisplayCName: '', ValueCName: '', Date: ''}];
            for (var i = 0; i < $scope.Details.length; i++) {
                $scope.Details[i]["isDisabledFromTable"] = true;
                $scope.Details[i]["isDisabledDisplayCName"] = true;
                $scope.Details[i]["isDisabledValueCName"] = true;
                $scope.Details[i]["isDisabledDate"] = true;
            }
            $scope.TypeChange = function (cType, cName, index) {
                $scope.Details[index].FromTable = "";
                $scope.Details[index].DisplayCName = "";
                $scope.Details[index].ValueCName = "";
                for (var i = 0; i < $scope.Details.length; i++) {
                    if ($scope.Details[i].CName == cName) {
                        if (cType == "Dropdown") {
                            $scope.Details[i]["isDisabledDate"] = true;
                            $scope.Details[i]["isDisabledFromTable"] = false;
                            $scope.Details[i]["isDisabledDisplayCName"] = false;
                            $scope.Details[i]["isDisabledValueCName"] = false;
                        } else if (cType == "DatePicker") {
                            $scope.Details[i]["isDisabledFromTable"] = true;
                            $scope.Details[i]["isDisabledDisplayCName"] = true;
                            $scope.Details[i]["isDisabledValueCName"] = true;
                            $scope.Details[i]["isDisabledDate"] = false;
                            $scope.Details[i]["TableMessage"] = "";
                            $scope.Details[i]["DisplayNameMessage"] = "";
                            $scope.Details[i]["ValueNameMessage"] = "";
                        } else if (cType == "Textbox") {
                            $scope.Details[i]["isDisabledFromTable"] = true;
                            $scope.Details[i]["isDisabledDisplayCName"] = true;
                            $scope.Details[i]["isDisabledValueCName"] = true;
                            $scope.Details[i]["isDisabledDate"] = true;
                            $scope.Details[i]["TableMessage"] = "";
                            $scope.Details[i]["DisplayNameMessage"] = "";
                            $scope.Details[i]["ValueNameMessage"] = "";
                        } else {
                            $scope.Details[i]["isDisabledFromTable"] = true;
                            $scope.Details[i]["isDisabledDisplayCName"] = true;
                            $scope.Details[i]["isDisabledValueCName"] = true;
                            $scope.Details[i]["isDisabledDate"] = true;
                            $scope.Details[i]["TableMessage"] = "";
                            $scope.Details[i]["DisplayNameMessage"] = "";
                            $scope.Details[i]["ValueNameMessage"] = "";
                        }
                    }
                }
            }
            $scope.ValidateTable = function (table, index) {
                if (table != '') {
                    $http({
                        method: "post",
                        url: "/Home/ValidateTable",
                        params: { tableName: table }
                    }).then(function (response) {
                        for (var i = 0; i < $scope.Details.length; i++) {
                            if (i == index) {
                                if (response.data == '') {
                                    $scope.Details[i]["TableColor"] = "Red";
                                    $scope.Details[i]["TableMessage"] = "Table name not avaliable.";
                                }
                                else {
                                    $scope.Details[i]["TableColor"] = "Green";
                                    $scope.Details[i]["TableMessage"] = "Table name avaliable.";
                                }
                            }
                        }
                    });
                }
                else {
                    for (var i = 0; i < $scope.Details.length; i++) {
                        if (i == index) {
                            $scope.Details[i]["TableMessage"] = "";
                        }
                    }
                }
            }
            $scope.ValidateDisplayName = function (table, index, columnName) {
                if (columnName != '' && table != '') {
                    $http({
                        method: "post",
                        url: "/Home/ValidateColumnName",
                        params: { tableName: table, columnName: columnName }
                    }).then(function (response) {
                        for (var i = 0; i < $scope.Details.length; i++) {
                            if (i == index) {
                                if (response.data == '') {
                                    $scope.Details[i]["DisplayNameColor"] = "Red";
                                    $scope.Details[i]["DisplayNameMessage"] = "Column name not avaliable.";
                                }
                                else {
                                    $scope.Details[i]["DisplayNameColor"] = "Green";
                                    $scope.Details[i]["DisplayNameMessage"] = "Column name avaliable.";
                                }
                            }
                        }
                    });
                }
                else {
                    if (columnName == '') {
                        for (var i = 0; i < $scope.Details.length; i++) {
                            if (i == index) {
                                $scope.Details[i]["DisplayNameMessage"] = "";
                            }
                        }
                    }
                }
            }
            $scope.ValidateValueName = function (table, index, columnName) {
                if (columnName != '' && table != '') {
                    $http({
                        method: "post",
                        url: "/Home/ValidateColumnName",
                        params: { tableName: table, columnName: columnName }
                    }).then(function (response) {
                        for (var i = 0; i < $scope.Details.length; i++) {
                            if (i == index) {
                                if (response.data == '') {
                                    $scope.Details[i]["ValueNameColor"] = "Red";
                                    $scope.Details[i]["ValueNameMessage"] = "Column name not avaliable.";
                                }
                                else {
                                    $scope.Details[i]["ValueNameColor"] = "Green";
                                    $scope.Details[i]["ValueNameMessage"] = "Column name avaliable.";
                                }
                            }
                        }
                    });
                }
                else {
                    if (columnName == '') {
                        for (var i = 0; i < $scope.Details.length; i++) {
                            if (i == index) {
                                $scope.Details[i]["ValueNameMessage"] = "";
                            }
                        }
                    }
                }
            }
        });
    </script>
</head>
<body>
    <div ng-app="MyApp" ng-controller="MyController">
        <table id="tblOrders" class="table table-responsive">
            <tr>
                <th>Cname</th>
                <th>Ctype</th>
                <th>From Table</th>
                <th>DisplayCName</th>
                <th>ValueCName</th>
                <th>Date</th>
            </tr>
            <tbody ng-repeat="detail in Details">
                <tr>
                    <td>{{detail.CName}}</td>
                    <td>
                        <select ng-model="detail.CType" class="form-control" ng-change="TypeChange(detail.CType,detail.CName,$index)">
                            <option value="audi" selected>Select value</option>
                            <option value="Dropdown">Dropdown</option>
                            <option value="Textbox">Textbox</option>
                            <option value="DatePicker">DatePicker</option>
                        </select>
                    </td>
                    <td>
                        <input type="text" class="form-control" ng-model="detail.FromTable" ng-disabled="detail.isDisabledFromTable"
                            ng-keyup="ValidateTable(detail.FromTable,$index)" />
                        <span ng-style="{color:detail.TableColor}" ng-bind="detail.TableMessage"></span>
                    </td>
                    <td>
                        <input type="text" class="form-control" ng-model="detail.DisplayCName" ng-disabled="detail.isDisabledDisplayCName"
                            ng-keyup="ValidateDisplayName(detail.FromTable,$index,detail.DisplayCName)" />
                        <span ng-style="{color:detail.DisplayNameColor}" ng-bind="detail.DisplayNameMessage">
                        </span>
                    </td>
                    <td>
                        <input type="text" class="form-control" ng-model="detail.ValueCName" ng-disabled="detail.isDisabledValueCName"
                            ng-keyup="ValidateValueName(detail.FromTable,$index,detail.ValueCName)" />
                        <span ng-style="{color:detail.ValueNameColor}" ng-bind="detail.ValueNameMessage">
                    </td>
                    <td>
                        <select ng-model="detail.Date" class="form-control" ng-disabled="detail.isDisabledDate">
                            <option value="Today">Today</option>
                            <option value="Lastday">Lastday</option>
                        </select>
                    </td>
                </tr>
            </tbody>
        </table>
    </div>
</body>
</html>

Screenshot