System.data.sqlclient.sqlexception: 'column name or number of supplied values does not match table definition.'

Question

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 26-Sep-22 22:35pm

Rodrigo Mendes 2022

Updated 26-Sep-22 23:13pm

Richard MacCutchan

v2

Add a Solution

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])
);

2 solutions

Solution 1

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)

Posted 26-Sep-22 22:59pm

Richard Deeming

Comments

Rodrigo Mendes 2022 27-Sep-22 5:15am

Thanks for the answer, but it didn't work and I still get the error

Add a Solution

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

OriginalGriff · Accepted Answer · 2022-09-26T23:05:00

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.

System.data.sqlclient.sqlexception: 'column name or number of supplied values does not match table definition.'

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

System.data.sqlclient.sqlexception: 'column name or number of supplied values does not match table definition.'

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us