Insert data into a table with Foreign key in the ID

Asked

Viewed 523 times

0

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.

  • You have the customer id?

  • Your question is not bad, but this missing information to understand better, see well, you have the customer id and are searching all his phones? If you are going to change one of these phones that is already saved then automatically you will already have its id. Now if you are trying to create a new phone and saved in the bank you will have to inform the customer ID on Insert. Another thing I don’t understand what you do with dataAdapter.Update ???? Edit your question in more detail.

1 answer

2


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);
}
  • So in case I will need to enter the customer ID (which already exists from the other table) I want to add the phone?

  • This depends on the usability of your screen. Normally, Voce selects the client who wants to add the phone to, when selecting the client, Voce will have the ID of it. (If you do not select the customer, you cannot assign the phone to a specific customer)

  • yes, but as Voce did not put the code of your grid, I have no way to put it. Then I put a textbox as an example. but it is only exchange by grid

  • I updated, have a look please?

  • You are already playing the value for the variable contatoID, or the IDCli_atual depending on which of the two systems is storing your client code

  • Gave reference error of undefined object for an instance of an object

  • the moment Voce will execute the insertion code, its variable contatoID must have the value filled, for this to happen, you must have selected the line before (in case you have clicked on dgvTable_Contato) to carry this variable.

  • can you view the chat please?

Show 4 more comments

Browser other questions tagged

You are not signed in. Login or sign up in order to post.