Insert data into a table with Foreign key in the ID

Question

I will try to be as objective as possible so not many codes will put (because the original is really too big). I am making a progress of simple registration that has: - 1 datagridview to select the desired customer from a list of records - 1 datagridview within a tabPage to display the name and address data of the selected customer, with inclusion, change and deletion - 1 datagridview within a tabPage to display the phone data of the selected customer, with inclusion, modification and deletion And for each tabPage has a table created in SQL Server, one with the name and address data and the other with the phone (to add more than one for each customer). What happens is I thought I’d take the ID telephone

Updating: When I go to register a new client, I have the data of both dataGridView who are within two tabPage to fill. On page 1 of information such as name and address and on page 2 the phone information, which can be one or more of one (which can be viewed on dataGridView of this page). I used the foreign key to connect the IDs of the client in both tables but I’m unable to enter new phone numbers.

To illustrate the relevant part of the code to include the phone is this:

objConn = new SqlConnection(strConn);
objComm = new SqlCommand(strCommtblTel, objConn);
objComm.CommandText = "INSERT INTO tblTelefone (Telefone) values (@Telefone)";


try
{
    objConn.Open();

    objComm.Parameters.AddWithValue("@Telefone", txt_telefone.Text);

    dataAdapter.Update(dataSet, "tblTelefone");

    objComm.ExecuteNonQuery(); //retorna o número de linhas afetadas 
    dataSet.Clear(); //limpa os dados no dataSet
    objConn.Close(); //encerra a conexão com o banco de dados 

}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}


private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{

    if (e.RowIndex >= 0)
    {
        txt_nome.Text = dataGridView1.CurrentRow.Cells["Nome"].Value.ToString();
        txt_endereco.Text = dataGridView1.CurrentRow.Cells["Endereço"].Value.ToString();

    }

}

private void dgvTable_Cliente_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
    numLinha = dgvTable_Cliente.CurrentRow.Index;
    clienteID = dgvTable_Cliente.CurrentRow.Cells["ID"].Value.ToString();
    IDCli_atual = clienteID;

    txt_nome.Text = dgvTable_Cliente.CurrentRow.Cells["Nome"].Value.ToString();
    txt_endereco.Text = dgvTable_Cliente.CurrentRow.Cells["Endereço"].Value.ToString();

}


private void dgvTable_Contato_CellContentClick(object sender, DataGridViewCellEventArgs e)
{

    txt_telefone.Text = dgvTable_Contato.CurrentRow.Cells["Telefone"].Value.ToString();

    contatoID = dgvTable_Contato.CurrentRow.Cells["ID"].Value.ToString();
}

//***********

The problem I’m having is that I can’t add new phones or anything else because I need to get the ID from the phone, and I’d like a little help on that because what I’m trying to do isn’t working.

Answer 1

In fact Voce needs to pass the code of the client Voce wants to associate to the phone Voce is inserting, based on its code Voce needs to do this:

Insert the field ID in your INSERT string.

objComm.CommandText = "INSERT INTO tblTelefone (Telefone, ID) values (@Telefone, @ID)";

then Voce needs to assign the value to this parameter, picking up where the ID of the client is in its code..

objComm.Parameters.AddWithValue("@ID", contatoID.ToString());

Here is the final version of your code using ex a textbox( Voce can swap with the selected client on your grid).

objConn = new SqlConnection(strConn);
objComm = new SqlCommand(strCommtblTel, objConn);
objComm.CommandText = "INSERT INTO tblTelefone (Telefone, ID) values (@Telefone, @ID)";


try
{
    objConn.Open();

    objComm.Parameters.AddWithValue("@Telefone", txt_telefone.Text);
    objComm.Parameters.AddWithValue("@ID", contatoID.ToString());

    dataAdapter.Update(dataSet, "tblTelefone");

    objComm.ExecuteNonQuery(); //retorna o número de linhas afetadas 
    dataSet.Clear(); //limpa os dados no dataSet
    objConn.Close(); //encerra a conexão com o banco de dados 

}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}