Get information using a dropdownlist as select

0

I need to make a basic fuel system, where when I select the type of fuel, it brings me the last registered value of that product and automatically selects and automatically inserts the value of the fuel in the TextBox.

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

Purchase Table:

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])
);

Supply table:

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])
);

Supply 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);
    }
}

Queries Usage Class:

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>();
    }

}

}

Supply View:

    @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"))
    
asked by anonymous 10.10.2017 / 21:27

1 answer

1

To return all the values you need, you need to make a JOIN with the purchase table. I'll give you an example, SELECT below will return all columns of Supply and Purchase but you can select the ones you need, I preferred to use LEFT JOIN because it allows you to return not only the related data from the two tables but also the unrelated data found in the table to the left of the JOIN clause. If there is no related data between the tables, neither the left nor the right of JOIN , the results of all the columns in the selection on the right will be NULL . But you can use INNER JOIN or RIGHT JOIN if you 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 where you are doing FROM and the table attached to your FOREIGN KEY , you need to JOIN

    
11.10.2017 / 17:10