Insert data into a table with foreign key in ID

Question

Insert data into a table with foreign key in ID

Navigation

#1 by (2 votes)

0

I'll try to be as objective as possible so not many codes will I put (because the original is really too big). I'm doing a simple registration program that has: - 1 datagridview to select the desired customer from a list of records - 1 datagridview within tabPage to display the name and address data of the selected customer, including, change and delete - 1 datagridview within tabPage to display selected customer's phone data, including, change, and delete 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 client). What happens is that I thought about getting ID from the phone

Current: When I am going to register a new client, I have the data of the two dataGridView that are within two tabPage to fill. In page 1 of information such as name and address and page 2 the information of the phone, which can be one or more than one (which can be viewed in dataGridView of this page). I used the foreign key to connect the% s of the client% s to the ID but I can not enter new phone numbers.

To exemplify 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 not add new phones and nothing else besides because I need to get the tables from the phone, and I'd like some help on it because what I'm trying is not working.

sql-server c# datagridview datatable

asked by anonymous 17.03.2017 / 14:36

1 answer

how do I get a value from the textField and pass it to an int variable? Problem with div size when scrolling page

score 2 · Accepted Answer

In fact you need to pass the client code that you want to associate with the phone you are inserting, based on your code you need to do this:

Insert the ID field in your INSERT string.

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

Then you need to assign the value to this parameter, picking up where the customer ID is located in your code ..

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

Here is the final version of your code using as a textbox ex (which you can exchange for the selected client in 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);
}