0

I have troubles with loading data from my Azure SQL database to a chart I'm creating using chartJS. I am trying to fetch data from columns "hrPlan", "hrSub" and "hrFact" for the year 2021 from the "workTS" table, but it is currently working incorrectly.
If I try to get values from hrPlan,hrSub or hrFact in a log directly from chart function like this: console.log("hrPlan=" + hrPlan); I'm getting the following error: Uncaught ReferenceError: hrPlan is not defined I think i didn't properly gave data values to my chart function, but I might be wrong.
Here's how it supposed to look like (all data is hardcoded):enter image description here Here's how it looks now:enter image description here dbo.workTS:enter image description here My code for the workTS model:

public class WorkTS
    {
        public string? org { get; set; }
        public DateTime? dt { get; set; }
        public decimal? hrFact { get; set; }
        public decimal? hrPlan { get; set; }
        public decimal? hrReestr { get; set; }
        public DateTime? dtSynchro { get; set; }
        public DateTime? dtMonth { get; set; }
        public decimal? hrSub { get; set; }
        public decimal? hrFactWSub { get; set; }
    }

Context:

public class AzureDBContext : DbContext
    {
        public DbSet<WorkTS> WorkTs { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<WorkTS>(builder =>
            {
                builder.HasNoKey();
                builder.ToTable("workTS");
            });
        }

        public AzureDBContext(DbContextOptions<AzureDBContext> options)
            : base(options)
        {            
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("{database connection string}");
        }
    }

Controller:

private readonly AzureDBContext _azdb;

        public HomeController(ILogger<HomeController> logger, AzureDBContext azcontext)
        {
            _logger = logger;
            _azdb = azcontext;
        }

        public async Task<ActionResult> OnGetWorkTSData_MTK()  
        {            
            var plan = _azdb.WorkTs.Where(w => w.org == "ООО \"МТК\"").Where(w => w.dtMonth.Value.Year == 2021).Select(w => w.hrPlan).ToArray();
            var sub = _azdb.WorkTs.Where(w => w.org == "ООО \"МТК\"").Where(w => w.dtMonth.Value.Year == 2021).Select(w => w.hrSub).ToArray();
            var fact = _azdb.WorkTs.Where(w => w.org == "ООО \"МТК\"").Where(w => w.dtMonth.Value.Year == 2021).Select(w => w.hrFact).ToArray();
            return new JsonResult(new { hrPlan = plan, hrSub = sub, hrFact = fact });
        } 

And finally, code for the chart:

            <div>
                <canvas id="workTSChart"></canvas>
            </div>
            <script>console.log("This is first debug line");</script>
            <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
            <script>
                $(document).ready(function () {
                    $.ajax({
                        type: "Get",
                        url: '@Url.Action("OnGetWorkTSData_MTK","home")',
                        contentType: "application/json",
                        dataType: "json",
                        success: function (response) {                            
                            var data = response.hrPlan;
                            var data2 = response.hrSub;
                            var data3 = response.hrFact;
                                                        
                            var ctx = document.getElementById("workTSChart");
                            var workTSChart = new Chart(ctx, {
                                type: 'bar',
                                data: {
                                    labels: [
                                        'ООО "МТК"',
                                    ],
                                    datasets: [{
                                        label: 'Plan hours',
                                        data: data,
                                        backgroundColor: 'rgb(161, 221, 239)',
                                        borderColor: 'rgb(161, 221, 239)',
                                    },
                                    {
                                        label: 'Sub hours',
                                        data: data2,
                                        backgroundColor: 'rgb(254, 171, 133)',
                                        borderColor: 'rgb(254, 171, 133)',
                                    },
                                    {
                                        label: 'Fact hours',
                                        data: data3,
                                        backgroundColor: 'rgb(127, 137, 138)',
                                        borderColor: 'rgb(127, 137, 138)',
                                    }]
                                },
                                options: {
                                    responsive: true,
                                    plugins: {
                                        legend: {
                                            position: 'top',
                                        },
                                        title: {
                                            display: true,
                                            text: 'Test chart'
                                        }
                                    }
                                },
                            });

                        },
                        error: function (response) {
                            alert(response.responseText);
                            console.log("This is ERROR line");
                        }
                    });
                });
4
  • Hi @witchgen, your code works well in my project. And it could get the correct value by response.hrPlan in ajax success function. What do you mean to log hrPlan error? Commented Jul 15, 2021 at 2:27
  • @Rena by logging hrPlan error I meant writing the value of hrPlan to log so I can then read it in google chrome's developer log tool. Also, I realized my mistake. I should've used Sum() instead of ToArray() invar plan = _azdb.WorkTs.Where(w => w.org == "ООО \"МТК\"").Where(w => w.dtMonth.Value.Year == 2021).Select(w => w.hrPlan).ToArray(); Now I have correct values, but they do not display in my chart for some reason (chart itself is present, but it's empty). How can I fix this? Commented Jul 15, 2021 at 6:54
  • 1
    Hi @witchgen, you could check this document. data in datasets must be an array. I have give a solution below. Please check. Commented Jul 15, 2021 at 7:08
  • Thank you, I changed it and now it is working as intended. Also thanks for the link. Commented Jul 15, 2021 at 7:35

1 Answer 1

1

If the backend is like below, the data you get is just a simple decimal.

var plan = _azdb.WorkTs.Where(w => w.org == "ООО \"МТК\"")
                       .Where(w => w.dtMonth.Value.Year == 2021)
                       .Select(w => w.hrPlan)
                       .Sum();

data in datasets should be an array. You need change like below:

datasets: [{
    label: 'Plan hours',
    data: [data],    //change here...
    backgroundColor: 'rgb(161, 221, 239)',
    borderColor: 'rgb(161, 221, 239)',
},
{
    label: 'Sub hours',
    data: [data2],     //change here...
    backgroundColor: 'rgb(254, 171, 133)',
    borderColor: 'rgb(254, 171, 133)',
},
{
    label: 'Fact hours',
    data: [data3],    //change here...
    backgroundColor: 'rgb(127, 137, 138)',
    borderColor: 'rgb(127, 137, 138)',
}]
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.