473,320 Members | 1,802 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

INSERT without knowing database column order?

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!


Nov 17 '05 #1
2 1775
"AdamM" <no****@nospam.com> wrote in message
news:bJ********************@comcast.com...
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?


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

Obviously, amend the column names accordingly :-)

Otherwise, use a parameterised stored procedure.
Nov 17 '05 #2
AdamM wrote:
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!


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.
--
Hans Kesting
Nov 17 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
3
by: rdraider | last post by:
I'm doing a data conversion project, moving data from one SQL app to another. I'm using INSERT INTO with Select and have the syntax correct. But when executing the script I get: Server: Msg...
20
by: akej via SQLMonster.com | last post by:
Hi, i have table with 15 columns CREATE TABLE . ( PRIMARY KEY , NULL , NULL , NULL , NULL , (50) NULL , NULL
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
8
by: K.Fawcett | last post by:
I am doing a simple insert into a table and I would like the data to be retrieved in the order it is inserted not by what appears to be alphabetical order. insert into mytbl values('c') insert...
6
by: Kathy Burke | last post by:
Ugh. I'm using the following in an asp.net. I get an Syntax Error in INSERT INTO Statement on line Cmd1.ExecuteNonQuery(). I've made all my database fields text (just to eliminate that as a...
6
by: Roy Gourgi | last post by:
Hi, I am trying to add a row to my table but I get the error message "invalid column name SOBN and BN1" on this statement. Basically, I am trying to add the row into the same table that I am...
11
by: Neo Geshel | last post by:
I have an Access DB, from which I am going to pull images. Each image has an associated ID, but the ID's are not necessarily sequential (some images may have been deleted, leaving gaps in the list...
8
by: nano2k | last post by:
Hi Shortly, I keep invoices in a table. Occasionally, someone will fire the execution of a stored procedure (SP) that performs several UPDATEs against (potentially) all invoices OLDER than a...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.