Click here to Skip to main content
15,513,143 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
When I try to add values through the interface I get this error: System.Data.SqlClient.SqlException: 'Column name or number of supplied values does not match table definition.'

What I have tried:

C#
private void button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection("Data Source=OPCOSQL\\OPCOSQL;Initial Catalog=OPCO-SQL;Integrated Security=True;Pooling=False");
    con.Open();
    SqlCommand cmd = new SqlCommand("insert into [Table] values(@Utilizador,@Nome,@Disco,@Processador,@Memória,@Tipo,@Localização)", con);
    cmd.Parameters.AddWithValue("@Utilizador", textBox1.Text);
    cmd.Parameters.AddWithValue("@Nome", textBox2.Text);
    cmd.Parameters.AddWithValue("@Disco", textBox3.Text);
    cmd.Parameters.AddWithValue("@Processador", textBox4.Text);
    cmd.Parameters.AddWithValue("@Memória", textBox5.Text);
    cmd.Parameters.AddWithValue("@Tipo", textBox6.Text);
    cmd.Parameters.AddWithValue("@Localização", textBox7.Text);
    cmd.ExecuteNonQuery();
    con.Close();
}


SQL
CREATE TABLE [dbo].[Table] (
    [AssetNumber] INT           NOT NULL IDENTITY,
    [Utilizador]        VARCHAR(50) NULL,
    [Nome]        VARCHAR(50)    NULL,
    [Disco]       VARCHAR(50)           NULL,
    [Processador] VARCHAR(50)  NULL,
    [Memória]     VARCHAR(50)           NULL,
    [Tipo]        VARCHAR(50)    NULL,
    [Localização] VARCHAR(50)    NULL, 
    CONSTRAINT [PK_Table] PRIMARY KEY ([AssetNumber])
);
Posted
Updated 27-Sep-22 0:13am
v2
Comments
Richard MacCutchan 27-Sep-22 4:41am    
What is the table schema?
Rodrigo Mendes 2022 27-Sep-22 4:50am    
CREATE TABLE [dbo].[Table] (
[AssetNumber] INT NOT NULL IDENTITY,
[Utilizador] VARCHAR(50) NULL,
[Nome] VARCHAR(50) NULL,
[Disco] VARCHAR(50) NULL,
[Processador] VARCHAR(50) NULL,
[Memória] VARCHAR(50) NULL,
[Tipo] VARCHAR(50) NULL,
[Localização] VARCHAR(50) NULL,
CONSTRAINT [PK_Table] PRIMARY KEY ([AssetNumber])
);

You don't list the column names in your INSERT command:
SQL
INSERT INTO MyTable VALUES ( ... )
So it expects a value for every column., which means that the engine tries to insert values in the column order of the CREATE or ALTER table that was last executed.
That's a bad idea, because subsequent ALTER TABLE statements can change that and your database integrity gets compromised.
In this case, it's even worse, since the first column is an IDENTITY column, and you can't write to that at all.

List your columns in the order that your parameters will be supplied, and it'll work, and be more future proof:
SQL
INSERT INTO MyTable (Column1, Column2, ... ) VALUES ( ... )



After new code supplied:
If I try your code in SSMS:
SQL
CREATE TABLE [dbo].[Table] (
    [AssetNumber] INT           NOT NULL IDENTITY,
    [Utilizador]        VARCHAR(50) NULL,
    [Nome]        VARCHAR(50)    NULL,
    [Disco]       VARCHAR(50)           NULL,
    [Processador] VARCHAR(50)  NULL,
    [Memória]     VARCHAR(50)           NULL,
    [Tipo]        VARCHAR(50)    NULL,
    [Localização] VARCHAR(50)    NULL, 
    CONSTRAINT [PK_Table] PRIMARY KEY ([AssetNumber])
SQL
insert into dbo.[Table] ([Utilizador], [Nome], [Disco], [Processador], [Memória], [Tipo], [Localização]) values ('@Utilizador', '@Nome', '@Disco', '@Processador', '@Memória', '@Tipo', '@Localização')

Then it inserts perfectly, and I get exactly what I expect when I query the table afterwards:
1	@Utilizador	@Nome	@Disco	@Processador	@Memória	@Tipo	@Localização


So ... start with SSMS, and check that you are using the right DB, the right table, and that the columns match with the CREATE statement you showed us.
 
Share this answer
 
v2
Comments
Rodrigo Mendes 2022 27-Sep-22 5:14am    
Thanks for the answer, but it didn't work and I still get the error
OriginalGriff 27-Sep-22 5:31am    
And what - exactly - did you try?
Rodrigo Mendes 2022 27-Sep-22 5:37am    
private void button1_Click(object sender, EventArgs e)

{
SqlConnection con = new SqlConnection("Data Source=OPCOSQL\\OPCOSQL;Initial Catalog=OPCO-SQL;Integrated Security=True;Pooling=False");
con.Open();
SqlCommand cmd = new SqlCommand("insert into [Table] ([Utilizador], [Nome], [Disco], [Processador], [Memória], [Tipo], [Localização]) values (@Utilizador, @Nome, @Disco, @Processador, @Memória, @Tipo, @Localização)", con);
cmd.Parameters.AddWithValue("@Utilizador", textBox1.Text);
cmd.Parameters.AddWithValue("@Nome", textBox2.Text);
cmd.Parameters.AddWithValue("@Disco", textBox3.Text);
cmd.Parameters.AddWithValue("@Processador", textBox4.Text);
cmd.Parameters.AddWithValue("@Memória", textBox5.Text);
cmd.Parameters.AddWithValue("@Tipo", textBox6.Text);
cmd.Parameters.AddWithValue("@Localização", textBox7.Text);
cmd.ExecuteNonQuery();
con.Close();
}
OriginalGriff 27-Sep-22 6:06am    
Answer updated.
Rodrigo Mendes 2022 27-Sep-22 6:13am    
Thanks again for the reply, I checked SSMS and and made the changes now it shows this error:
System.Data.SqlClient.SqlException: 'Invalid column name 'Utilizador'.'
You are trying to insert seven values into a table with eight columns.

But you won't be able to insert a value for the first column, since it's an IDENTITY column.

Change your INSERT statement to explicitly specify the columns you're inserting into:
SQL
insert into [Table] ([Utilizador], [Nome], [Disco], [Processador], [Memória], [Tipo], [Localização]) values (@Utilizador, @Nome, @Disco, @Processador, @Memória, @Tipo, @Localização)
 
Share this answer
 
Comments
Rodrigo Mendes 2022 27-Sep-22 5:15am    
Thanks for the answer, but it didn't work and I still get the error

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900