Pick up information using a dropdownlist as select

Question

I need to make a basic supply system, where when selecting the type of fuel, it brings me the last registered value of that product and automatically select and still automatically insert in Textbox the value that the fuel was purchased.

The supply table is only pulling the Product Name (NomeProdutoId) as foreign key, to pull the product, do not know if I will have to create another foreign key to get the value of the Purchase table.

Tabela Compra:

CREATE TABLE [dbo].[Compra] (
[Id]          INT            IDENTITY (1, 1) NOT NULL,
[DtCompra]    DATETIME       NULL,
[Quant]       INT            NULL,
[Tipo]        NVARCHAR (255) NULL,
[VlrUnit]     REAL           NULL,
[VlrTotal]    REAL           NULL,
[NomeId]      INT            NULL,
[NomeProduto] NVARCHAR (255) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK2BFDD8DA794B2666] FOREIGN KEY ([NomeId]) REFERENCES [dbo].[Fornecedores] ([Id])
);

Table Supply:

CREATE TABLE [dbo].[Abastecimento] (
[Id]            INT        IDENTITY (1, 1) NOT NULL,
[DtAbastecido]  DATETIME   NULL,
[Litro]         INT        NULL,
[VlrUnit]       FLOAT (53) NULL,
[TotalGasto]    FLOAT (53) NULL,
[AutorId]       INT        NULL,
[NumCarroId]    INT        NULL,
[Km]            INT        NULL,
[NomeProdutoId] INT        NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK84286500787E6DCB] FOREIGN KEY ([AutorId]) REFERENCES [dbo].[Usuario] ([Id]),
CONSTRAINT [FK842865002F58EAD8] FOREIGN KEY ([NumCarroId]) REFERENCES [dbo].[Veiculo] ([Id]),
CONSTRAINT [FK8428650023D2F9D9] FOREIGN KEY ([NomeProdutoId]) REFERENCES [dbo].[Compra] ([Id])
);

Abastecimento Controller:

[HttpPost]
public ActionResult Adiciona(AbastecimentoModel viewModel)
{
    /* Ver qual produto está sendo selecionado.*/
    var Produto = ckm.ConsultaProduto(viewModel.NomeProdutoId);

    viewModel.TotalGasto = viewModel.Litro * viewModel.VlrUnit;

    if (ModelState.IsValid)
    {
        Abastecimento abastecimento = viewModel.CriaAbastecimento();
        dao.Adiciona(abastecimento);
        //return View();
        return RedirectToAction("Index");
    }
    else
    { 
        ViewBag.Compra = compraDAO.Lista();
        ViewBag.Usuarios = usuarioDAO.Lista();
        ViewBag.Veiculo = veiculoDAO.Lista();
        return View("Form",viewModel);
    }
}

Use class of Queries:

public class ControleKm : Controller
{
    private Rota r;
    private Compra c;
    private ISession session;
    public ControleKm(ISession session, Rota r, Compra c)
    {
        this.c = c;
        this.r = r;
        this.session = session;
    }

    public IList<Rota> Consulta(int NumCarroId)
    {
        string hql = "SELECT r FROM Rota r";
        IQuery query = session.CreateQuery(hql);
        return query.List<Rota>();
    }
    public IList<Abastecimento> ConsultaProduto(int NomeProdutoId)
    {
        string hql = "SELECT a FROM Abastecimento a";
        IQuery query = session.CreateQuery(hql);
        return query.List<Abastecimento>();
    }
    public IList<Abastecimento> ConsultaValor(double VlrUnit)
    {
        string hql = "SELECT a FROM Abastecimento a";
        IQuery query = session.CreateQuery(hql);
        return query.List<Abastecimento>();
    }

}

}

View of Supply:

    @model BlogWeb.ViewsModels.AbastecimentoModel

@Html.ValidationMessageFor(a => a.DtAbastecido)
@Html.LabelFor(a => a.DtAbastecido , "Data Abastecido:")
@Html.TextBoxFor(a => a.DtAbastecido, "{0:dd-MM-yyyy}", new { Type = "date" })

@Html.ValidationMessageFor(a => a.Km)
@Html.LabelFor(a => a.Km, "Quilometragem:")
@Html.TextBoxFor(a => a.Km)

@Html.ValidationMessageFor(a => a.Litro)
@Html.LabelFor(a => a.Litro, "Litros:")
@Html.TextBoxFor(a => a.Litro)

@Html.ValidationMessageFor(a => a.VlrUnit)
@Html.LabelFor(a => a.VlrUnit, "Valor Unit:")
@Html.TextBoxFor(a => a.VlrUnit)

@Html.ValidationMessageFor(a => a.NomeProdutoId)
@Html.LabelFor(a => a.NomeProdutoId, "Escolher Produto:")
@Html.DropDownListFor(a => a.NomeProdutoId, new SelectList(ViewBag.Compra, "Id", "NomeProduto"))

@Html.ValidationMessageFor(a => a.AutorId)
@Html.LabelFor(a => a.AutorId, "Responsavel Abastecimento:")
@Html.DropDownListFor(a => a.AutorId, new SelectList(ViewBag.Usuarios, "Id", "Nome"))

@Html.ValidationMessageFor(a => a.NumCarroId)
@Html.LabelFor(a => a.NumCarroId, "Nº do Carro:")
@Html.DropDownListFor(a => a.NumCarroId, new SelectList(ViewBag.Veiculo, "Id", "NCarro"))

Answer 1

To return all the values you need it is necessary to make one JOIN with the Buy Table. I’ll give you an example, the SELECT below will return all the columns of Supply and Purchase but you can select the ones you need, I preferred to use a LEFT JOINbecause it allows returning not only the related data of the two tables but also the unrelated data found in the table to the left of the clause JOIN. If there is no related data between the tables neither left nor right of the JOIN, the results of all the columns of the selection of the table to the right will be NULL. But you can use the INNER JOIN or RIGHT JOIN feel better.

SELECT a.*, c*
FROM Abastecimento AS a 
LEFT JOIN Compra AS c ON a.NomeProdutoId = c.Id 

In all your other queries, if you want to get the data from the table you are doing the FROMand the table linked to its FOREIGN KEY, you need to make a JOIN