There has to be a better way to INSERT/UPDATE

I have a form that pulls existing information from a database and allows users to edit it. Every field can be left blank if the user wishes. I am having an issue with determining which SQL statement to run. There has to be an easier way to do this then what I am doing. For each filed I process the data this way:

SQL2="select * from 1985ClassList where MailingListID =" & Session("EID")
set aData = oConn.execute(SQL2)


SQL = "Update 1985ClassList SET "

if aData.Fields("FirstName") > " " and Request.QueryString("FirstName") > " " then
SQL = SQL & "'FirstName' = '" & Request.QueryString("FirstName") & "', "
end if

if aData.Fields("FirstName") = " " and Request.QueryString("FirstName") > " " then
strFSQL = strFSQL & "FirstName, "
strVSQL = strVSQL & Request.QueryString("FirstName") & ", "
end if


SQL = SQL & "where MailingListID =" & Session("EID")
set mData = oConn.execute(SQL)

if strFSQL > " " then
SQL1 = "Insert into 1985ClassList " & strFSQL & "VALUES " & strVSQL
end if
set mData = oConn.execute(SQL1)

It works fine for the UPDATE, but not for the INSERT.

could you please echo out the sql prior to execution and display that here

i cannot see what the sql would look like after the script has finished building it

This is the one line I get:
Update 1985ClassList SET 'FirstName' = 'Tracy', 'LastName' = 'Bergeron', where MailingListID =10
I do not get anything for an INSERT as a new record.
The database has people's names in it already, but nothing else. I have debug code to test the address field that will echo what is going on. It can be seen at http://www.ths85.org/contact.asp. Select any name from the long list and continue as if you want to save information you edit. Don't worry I have commented out the execution line, so it won't actually go to the database.

remove the quotes from the column names, and the spurious comma in front of the WHERE

Update 1985ClassList
SET FirstName = 'Tracy', LastName = 'Bergeron' where MailingListID =10

I will make the minor syntax changes to the UPDATE, that's easy.

Any reason why the code is not generating the INSERT statement.
I think it has some thing in the way I am compairing the data from the database and the form, see this line from the code in the thread:
if aData.Fields("FirstName") = " " and Request.QueryString("FirstName") > " " then

sorry, i don't read that particular code language

if you could echo the INSERT sql...

otherwise, please post in the forum for whatever language that is

I just have a suggestion that could make your programming life that much easier. Use an INSTEAD OF trigger on the table you're inserting into. That way, you just use an insert statement for everything and let the trigger figure out if it should really be an update.

What's up with the: if aData.Fields("FirstName") > " "

Try using <> "" instead, if you need to test whether or not the string is empty.

Apart from that, if you're using MS SQL, and I guessing you are, since this is the MS SQL forum, your code is one big invitation for SQL injection.

Whenever you take something from the REQUEST object (generally for user input) and use it as part of a SQL statement, validate the content. If it's supposed to be a number, cast it (CLng or CInt, etc.), if it's a string, make sure you replace single quotes with double single quotes!!

Why not your use a stored procedure and let SQL do all the hard work? This avoids horrendous looking code like you have.

Agreed!