INSERT without knowing database column order?

  • Thread starter Thread starter AdamM
  • Start date Start date
A

AdamM

Currently, my code reads from a database without knowing the order of the
columns like this:

string name = (string)datarow["name"];
However, is there a way to write or do an INSERT back to a database without
getting caught up in the order of the columns?

Right now I do something like this, which is delicate and breaks if the
order of the columns changes:

database.ExecuteNonQuery("INSERT INTO dbtable VALUES ('texas', 'austin',
1971)";



Thanks in advance!
 
However, is there a way to write or do an INSERT back to a database
without getting caught up in the order of the columns?
Click to expand...

"INSERT INTO dbtable (state, city, date) VALUES ('texas', 'austin', 1971)";

Obviously, amend the column names accordingly :-)

Otherwise, use a parameterised stored procedure.
 
AdamM said:
Currently, my code reads from a database without knowing the order of the
columns like this:

string name = (string)datarow["name"];
However, is there a way to write or do an INSERT back to a database without
getting caught up in the order of the columns?

Right now I do something like this, which is delicate and breaks if the
order of the columns changes:

database.ExecuteNonQuery("INSERT INTO dbtable VALUES ('texas', 'austin',
1971)";



Thanks in advance!
Click to expand...

You can use the "full" syntax:
INSERT INTO <tablename> (<columnlist>) VALUES (<valuelist>)

for instance (guessing a bit)
INSERT INTO dbtable (state, town, year) VALUES ('texas', 'austin', 1971)

the order of values needs to be the same as the columns ("1971" maps to
"year"), but it is independent of the order in the table definition.
You can also skip columns this way, to have the database use the defined
default values.
 
Back
Top