Click here to Skip to main content
Search within:



Next

simplifying a query with not writing the column names
Sign Up to vote bad
good
See more: ASP.NETSQL-ServerWebmatrix 
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:10
cs101000724
Add a Solution

Comments
Member 9581488 - 2 Jan '13 - 13:40
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.

2 solutions

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  
Posted 2 Jan '13 - 8:08
Sheikh Muhammad Haris5.8K
Comments
cs101000 - 2 Jan '13 - 14:36
Thank you! Useful query it is.
Sheikh Muhammad Haris - 2 Jan '13 - 18:34
Welcome :)
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  
Posted 2 Jan '13 - 8:07
OriginalGriff741K
Comments
cs101000 - 2 Jan '13 - 14:34
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:37
It always is! :laugh:
Add a Solution

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 355
1 OriginalGriff 325
2 Arun Vasu 268
3 CPallini 213
4 Tadit Dash 201
0 Sergey Alexandrovich Kryukov 10,005
1 OriginalGriff 7,654
2 CPallini 4,171
3 Rohan Leuva 3,447
4 Maciej Los 2,974


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