Click here to Skip to main content
12,406,795 members (55,238 online)
Rate this:
 
Please Sign up or sign in to vote.
db.Execute(@"
  INSERT INTO DeletedOrders 
  VALUES (@0, @1, @2, @3, @4, @5, @6, @7
  , @8, @9, @10, @11, @12, @13, @14, @15, @16, @17)"
  , AuditNum, qFullOrder.Price, qFullOrder.Status, qFullOrder.PricingTime
  , qFullOrder.WordsNum, qFullOrder.DeliveryTime, qFullOrder.FileName
  , qFullOrder.Lang, qFullOrder.Description, qFullOrder.Time, qFullOrder.Field
  , qFullOrder.Subject, qFullOrder.Gender, qFullOrder.FirstName, qFullOrder.LastName
  , qFullOrder.Cell, qFullOrder.Phone, qFullOrder.Email);

I have a table with 19 columns. The first one is of identity type. I want to perform the above query (with 18 columns) without putting all those 19 column names in the statment
(before values part). Although the first column is identity I get this error:
The number of columns in the query and the table must match.
Posted 2-Jan-13 7:10am
Comments
Member 9581488 2-Jan-13 13:40pm
   
do you have your Identity column's Identity Increment? If you want to insert data without specifying your columnNames use:
insert into tablename(value1,value2,...., so on)
but data types must match.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Always use explicit columns both in the INSERT and in the SELECT projection. Even if you don't want to, you should. Its a good SQL practice.

Just drag and drop the column names from the object browser.

OR

you can use the query below to get List of columns for your table

SELECT c.name + ','  AS column_name 
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE t.name = 'Your_Table_Name'

Reason:
For Instance, If you don't mention column names, then after a long time if you added a new column in your table, then the whole mapping got wrong and all operations would result in breaking your code.




Accept Solution if it helps you.
  Permalink  
Comments
cs101000 2-Jan-13 14:36pm
   
Thank you! Useful query it is.
Sheikh Muhammad Haris 2-Jan-13 18:34pm
   
Welcome :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

If you do not specify column names, then you must provide data for each and every column in the table - otherwise it doesn't know where they are to go. The problem with that is, that if you have any automatic columns, you cannot provide the data because that will cause an error...

It is not a good idea to omit column names anyway - it may seem more work for you, but it is a useful safeguard for your data should anyone change the table layout in future. If you do not specify columns, then data will just be inserted, and could be entered into the wrong columns - which will take a lot longer to sort out than a copy-and-paste job to add the column names to your queries...
  Permalink  
Comments
cs101000 2-Jan-13 14:34pm
   
Thank you! I see that it's a risky practice. I will take your advice.However it's a little more than a copy-paste in my scenario :-)
OriginalGriff 2-Jan-13 14:37pm
   
It always is! :laugh:

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160730.1 | Last Updated 2 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100