Can I have a dynamic UpdateCommand inside a SqlDataSource?

Question

I have a SqlDataSource that I would like to use for updating. I would be using two similar update commands, and I was wondering how I could do this in one SqlDataSource. I know how to do 'normal' parameters, but I was wondering how I could change what was being updated as well as how it's being updated. Here are my statements as I would put them into the SqlDataSource:

UPDATE Part SET checkbox02 = @val WHERE partnum = @partnum

UPDATE Part SET checkbox03 = @val WHERE partnum = @partnum

But, I can only have one UpdateCommand per SqlDataSource.

Is it possible to do something like this:

UPDATE Part SET @checkbox = @val WHERE partnum = @partnum

where I can change @checkbox and have it update either checkbox02 or checkbox03 depending on what I set the parameter to? Determination of @checkbox would be happening in the code-behind.

If this is not possible, what is the best alternative to minimize code/memory usage?

You're probably going to have to get rid of the SqlDataSource, and then use SQL queries in the code-behind via ADO.NET (SQLCommand, SQLConnection, etc.). — Garrison Neely
– Garrison Neely, Commented Jul 16, 2013 at 17:12
You cannot just easily parametrize table and/or column names in your SQL statement. If you want to do this, you'll need to use dynamic SQL (building up your SQL statement as a string and executing it) - with the usual warnings about vulnerability against SQL injection attacks and all$ — marc_s
– marc_s, Commented Jul 16, 2013 at 17:18
It appears as if this will not work. Could I instead do this: Have UpdateCommand="UPDATE Part SET checkbox02 = @val02, checkbox03 = @val03 WHERE partnum = @partnum" and then set the parameter that I don't want to change to the appropriate column name? For example, say I don't want to change chekcbox02 this time, so I set the @val02 parameter to checkbox02. — wlyles
– wlyles, Commented Jul 16, 2013 at 17:34
@jadarnel27 that actually doesn't work. I tried it, but it won't convert checkbox02 to data type tinyint (the checkbox data types) — wlyles
– wlyles, Commented Jul 16, 2013 at 19:19

TylerH · Accepted Answer · 2025-04-10 17:14:32Z

0

What I was trying to do here is not feasible with the SqlDataSource class.

As I recall, the solution was to use a more custom solution with SQL queries in ADO.NET as suggested by Garrison Neely:

You're probably going to have to get rid of the SqlDataSource, and then use SQL queries in the code-behind via ADO.NET (SQLCommand, SQLConnection, etc.).

- Garrison Neely Jul 16, 2013 at 17:12

edited Apr 10 at 17:14

TylerH

21.2k83 gold badges84 silver badges120 bronze badges

answered Mar 20, 2018 at 22:51

wlyles

2,3161 gold badge25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Can I have a dynamic UpdateCommand inside a SqlDataSource?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related