How to select the foreign key id in the main table? [duplicate]

Navigation
0
public List Select() { List ListaCaminhao = new List();

        SqlConnection conexao = new SqlConnection(strCon);

        string sql = "Select * from Caminhao;";

        SqlCommand cmd = new SqlCommand(sql, conexao);

        conexao.Open();

        try
        {
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            while (reader.Read())
            {
                Model.Caminhao caminhao = new Model.Caminhao();

        caminhao.IDCaminhao = Convert.ToInt32(reader[0].ToString());

        caminhao.IDMotorista = convert.ToInt32(reader["IDMotorista"].ToString());

This second reader["IDMotorista"] is my secondary key, it's the id I want it to show the name.

    
asked by anonymous 31.03.2016 / 14:31

2 answers

2

What you need is JOIN , to get the name of the driver that is in the child table. 

Select C*, M.NomeMotorista from Caminhao C -- Corrija o M.NomeMotorista para seu campo
join Motorista M
on M.IDMotorista = C.IDMotorista  -- verifique o nome correto das chaves.

You'll also need to change your class caminhao with one more LeaderName property. 

caminhao.NomeMotorista = convert.ToInt32(reader["NomeMotorista "].ToString());
    
31.03.2016 / 19:58
1

Replace the line: 

string sql = "Select * from Caminhao;";

By: 

string sql = "SELECT c.*, m.nome AS nomemotorista FROM caminhao AS c
JOIN motorista AS m ON m.idmotorista = c.idmotorista";

In your 'Model.Caminhao' add also the attribute 'hostname';

On line: 

 caminhao.IDMotorista = convert.ToInt32(reader["IDMotorista"].ToString());

Change to: 

caminhao.nomeMotorista = convert.ToInt32(reader["nomemotorista"].ToString());

In addition, you should also change from 'truck.Motorist' to 'truck.MotoristName' where you load your list to the graphic element.

    
31.03.2016 / 15:17
How to create a static website Search PHP / MySQL (show only result exactly the same as typed)