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

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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