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):
Here's how it looks now:
dbo.workTS:
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");
}
});
});
response.hrPlanin ajax success function. What do you mean to loghrPlanerror?Sum()instead ofToArray()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?dataindatasetsmust be an array. I have give a solution below. Please check.