how to update fields so if a user can delete the text in field?

[debrac]

I have a form, and a user should be able to edit records, and if they want to delete a field, (not an entire record) they can. why doesnt an update of a recordset accept empty strings?

thanks.

 

[link9]

If you are wanting to NULL out a field in a record, try using a SQL UPDATE statement... the following is valid:

UPDATE tableName SET colName = NULL where pkField = pkValue

and string the whole update together like this:

UPDATE tableName SET col1 = NULL, col2 = userInput, col3 = NULL WHERE pkField = pkValue

Your app will run faster by using this method, as well -- rather than re-opening the recordset in question... going through each record and assigning a new value, and then calling the .update method on that recordset. SQL solutions will almost always be the better choice because of the lack of the extra object floating around in memory (the recordset you are updating) --

Instead, you have the one connection object that you simply execute a text based SQL command on.

hope that helps!
Paul Prewett

 

[debrac]

thanks, but what if u dont know if the value will be null/empty?
the user can change the text field or i want them to be able to delete the text field.

 

[link9]

You have to loop through them to find out which ones are/aren't blank -

Think of this:

'make a sql variable
dim sql

'start it with default value
sql = "UPDATE tableName set "

'check form vars and build statement accordingly
if request.form(&quot;val1&quot <> &quot;&quot; then
sql = sql & &quot;col1 = '&quot; & request.form(&quot;val1&quot & &quot;',&quot;
else
sql = sql & &quot;col1 = NULL,&quot;
end if

if request.form(&quot;val2&quot <> &quot;&quot; then
sql = sql & &quot;col2 = '&quot; & request.form(&quot;val2&quot & &quot;',&quot;
else
sql = sql & &quot;col2 = NULL,&quot;
end if


'chop off that last comma
sql = left(sql, len(sql)-1)

'add your where clause
sql = sql & &quot; WHERE pkColumn = &quot; & pkValue

'execute the statement
conObject.execute sql


Although simplified, this method works well for updating tables based on form inputs from users.


paul

 

[SauerC]

why not just let the field contain an empty string, instead of putting nulls in?

 

[link9]

There are lots of good reasons not to do that, not the least of which being that an empty string takes up more real estate than a NULL -- maybe not a big deal in a small database, but the bigger the database, the bigger the deal.