Click here to Skip to main content
11,578,524 members (60,814 online)
Rate this: bad
good
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 at 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 at 2-Jan-13 14:36pm
   
Thank you! Useful query it is.
Sheikh Muhammad Haris at 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 at 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 at 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
0 Peter Leow 175
1 Richard MacCutchan 165
2 OriginalGriff 129
3 Afzaal Ahmad Zeeshan 90
4 Frankie-C 75
0 OriginalGriff 904
1 Sergey Alexandrovich Kryukov 715
2 Abhinav S 573
3 F-ES Sitecore 420
4 Suvendu Shekhar Giri 393


Advertise | Privacy | Mobile
Web04 | 2.8.150603.1 | Last Updated 2 Jan 2013
Copyright © CodeProject, 1999-2015
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