| Author |
Topic |
|
MikeB
Constraint Violating Yak Guru
387 Posts |
 Posted - 2004-04-21 : 18:54:11
|
Is it possible to update a field using a column index?E.G., UPDATE tbMyTable SET 1 = 100 WHERE pkField = 1 Mike B |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-21 : 19:04:36
|
| What do you mean by column index? Do you mean pkField would be the name of the constraint? If so, then no, you must refer to the column by its name or if it is a constraint with multiple columns, then you must specify all column names.Tara |
 |
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-04-21 : 19:19:56
|
quote:
Originally posted by tduggan
What do you mean by column index? Do you mean pkField would be the name of the constraint? If so, then no, you must refer to the column by its name or if it is a constraint with multiple columns, then you must specify all column names.Tara
Column index meaning Column NumberMyTable------------------------------------pkField (0) | Column1 (1) | Column2 (2) |----------------------------------------- 1 | 0 | 0Now becomes ------------------------------------pkField (0) | Column1 (1) | Column2 (2) |----------------------------------------- 1 | 100 | 0 Impossible huh? Too bad, it would be perfect for front end applications if you could simply update the column basedits Column number?Mike B |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-21 : 19:25:18
|
| But what if the order of columns changed? Then you'd have to figure out which column you really were referring to.Tara |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-04-21 : 19:30:25
|
quote:
Originally posted by tduggan
But what if the order of columns changed? Then you'd have to figure out which column you really were referring to.Tara
Just to kill the cat, how would the order of the columne change?I create a list control with a returned empty result set, then I use an index to get its name, so why can't I use an index to populate it? _RecordsetPtr pRs = pCmd->Execute(NULL, NULL, adCmdStoredProc); if(pRs) { int nFields = pRs->Fields->GetCount(); _variant_t vIndex; vIndex.vt = VT_I4; for(int i = 0; i < nFields; i++) { vIndex.lVal = i; _variant_t vFieldName = pRs->Fields->GetItem(vIndex)->Name; m_cTemplates.InsertColumn(vIndex.lVal + 2, (CString)vFieldName.bstrVal, LVCFMT_LEFT, 47, FALSE, TRUE, dtDouble); } }Mike B |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-21 : 19:38:22
|
| I thought you were referring to the ordinal position of the column in the table.Tara |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-04-21 : 21:26:43
|
| He is referring to that.You can do it in your client code. You demonstrated a way to get the field name from the position in code, use that same logic to create the necessary sql calls to update.Damian |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-04-21 : 22:01:43
|
quote:
Originally posted by tduggan
I thought you were referring to the ordinal position of the column in the table.Tara
;) I was refering to that, your answer was right, I was just wondering what would make the ordinal position of column to change?quote:
He is referring to that.You can do it in your client code. You demonstrated a way to get the field name from the position in code, use that same logic to create the necessary sql calls to update.
Your right, but I don't have an open recordset at the point of updating. I update using a _CommandPtr to call a stored procedure. I do this because I need an output value from the store proc after the field is successfully updated.I thought it might be possible to get the field name in a stored proc based on the oridinal position, or even update base on the postion, but I guess not.Mike B |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-21 : 23:06:23
|
| If you used an identity column and never deleted from the table it would be possible. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-04-21 : 23:33:51
|
He is talking about column position not row number  Damian |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-22 : 12:26:53
|
| Some people just want columns moved in a table. The ordering doesn't really matter, but some people like to have certain columns moved up occassionally, regardless if I tell them that the order doesn't matter.Tara |
|
|
|
|
Update fields using column index?