Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Updating a table/field using the ordinal number
Posted on 2004-09-14
Good Morning SQL-Senseis,
I was wondering if it is possble to right a Transact-Sql statement that uses the Ordinal Number of a field to update a table.
My code will be passing the Ordinal Number to a Update function. I would simply like to use that number dynamically to create the field being updated.
The statement is being created in VB.Net and then being passed to the SQL Server
Thanx
I was wondering if it is possble to right a Transact-Sql statement that uses the Ordinal Number of a field to update a table.
My code will be passing the Ordinal Number to a Update function. I would simply like to use that number dynamically to create the field being updated.
The statement is being created in VB.Net and then being passed to the SQL Server
Thanx
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4-
4
-
4
12 Comments
This works with the Northwind database
Table name is Orders
and notice the where clause in the function
where orderid=11077
Jay
declare @colname varchar(55)
declare @ordpos int
set @ordpos=6
select @colname=column_name from INFORMATION_SCHEMA.Columns
where table_name='orders'
and ordinal_position=@ordpos
exec ('update orders
set '+@colname+'=getdate() where orderid=11077')
Table name is Orders
and notice the where clause in the function
where orderid=11077
Jay
declare @colname varchar(55)
declare @ordpos int
set @ordpos=6
select @colname=column_name from INFORMATION_SCHEMA.Columns
where table_name='orders'
and ordinal_position=@ordpos
exec ('update orders
set '+@colname+'=getdate() where orderid=11077')
I am building a Content editor that will read the field to be edited dynamically. It is easier and cleaner to simply send over the Ordinal. I could send over the field name but that can get ugly.
If I can simply update to a field by it's ordianl number it would simplify the Update code
If I can simply update to a field by it's ordianl number it would simplify the Update code
also since you are using VB you could simply select the record using an updatable
cursor, and then set the value by ordinal number.
Jay
cursor, and then set the value by ordinal number.
Jay
Active today
>>It is easier and cleaner to simply send over the Ordinal.
Maybe it looks like it's a good idea, but it isn't. You have to take care of the data type, and using varchar or whatever else as common type to update ANY database type will be MUCH MORE UGLY than passing a column name (which will ALYWAYS be varchar).
As jltoops indicates, this will probably be much easier using VB:
dim rs as ADODB.Recordset
set rs = new ADODB.Recordset
rs.open (yourconnection, "SELECT * FROM YOURTABLE WHERE ... ", <options for a keyset cursor>)
rs.fields(yourordinalnumbe
rs.update
set rs = nothing
+ the errorhandling bla bla rhabarber rhabarber ...
+ check out the fields collection base number, i never remember if it's 0 or 1 :-)
Cheers
Maybe it looks like it's a good idea, but it isn't. You have to take care of the data type, and using varchar or whatever else as common type to update ANY database type will be MUCH MORE UGLY than passing a column name (which will ALYWAYS be varchar).
As jltoops indicates, this will probably be much easier using VB:
dim rs as ADODB.Recordset
set rs = new ADODB.Recordset
rs.open (yourconnection, "SELECT * FROM YOURTABLE WHERE ... ", <options for a keyset cursor>)
rs.fields(yourordinalnumbe
rs.update
set rs = nothing
+ the errorhandling bla bla rhabarber rhabarber ...
+ check out the fields collection base number, i never remember if it's 0 or 1 :-)
Cheers
updatable cursor???
Here is the structure of what is happening:
A catalog page is generted in admin mode with a dynamic link that contains the info of the field being displayed.
The edit page it lead to reads the querystring and queries the field from the table.
The user edits the data in a text editor (FreeTextBox) and hits saved.
At this point the page goes to update this field using the ordinal id I had stored on the page.
Since there was only one record ever called I do not understand where a cursor comes in (my possible ignorance)
Here is the structure of what is happening:
A catalog page is generted in admin mode with a dynamic link that contains the info of the field being displayed.
The edit page it lead to reads the querystring and queries the field from the table.
The user edits the data in a text editor (FreeTextBox) and hits saved.
At this point the page goes to update this field using the ordinal id I had stored on the page.
Since there was only one record ever called I do not understand where a cursor comes in (my possible ignorance)
Active today
Even a recordset with 1 record becomes a cursor.
The problem with using a pure dynamic SQL as suggested above (i am sure jltoops agrees) are the negative side-effects:
* less performance
* security issues
Regarding the security issue, take this sample (pseudo-code, won't work exaclty like this):
exec ('update YOURTABLE set '+@colname+'=' + @colvalue +' where PK=' + @PK_value )
What will happen if the user will submit the following value for the column
"'anyvalue' DROP DATABASE select * from YOURTABLE "
The resulting sql will be this:
update YOURTABLE set yourcolumn="anyvalue" DROP DATABASE SELECT * FROM YOURTABLE where PK= 7
Funny, you allowed your user to drop the database :-)
Actually, your generic dynamic SQL will error out because if the last SELECT, because meanwhile the database has been dropped...
Don't think this is only imagination, too many shopping web sites have been hacked/destroyed this way !!!!
Using the sample code I posted, such things cannot occur, as ADO will care about all this.
CHeers
The problem with using a pure dynamic SQL as suggested above (i am sure jltoops agrees) are the negative side-effects:
* less performance
* security issues
Regarding the security issue, take this sample (pseudo-code, won't work exaclty like this):
exec ('update YOURTABLE set '+@colname+'=' + @colvalue +' where PK=' + @PK_value )
What will happen if the user will submit the following value for the column
"'anyvalue' DROP DATABASE select * from YOURTABLE "
The resulting sql will be this:
update YOURTABLE set yourcolumn="anyvalue" DROP DATABASE SELECT * FROM YOURTABLE where PK= 7
Funny, you allowed your user to drop the database :-)
Actually, your generic dynamic SQL will error out because if the last SELECT, because meanwhile the database has been dropped...
Don't think this is only imagination, too many shopping web sites have been hacked/destroyed this way !!!!
Using the sample code I posted, such things cannot occur, as ADO will care about all this.
CHeers
Ok, see your point but do I want to use ADODB.
I have been using primarily SQLClient and intellisense is not liking the Adodb stuff at all.
( I am using vb.net) so I probally have to inherit or import something?
I have been using primarily SQLClient and intellisense is not liking the Adodb stuff at all.
( I am using vb.net) so I probally have to inherit or import something?
more that likely ..
in my pages i just say
strSql = "select * from DYNA_TABLE_SURVEY"
Set conn = Server.CreateObject("ADODB
conn.ConnectionString = Application("saConn")
conn.connectiontimeout=360
conn.Open
set DataCmd = Server.CreateObject("ADODB
DataCmd.ActiveConnection = conn
Set Rs = Conn.Execute(strSQL)
if Rs.EOF then
response.write "<BR><BR><B><CENTER>No Records to Display</B></Center>"
response.end
end if
JAY
in my pages i just say
strSql = "select * from DYNA_TABLE_SURVEY"
Set conn = Server.CreateObject("ADODB
conn.ConnectionString = Application("saConn")
conn.connectiontimeout=360
conn.Open
set DataCmd = Server.CreateObject("ADODB
DataCmd.ActiveConnection = conn
Set Rs = Conn.Execute(strSQL)
if Rs.EOF then
response.write "<BR><BR><B><CENTER>No Records to Display</B></Center>"
response.end
end if
JAY
Active today
VB.Net has several things in the System.Data libraries:
Sqlclient -> works directly with SQL Server dbnet libraries, use this one if you work ONLY with SQL Server
Odbc -> works odbc connections
Oledb -> works with oledb connection
Under the hood, Ado.net is used for all of them (AFAIK)
so, my above VB6 code would be written like this in VB.NET (pseudo-code, as I write more C# than VB.NET)
dim conn as System.Data.Sqlclient.SqlC
dim proc as System.Data.Sqlclient.SqlC
proc.Connection = conn
proc.CommandText = "UPDATE yourtable SET YourCol = ? WHERE PK = ?"
proc.Parameters.add ( new SqlParameter ( "YourColParam" ).Value = <yourvalue> );
proc.Parameters.add ( new SqlParameter ( "YourPK").Value = <yourPKvalue> );
proc.ExecuteNonQuery()
As you see, the column name is fixed in the Command text, but you can easily build the SQL command using the column name which you SHOULD use, and not the ordinal number. Please follow the advice here NOT to use the ordinal number...
CHeers
Sqlclient -> works directly with SQL Server dbnet libraries, use this one if you work ONLY with SQL Server
Odbc -> works odbc connections
Oledb -> works with oledb connection
Under the hood, Ado.net is used for all of them (AFAIK)
so, my above VB6 code would be written like this in VB.NET (pseudo-code, as I write more C# than VB.NET)
dim conn as System.Data.Sqlclient.SqlC
dim proc as System.Data.Sqlclient.SqlC
proc.Connection = conn
proc.CommandText = "UPDATE yourtable SET YourCol = ? WHERE PK = ?"
proc.Parameters.add ( new SqlParameter ( "YourColParam" ).Value = <yourvalue> );
proc.Parameters.add ( new SqlParameter ( "YourPK").Value = <yourPKvalue> );
proc.ExecuteNonQuery()
As you see, the column name is fixed in the Command text, but you can easily build the SQL command using the column name which you SHOULD use, and not the ordinal number. Please follow the advice here NOT to use the ordinal number...
CHeers
Featured Post
![[Webinar] How Hackers Steal Your Credentials](https://filedb.experts-exchange.com/files/public/2017/5/20/3315d9d8-8110-4d52-be94-a5aacdd83e4b.png)
Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 10 hours left to enroll
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.