CODEWITHSUNDEEP
c#sql-serverado.netsqlconnection
Ludson Farah
Ludson Farah

Reputation: 31

Error on SQL connection - C#

I'm having some trouble on connection to my SQL server on my C# application. I have a code that can either insert or update a table of my database. If I update the table, everything go rigth, but if I insert it, I get an error.

Here is the code:

int ID;
conn = new SqlConnection(@"Data Source = SERVER\SQLEXPRESS; Initial Catalog = SQLFiat; User ID = sa; Password = secret");
conn.Open();            

if (cbCarros.SelectedIndex >= 0)
{
    ID = cbCarros.SelectedIndex + 1;
    cmd = new SqlCommand("UPDATE Carros SET ID_Carro=@ID_Carro,Nome=@Nome,Modelo=@Modelo,Combustivel=@Combustivel,Cambio=@Cambio,Portas=@Portas WHERE ID_Carro=@ID_Carro", conn);
    cmd.Parameters.AddWithValue("@ID_Carro", ID);
    cmd.Parameters.AddWithValue("@Nome", tbNome.Text);
    cmd.Parameters.AddWithValue("@Modelo", tbModelo.Text);
    cmd.Parameters.AddWithValue("@Combustivel", cbCombustivel.SelectedItem.ToString());
    cmd.Parameters.AddWithValue("@Cambio", cbCambio.SelectedItem.ToString());
    cmd.Parameters.AddWithValue("@Portas", Int32.Parse(tbPortas.Text));
    cmd.ExecuteNonQuery();
    cmd.Dispose();

    cmd = new SqlCommand("UPDATE Rel_Carro_Apertadeira SET ID_Carro=@ID_Carro,ID_Aper=@ID_Aper WHERE ID_Carro=@ID_Carro", conn);
    cmd.Parameters.AddWithValue("@ID_Carro", ID);
    int ID_Aper = cbApertadeiras.SelectedIndex + 1;
    cmd.Parameters.AddWithValue("@ID_Aper", ID_Aper);
    cmd.ExecuteNonQuery();                
}
else
{
    ID = retornaMaiorID() + 1;               
    cmd = new SqlCommand("INSERT INTO Carros (ID_Carro,Nome,Modelo,Combustivel,Cambio,Portas) Values (@ID_Carro,@Nome,@Modelo,@Combustivel,@Cambio,@Portas)", conn);
    cmd.Parameters.AddWithValue("@ID_Carro", ID);
    cmd.Parameters.AddWithValue("@Nome", tbNome.Text);
    cmd.Parameters.AddWithValue("@Modelo", tbModelo.Text);
    cmd.Parameters.AddWithValue("@Combustivel", cbCombustivel.SelectedItem.ToString());
    cmd.Parameters.AddWithValue("@Cambio", cbCambio.SelectedItem.ToString());
    cmd.Parameters.AddWithValue("@Portas", Int32.Parse(tbPortas.Text));
    cmd.ExecuteNonQuery();
    cmd.Dispose();

    cmd = new SqlCommand("INSERT INTO Rel_Carro_Apertadeira (ID_Carro,ID_Aper) Values (@ID_Carro,@ID_Aper)", conn);
    cmd.Parameters.AddWithValue("@ID_Carro", ID);
    int ID_Aper = cbApertadeiras.SelectedIndex + 1;
    cmd.Parameters.AddWithValue("@ID_Aper", ID_Aper);
    cmd.ExecuteNonQuery();
}

The error is:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll executenonquery requires an open and available connection. the connection's current state is closed

Thanks in advance

Upvotes: 2

Views: 395

Answers (1)

Ludson Farah
Ludson Farah

Reputation: 31

I got the problem, on my function retornaMaiorID() I was closing the connection. Sorry bother you.

Thanks

Upvotes: 1

Related Questions