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?
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.
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.
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("val1" <> "" then
sql = sql & "col1 = '" & request.form("val1" & "',"
else
sql = sql & "col1 = NULL,"
end if
if request.form("val2" <> "" then
sql = sql & "col2 = '" & request.form("val2" & "',"
else
sql = sql & "col2 = NULL,"
end if
'chop off that last comma
sql = left(sql, len(sql)-1)
'add your where clause
sql = sql & " WHERE pkColumn = " & pkValue
'execute the statement
conObject.execute sql
Although simplified, this method works well for updating tables based on form inputs from users.
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.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.