Click here to Skip to main content
15,868,120 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
One of our clients in Russia is reporting this error: "Column name or number of supplied values does not match table definition." This is happening on an INSERT command being sent to SQL Server. When we look at the command in SQL Server Profiler we see numeric fields having a comma in place of the decimal point. This is a parameterized INSERT query being sent using ADO. Our application is in Delphi, but the language should be irrelevant. The Delphi ADO components are direct interfaces to the native ADO objects - basically just wrappers calling the corresponding methods in the ADO interface.

Our actual insert has over 200 columns. For the sake of clarity in this post, I've pared it down to 4 columns. The table has this schema:

CREATE TABLE [dbo].[equip](
[ac_main_mccb_no] [varchar](50) NULL,
[ac_sub_mccb_no] [varchar](50) NULL,
[design_pressure] [numeric](9, 4) NULL,
[design_temp] [numeric](4, 0) NULL)


The INSERT statement looks like this:

INSERT INTO equip
(ac_main_mccb_no,ac_sub_mccb_no,design_pressure,design_temp) 
VALUES (:ac_main_mccb_no,:ac_sub_mccb_no,:design_pressure,:design_temp)


When the client looks at the insert in SQL Server Profiler they see this in the trace:
exec sp_executesql N'INSERT INTO equip
(ac_main_mccb_no,ac_sub_mccb_no,design_pressure,design_temp) 
VALUES (@P1,@P2,@P3,@P4)',
N'@P1 varchar(50),@P2 varchar(50),@P3 float,@P4 float',
NULL,NULL,3,5,380


The value we're passing for design_pressure is 3.5 but is showing up in the trace as "3,5". This is happening in all their float parameters. The SQL shown here is clearly wrong, but we don't produce this SQL. This is just what the trace in SQL Server Profiler is showing.

I imagine this is somehow related to the fact that in Russia the decimal separator is the comma, but our code is not formatting these strings. They're just parameters passed as floating point values to the ADO interface. I believe this is borne out by the parameter datatype being float, and the lack of quote characters around the field, as string parameters have.

This worked before the customer made some change. They're blaming an upgrade of our software, but I believe this is very unlikely, since we have been using the same ADO query for this insert since 2013. We suspect some change in their database configuration but we don't know where to look.

From the viewpoint of an application developer, the path from client application ADO component to SQL Server instance on the server is a "black box." It appears that somewhere along that path our parameters are being corrupted. Can someone with expertise in this area can give us a clue where to look for anything that could cause this behavior?

The customer said they tried changing the Windows setting for decimal separator to the period, but it made no difference. I've also tried setting my decimal separator to comma in an attempt to duplicate their issue. It had no effect on my machine either.

What I have tried:

The customer said they tried changing the Windows setting for decimal separator to the period, but it made no difference. I've also tried setting my decimal separator to comma in an attempt to duplicate their issue. It had no effect on my machine either.
Posted
Updated 11-Feb-22 10:59am
v5
Comments
_Asif_ 10-Feb-22 0:20am    
The error "Column Name or Number of Supplied values do not match table definition" is pretty straight forward. It simply means you have provided let's say 50 field names but you have provided 49 or 51 field values or provided field names do not match any fields in table. I don't think decimal/comma is an issue here because decimal/comma issue cause a different error.

Check your Insert SQL and see how many fields name and values have been passed.
Richard Deeming 10-Feb-22 4:50am    
The value we're passing for design_pressure is 3.5 but is showing up here as "3,5".

That suggests you're not passing parameters properly; you're concatenating the parameter values directly into the SQL command. That will leave you vulnerable to SQL Injection[^].

If you use parameters properly, then the problem will be solved. There will be no way for SQL to confuse a value (3.5) with executable code.
ronschuster 10-Feb-22 14:02pm    
The query is built in code looping through the list of fields needed in the insert statement. The same list is looped for both the column name list and the values list. It would be virtually impossible for them not to match. This is not to mention that this query is running at many sites and this is the only customer reporting this issue.
ronschuster 10-Feb-22 14:07pm    
We are NOT concatenating the parameter values directly into the SQL command. This is a parameterized query, which is at the heart of the entire issue. Please see my updated post and see if that is more clear now.
Richard Deeming 11-Feb-22 4:43am    
And yet you're clearly NOT using parameters:
exec sp_executesql N'...', N'...', NULL, NULL, 3,5, 380

The NULL, NULL, 3,5, 280 values are NOT parameters; they are literal values, which you have concatenated into your SQL query.

We can't help you - you need to look at the whole query, and count the number of fields in the query, the number of parameters you are supplying, and the number of columns in the table: and with a redacted query like you show, we can't help you with that.

And your parameter supply looks ... odd, but that may be a feature of your redaction, it's not really clear.
 
Share this answer
 
Comments
ronschuster 10-Feb-22 14:12pm    
The parameter supply (I assume you mean the list of the actual values of the parameters) DOES look odd. That's kinda the whole problem. We just assign the numeric values to the parameter objects using their numeric values. We don't build that string. That is a function of SQL Server under the hood that I have no control over.
There was absolutely nothing wrong the insert statement. The presence of the commas was totally a red herring. The problem was buried in an insert trigger that called another insert trigger, triggers that the customer had but we did not, which is why we couldn't replicate.

I very highly recommend for any developer using SQL programmatically to read this excellent blog post explaining SQL Server parameters in depth.
Why Parameters are a Best Practice
You'll never look at a trace in SQL Server Profiler the same way again.
 
Share this answer
 
Comments
Richard Deeming 14-Feb-22 11:15am    
So (part of) the code was not passing parameters correctly, as I said at the start. It just wasn't your code that was the problem. :)

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