Hello,

I'd like to use a DW UPDATE command server behavior with a variable column name (which I get from a QueryString), such as:

UPDATE tblColUSA
SET portImage = fn
WHERE ID = varListingID

FYI, portImage is my column name that varies depending on what gets passed from the previous page's QueryString. The actual (12) column names in my Access database are port1, port2, port3 ... port12. The person is uploading an image to their portfolio (they're permitted 12 images total) and I need to update the image's filename (fn) in the appropriate column (port1, port2, etc).

I don't know what the syntax is for letting DW know that the column name (portImage) varies, depending on the QueryString value that gets passed to the page. FYI, "fn" and "varListingID" above are also passed to this page via QueryString and I currently have them setup as variables in the DW Command dialog.

Any help would be much appreciated!

Hi David

Welcome to the forum.

I'm not aware that DW supports dynamic column names using the update behaviour without some manual editing of the code.

You could use the update behaviour and manually modify the behaviour. Follow these steps and it should be fine:

You need to create a variable to hold the passed column name.

Dim columnName
columnName = Request.QueryString("myColumnName"

Add a recordset in the normal way getting the value of a single record like this:

Select id, portImage From tblColUSA where id = varListingID

Create an update behaviour based on the recordset created.

Edit the update behaviour, find the assignment below:

MM_columnsStr = "portImage|',none,''"

And change to, note variable columnName:

MM_columnsStr = columnName & "|',none,''"

Edit the recordset and go to advanced mode, add a variable to the variables list with a name like varColumnName, set a runtime value of columnName (the name of the variable we created earlier on the page) and change the select statement to:

Select id, varColumnName From tblColUSA where id = varListingID

This should do what your after, so you've got the theory anyway.

Another way to do this would be to use an update command using a dynamically generated update statement. You can evaluate what column name (preferably use an integer to represent the column name) is being passed by the querystring. Use either an IF or better still a SELECT CASE statement and assign the value of your evaluation to a variable to use to generate the SQL statement.

Post back if you have any questions

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

I appreciate your quick reply Lee. "Create an update behaviour" requires a form on the page, which I didn't want to use ... that's why I was just using the command. I don't necessarily even want/need to use DW to accomplish this. If you could maybe just give me example code that uses an SQL UPDATE statement with three variables passed from a QueryString, such as below. In this case, portImage is my column/field name that is variable depending on what gets passed by the QueryString.

&lt;%@ Language="VBScript" %&gt;

&lt;%
'declare variables
Dim SQL, connection, recordset, sConnString
Dim listingID, fn, portImage

listingID = Request.QueryString("listingID"
fn = Request.QueryString("fn"
portImage = Request.QueryString("portImage"

'build the SQL statement
SQL = "UPDATE tblColUsa SET portImage='" & fn & "' WHERE ID=" & listingID

'Create an ADO connection and recordset object
set connection=Server.CreateObject("ADODB.Connection"

'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("/db/intColUSA.mdb"

'Open the connection to our database
Connection.Open(sConnString)

'Execute the SQL
Connection.execute(SQL)

'Close the resources and free up space
connection.Close
Set connection=Nothing

Response.write "&lt;div align='center'&gt;Record updated.&lt;/div&gt;"
%&gt;

D. David Andrews

Hi David

Change the SQL to, I haven't looked att he rest of your code, just this line:

'build the SQL statement
SQL = "UPDATE tblColUsa SET " & portImage & " ='" & fn & "' WHERE ID=" & listingID

This will pull in the portImage as required.

I have concerns about this approach though as you are leaving yourself wide open to SQL injection here, the reasons I've set out below:

1. You're using the querystring and so anyone can edit that and send the values they'd like, not the values you expect.
2. The values you are passing in the querystring are identifying actual database items, column names for example.
3. There's no checking for empty values or illegal values to prevent injection attack.

I would advise you do the following:

1. Change to FORM instead of GET
2. Restrict your fn length to something short, maybe the 8.3 approach.
3. Change the form fields in your form that represent the column name to something less obvious.
4. The portImage that handles the form field should be sending a numeric value instead of the name of the field in your database.
5. Check that the submitted value can be converted to a number, if not send them back where they came from, do not interact with your database until you get what you expect.
6. If you're ever passing string into a dynamic sql statement you need to do checks for the obvious:
SELECT, DELETE, UPDATE, INSERT, DROP, --, 1=1 etc, etc etc.

There's a whole lot to preventing SQL injection, so do not just rely on the steps above, read up on it.

Not giving you a lecture here, you probably know all this anyway, just thought it'll help you and others.

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
&lt;font size="1"&gt;[ Studio MX/MX2004 | ASP -&gt; VBScript/PerlScript/JavaScript | SQL | CSS ]&lt;/font&gt;

Edited by - Digga the Wolf on 18 Mar 2007 22:56:50

Thanks for the solution and the security advise too. I'll do some reading. FYI, I ended up going with a Select Case.

D. David Andrews