locked
How to use Ajax to do a database search based on Column and Row values? RRS feed

  • Question

  • User2142845853 posted

    Have an MVC app, and a table with data like this:

    dbo.ParameterTable

    MainThrust   AST1  AST2   AST3     AST4  AST5
    100           2      2      2      2     2
    200           3.4    3.7    4.1    4.4   5.2
    500           5      5.6    6.5    7.4   8
    1000          8      9.4    11.2   14.4  16
    5000         12      14.1    18    20    25
     

    Textbox 1 (AST Value)

    Textbox 2 (Mainthrust value)

    Textbox 3 resulting value

    So if the user enters 2 in textbox 1, this means its value is AST2;  then the user enters 500 in Mainthrust, now we have the x and y for this table.  How in the view can you use Ajax to send these 2 values into the controller function?    And what would the logic be for LINQ to get the value?  

    function (ValX, ValY)

    var resultvalue =  (from  TempVal in db.ParameterTable

        where TempVal.Mainthrust == ValX

        select new

            {  AST1 }.ToList());

    back in the view

     ajax   

          success(data)

    var x = JSON.Parse(data);

    $('#MyTextbox3').val(x[0].AST1);

    Ajax in the View does the call.  I already have the trap for catching both textboxes in the View,

        $('#Mainthrust').change(function () {
    

    when the user adds a value and clicks away from that textbox it comes into the javascript and I have made the code to get the value in the 2 textboxes.

    But now?  sending these 2 values by ajax and expecting it to return the value within the table there?  the column AST2 and Mainthrust value = 200 must return the lookup value of "3.7"

    Seems like a useful design pattern, but I have not done much with ajax, thanks for any ideas

    Thursday, November 17, 2016 6:52 PM

Answers

  • User475983607 posted

    rogersbr

    Im making one IF statement per AST column but how to return the results?  so difficult to make this simple concept work. Has to be some magical combination to get the value and return them back.  Visual Studio/C# could be improved here

    That's like a artist blaming the canvas and paint...

    IMHO, a multi-dimensional array is a better way to represent the matrix data.  The following is a basic example.  Keep in mind, I'm using JQuery in the layout.

    Controllers

            [HttpGet]
            public ActionResult Search()
            {
                return View();
            }
    
    
            [HttpPost]
            public decimal Search(int MainThrust, int AST)
            {
                //Raw Data
                decimal[,] matrix = new decimal[,]
                {
                    {3.4m,  3.7m,   4.1m,   4.4m,   5.2m },
                    {5m,    5.6m,   6.5m,   7.4m,   8m },
                    {8m,    9.4m,   11.2m,  14.4m,  16m, },
                    {12m,   14.1m,  18m,    20m,    25m },
                    {26m,   27m,    28m,    29m,    30m }
                };
    
                //Get the row
                Dictionary<int, int> MainThrustMap = new Dictionary<int, int>()
                {
                    {100,   0},
                    {200,   1},
                    {500,   2},
                    {1000,  3},
                    {5000,  4}
                };
    
                //Get the column index
                //and the row index
                int col = AST;
                int row = MainThrustMap[MainThrust];
    
                return matrix[row, col];
    
            }

    View

    @{
        ViewBag.Title = "Search";
    }
    
    <h2>Search</h2>
    
    @using (Html.BeginForm("Search", "Default", FormMethod.Post))
    {
        <div style="padding:5px;">
            <span style="min-width:100px;display:inline-block;">AST:</span>
            <input id="AST" name="AST" type="text" value="1" />
        </div>
        <div style="padding:5px;">
            <span style="min-width:100px;display:inline-block;">Mainthrust:</span>
            <input id="Mainthrust" name="Main Thrust" type="text" value="200" />
        </div>
        <div style="padding:5px;">
            <input id="Search" type="submit" value="Search" name="Search" />
        </div>
    }
        <hr />
        <div style="padding:5px;">
            <span style="min-width:100px;display:inline-block;">Result:</span>
            <input id="result" name="result" type="text" />
        </div>
    
    @section Scripts {
        <script>
            $(function () {
    
                //Buttom click handler
                $('#Search').click(function (e) {
                    e.preventDefault();
    
                    //Build the method parameters
                    //Search(int MainThrust, int AST)
                    var data = {};
                    data.Mainthrust = $('#Mainthrust').val();
                    data.AST = $('#AST').val();
    
                    console.log(JSON.stringify(data));
    
                    //call the action method
                    //and update the DOM on done
                    $.ajax({
                        type: "POST",
                        url: "@Url.Action("Search")",
                        contentType: "application/json; charset=utf-8",
                        dataType: "json",
                        data: JSON.stringify(data)
                    }).done(function (result) {
                        console.log(result.d);
                        $('#result').val(result);
                    });
                });
    
            });
        </script>
    }

    A few things to consider.  The dbo.ParameterTable has logical errors as shown.  This might be throwing you off.

    SQL server is mathematical.  Rather than hard coding dbo.ParameterTable look into cross reference relationships or pivot.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 19, 2016 12:45 AM
  • User475983607 posted

    thanks! that's a good solution.  But in my case the data has to be in a table that can be updated by outside users.

    Then I would store the data in format that's more appropriate for a record lookup.

    MainThrust	AST	Value
    100		1	2
    100		2	2
    100		3	2
    100		4	2
    100		5	2
    200		1	3.4
    200		2	3.7
    200		3	4.1
    200		4	4.4
    200		5	5.2

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 21, 2016 5:48 PM

All replies

  • User1771544211 posted

    Hi rogersbr,

    Please take the following code as reference.

    View page and javascript code:

    <form>
        AST : <input id="AST" name="AST" type="text" /><br/>
        Mainthrust :<input id="MainThrust" name="MainThrust" type="text" /><br/>
        Result : <input id="Result" name="Result" type="text" /><br />  
        <span id="info"></span>
        <input type="button" value="Search" id="Search" />
    </form>
    
    @section scripts{
        <script>
            $(document).ready(function () {
                $("#Search").click(function () {
                    var ast = $("#AST").val();
                    var mainthrust = $("Mainthrust").val();
                    $.ajax({
                        url: '/Home/Search',
                        type: 'POST',
                        data: {
                            AST: ast,
                            Mainthrust: mainthrust
                        },
                        success: function (data) {
                            $("#Result").val(data);
                        },
                        error: function () {
                            $('#info').html('<p>An error has occurred</p>');
                        },
                    })
                })
            })
        </script>    
    }

    Controller Code :

    [HttpPost]
    public string Search(string Mainthrust,string AST)
    {
        var parameters = from TempVal in db.ParameterTable
                         where TempVal.Mainthrust == Mainthrust //if your Mainthrust is int, you need to conver the string Mainthrust to int 
                         select TempVal;
        //Use Reflection to get the ASTX filed value
        PropertyInfo pinfo = typeof(Parameter).GetProperty("AST" + AST);
        var result = pinfo.GetValue(parameters.FirstOrDefault(), null);
    
        return result.ToString();
    }
    

    Best Regards,

    Jean

    Friday, November 18, 2016 9:46 AM
  • User2142845853 posted

    Hi Jean

    In the controller code, it never gets to the return statement, it stops running  after

        PropertyInfo pinfo = typeof(Parameter).GetProperty("AST" + AST);
        var result = pinfo.GetValue(parameters.FirstOrDefault(), null);
    

    var result =   after this line the code seems to return, no error.  I added a 2nd line,

     var result2 = pinfo.GetValue(parameters.FirstOrDefault(), null);
    

    but execution never gets here.  it does the first one and goes away.  and the typeof(Parameter) ?  what is that?   I guess that its part of:  using DocumentFormat.OpenXml.Drawing.Diagrams;

    looks like its just Visual Studio's bug once again.  Every month or so I manage to crash VS2015 and have to reinstall it because its so weak.  

    it skips breakpoints and seems to ignore code rules today.  Now I have to stop and uninstall and re install?  going to take 8 hours.  piece of junk

    Friday, November 18, 2016 4:42 PM
  • User2142845853 posted

    Just cannot make that work.  How is the propertyInfo supposed to work?  

    The problem I faced here is that the other column must be literally declared, cannot use an If/else.  and CANNOT use a string literal.  Your example would somehow find the other value directly, which wont work on my side so far;

    Im making one IF statement per AST column but how to return the results?  so difficult to make this simple concept work. Has to be some magical combination to get the value and return them back.  Visual Studio/C# could be improved here

    Friday, November 18, 2016 5:52 PM
  • User475983607 posted

    rogersbr

    Im making one IF statement per AST column but how to return the results?  so difficult to make this simple concept work. Has to be some magical combination to get the value and return them back.  Visual Studio/C# could be improved here

    That's like a artist blaming the canvas and paint...

    IMHO, a multi-dimensional array is a better way to represent the matrix data.  The following is a basic example.  Keep in mind, I'm using JQuery in the layout.

    Controllers

            [HttpGet]
            public ActionResult Search()
            {
                return View();
            }
    
    
            [HttpPost]
            public decimal Search(int MainThrust, int AST)
            {
                //Raw Data
                decimal[,] matrix = new decimal[,]
                {
                    {3.4m,  3.7m,   4.1m,   4.4m,   5.2m },
                    {5m,    5.6m,   6.5m,   7.4m,   8m },
                    {8m,    9.4m,   11.2m,  14.4m,  16m, },
                    {12m,   14.1m,  18m,    20m,    25m },
                    {26m,   27m,    28m,    29m,    30m }
                };
    
                //Get the row
                Dictionary<int, int> MainThrustMap = new Dictionary<int, int>()
                {
                    {100,   0},
                    {200,   1},
                    {500,   2},
                    {1000,  3},
                    {5000,  4}
                };
    
                //Get the column index
                //and the row index
                int col = AST;
                int row = MainThrustMap[MainThrust];
    
                return matrix[row, col];
    
            }

    View

    @{
        ViewBag.Title = "Search";
    }
    
    <h2>Search</h2>
    
    @using (Html.BeginForm("Search", "Default", FormMethod.Post))
    {
        <div style="padding:5px;">
            <span style="min-width:100px;display:inline-block;">AST:</span>
            <input id="AST" name="AST" type="text" value="1" />
        </div>
        <div style="padding:5px;">
            <span style="min-width:100px;display:inline-block;">Mainthrust:</span>
            <input id="Mainthrust" name="Main Thrust" type="text" value="200" />
        </div>
        <div style="padding:5px;">
            <input id="Search" type="submit" value="Search" name="Search" />
        </div>
    }
        <hr />
        <div style="padding:5px;">
            <span style="min-width:100px;display:inline-block;">Result:</span>
            <input id="result" name="result" type="text" />
        </div>
    
    @section Scripts {
        <script>
            $(function () {
    
                //Buttom click handler
                $('#Search').click(function (e) {
                    e.preventDefault();
    
                    //Build the method parameters
                    //Search(int MainThrust, int AST)
                    var data = {};
                    data.Mainthrust = $('#Mainthrust').val();
                    data.AST = $('#AST').val();
    
                    console.log(JSON.stringify(data));
    
                    //call the action method
                    //and update the DOM on done
                    $.ajax({
                        type: "POST",
                        url: "@Url.Action("Search")",
                        contentType: "application/json; charset=utf-8",
                        dataType: "json",
                        data: JSON.stringify(data)
                    }).done(function (result) {
                        console.log(result.d);
                        $('#result').val(result);
                    });
                });
    
            });
        </script>
    }

    A few things to consider.  The dbo.ParameterTable has logical errors as shown.  This might be throwing you off.

    SQL server is mathematical.  Rather than hard coding dbo.ParameterTable look into cross reference relationships or pivot.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 19, 2016 12:45 AM
  • User2142845853 posted

    thanks! that's a good solution.  But in my case the data has to be in a table that can be updated by outside users.  I did make a custom list

    string tty {get; set;}  outside the class

    var mydata = (from fpa in db.Parameter select fpa.MainThrust).ToList();
    
    var x0 = convert.ToInt32(mydata[0]);
    
    var x1 = convert.ToInt32(mydata[1]);
    
    //repeat this for all rows in the table.
    
    var YY = convert.ToInt32(MainThrust);
    
    var MTx = x0;
    
    if (YY > x0 && YY <= x1) MTx = x1;
    
    if (YY > x1 && YY <= x2) MTx = x2;

    //repeat for all rows

    var MTfinal = MTx.ToString();

    var params = (from TempV in db.Parameter
    where TempV.MainThrust == MTfinal
    select new
    { TempV }).ToList();


    var astrx = params[0];

    if (AST == '1') {tty = astrx.TempV.AST1;}
    if (AST == '2') {tty = astrx.TempV.AST2;}
    //repeat for all columns


    var finalval = tty;

    return(finalval);

    //within the ajax:

    success: function (data) {
    $('#result').val(data);
    
    

    I should have indicated that in the LINQ you cannot substitute a string literal for the command syntax, making it difficult;  or if there's a way it was not obvious.  this was a first pass at functionality, the use of foreach may be able to streamline

    Monday, November 21, 2016 4:38 PM
  • User475983607 posted

    thanks! that's a good solution.  But in my case the data has to be in a table that can be updated by outside users.

    Then I would store the data in format that's more appropriate for a record lookup.

    MainThrust	AST	Value
    100		1	2
    100		2	2
    100		3	2
    100		4	2
    100		5	2
    200		1	3.4
    200		2	3.7
    200		3	4.1
    200		4	4.4
    200		5	5.2

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 21, 2016 5:48 PM