Delete SQL statement

[MONFU]

Hello all,

I am a newbie in ASP development and I have an error. I am suspecting that I have a problem with the delete sql statement in my ASP page, which I cannot figure out what it is. Here is my code:-

'*** Create the sql to insert the e-mail address in the database if not empty

if EMail <> &quot;''&quot; then
sql1 = &quot;&quot;
sql1 = sql1 & &quot;DELETE FROM contact &quot;
sql1 = sql1 & &quot; WHERE e-mail = &quot; & oldEMail
sql1 = sql1 & &quot; AND pAddress1 = &quot; & oldAddress1
sql1 = sql1 & &quot; AND pAddress2 = &quot; & oldAddress2
sql1 = sql1 & &quot; AND others = &quot; & oldOthers & &quot;;&quot;
rs1.open (sql1)
rs1.open &quot;INSERT INTO contact (e-mail,pAddress1,pAddress2,others) VALUES (&quot; & EMail & &quot;,&quot; & oldAddress1 & &quot;,&quot; & oldAddress2 & &quot;,&quot; & oldOthers & &quot;&quot;
Response.Write(sql1)
oldEMail = EMail
end if

And this is the error it is giving me:-

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'e-mail = 'info@otters.com.mt' AND pAddress1 = '13, St.Paul's Street', AND pAddress2 = 'Victoria', AND others = 'other info';'.


Please can you help me out! All help is much appreciated.

Thanks

 

[mkuan]

You have extra &quot;From&quot; in your delete statement, and you need single quote on your string parameter.

 

[JuanitaC]

The easiest way to diagnose problems with SQL statements is to print them to the screen so you can see how it is being created:

sql1 = &quot;&quot;
sql1 = sql1 & &quot;DELETE FROM contact &quot;
sql1 = sql1 & &quot; WHERE e-mail = &quot; & oldEMail
sql1 = sql1 & &quot; AND pAddress1 = &quot; & oldAddress1
sql1 = sql1 & &quot; AND pAddress2 = &quot; & oldAddress2
sql1 = sql1 & &quot; AND others = &quot; & oldOthers & &quot;;&quot;
Response.write(sql1)
rs1.open (sql1)

Something to watch out for is un-escaped single quotes in your statement:

'e-mail = 'info@otters.com.mt' AND pAddress1 = '13, St.Paul's Street' AND pAddress2 = 'Victoria', AND others = 'other info';'.

To fix that, do this:

sql1 = sql1 & &quot;DELETE FROM contact &quot;
sql1 = sql1 & &quot; WHERE e-mail = &quot; & oldEMail
sql1 = sql1 & &quot; AND pAddress1 = &quot; & Replace(oldAddress1,&quot;'&quot;,&quot;''&quot
sql1 = sql1 & &quot; AND pAddress2 = &quot; & Replace(oldAddress2,&quot;'&quot;,&quot;''&quot
sql1 = sql1 & &quot; AND others = &quot; & Replace(oldOthers,&quot;'&quot;,&quot;''&quot & &quot;;&quot;
Response.write(sql1)
rs1.open (sql1)

 

[Tarwn]

One additional step, before searching for unescaped quotes, you will need to add them in. All string must be quote delimeted.

In the above SQL String with replaces:

sql1 = sql1 & "DELETE FROM contact "
    sql1 = sql1 & " WHERE e-mail = '" &  oldEMail & "'"
    sql1 = sql1 & " AND pAddress1 = '" &  Replace(oldAddress1,"'","''")  & "'"
    sql1 = sql1 & " AND pAddress2 = '" &  Replace(oldAddress2,"'","''") & "'"
    sql1 = sql1 & " AND others = '" &  Replace(oldOthers,"'","''") & "';"

Now this may be entirely unnecessary. If you have a primary key that is not being altered you can simply do an update on the table. I would highly recommend having a unique arbitrary id that is an autonumber field as your primary key because it will be very useful in many instances. In this case if you simply place the primary key in a hidden field on the previous page than you can do the following:

The following is all written under the assumption that you are:
1)Executing a statement to check if entry already existed
2)If it existed
Execute a delete on entry
Insert a new entry
3)Otherwise
Insert as a new entry


Rather than the three db connections you are making:
1: Add a variable for you primary key (Dim addressKey)
2: When you read the other values from the previous page, read the addressKey as well (remember to put in the hidden field)
addressKey = request.Form(&quot;addressKey&quot
3: In the previous page also keep a flag called something like nextAction. If the user is updating a record that already existed, set the value of nextAction to &quot;update&quot; if it is a new record, set it to &quot;new record&quot; (values are my own, as long as your consistent you can name them what you like)
4) Now that means we don't have to do your first SQL query (the check for empty record that doesn't appear above), you can simply see if it is an update or a new record.
5)If it is a new record, add it as you did above(your primary key will also be a null value)
If it is an existing record, use UPDATE
sql1 = &quot;UPDATE contact SET e-mail = '&quot; & oldEMail & &quot;'&quot;
sql1 = sql1 & &quot;, pAddress1 = '&quot; & Replace(oldAddress1,&quot;'&quot;,&quot;''&quot & &quot;'&quot;
sql1 = sql1 & &quot;, pAddress2 = '&quot; & Replace(oldAddress2,&quot;'&quot;,&quot;''&quot & &quot;'&quot;
sql1 = sql1 & &quot;, others = '&quot; & Replace(oldOthers,&quot;'&quot;,&quot;''&quot & &quot;'&quot;
sql1 = sql1 & &quot; WHERE addressKey = &quot; & addressKey & &quot;;&quot;

Now execute it exactly like your delete function above.

As you will notice, in no situation will you be executing as many statements as you are above, by passing those two hidden variables (primary key and next action) you have streamlined the necessary actions to be taken.


If you are interested, you can see a working example of a similar process in the ASP 102 FAQ that I wrote using primary keys, action variables, and a simple database (3 fields I think).

-Tarwn
------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation

 

[MONFU]

Hello Again,
I tried Juanita C's code but it still gave me the same problem. I am inserting the single quote exactly after retreveing the data from the form, for example e-mail = &quot;'&quot; & request.form(&quot;oldEmail&quot & &quot;'&quot;. I tried your code but still it is not working.

Tarwin, let me explain exactly my problem, cause I think your example is too complex in my situation. Well what I have is a form, with 4 entry fields, namely the e-mail address, Postal Address1, Postal Address2 and Others. Now the user can change anyone of them or all of them at the same time. There is only one row in the table containig these 4 fields, and whenever the user updates one of the fields, I want the database to register that data in the same exact field so as not to add more rows. I hope that I am making myself clear here. That is why I did 4 if clauses like the one with the E-Mail, so that if there is something in the field, it is deleted and inserted, if not, the old data is inserted.

I already did the same exact procedure on another page successfully, however the database contained only 2 fields. That is why I am suspecting that the syntax of the Delete statement is incorrect, since on the other page, with only 2 fields, it did work fine and how I wanted it to work.

I do not know if there is an easier way than mine, since as I told you I am a newbie with no experience yet. So all your ideas are more than welcome and thanks in advance for your precious help!

 

[JuanitaC]

When you retrieve the data from the form, that is where you should check for and replace the ' in the data:

e-mail = &quot;'&quot; & request.form(&quot;oldEmail&quot & &quot;'&quot;
oldAddress1 = &quot;'&quot; & Replace(request.form(&quot;oldAddress1&quot,&quot;'&quot;,&quot;''&quot & &quot;'&quot;
oldAddress2 = &quot;'&quot; & Replace(request.form(&quot;oldAddress2&quot,&quot;'&quot;,&quot;''&quot & &quot;'&quot;
oldOthers = Replace(request.form(&quot;oldOthers&quot,&quot;'&quot;,&quot;''&quot & &quot;'&quot;

Also, when you print out your Delete statement on your screen, how does it look? Post what it is being created if you need further help.

 

[MONFU]

Hello again Juanita,

I still have the same problem. This is how I am defining these fields:-

oldEMail = &quot;'&quot; & rs.Fields.Item(&quot;e-mail&quot.Value & &quot;'&quot;
oldAddress1 = &quot;'&quot; & rs.Fields.Item(&quot;pAddress1&quot.Value & &quot;'&quot;
oldAddress2 = &quot;'&quot; & rs.Fields.Item(&quot;pAddress2&quot.Value & &quot;'&quot;
oldOthers = &quot;'&quot; & rs.Fields.Item(&quot;others&quot.Value & &quot;'&quot;

and then the delete and insert code is here:-

if EMail <> &quot;''&quot; then
sql1 = sql1 & &quot;DELETE FROM contact &quot;
sql1 = sql1 & &quot; WHERE e-mail = &quot; & oldEMail
sql1 = sql1 & &quot; AND pAddress1 = &quot; & Replace(oldAddress1,&quot;'&quot;,&quot;''&quot
sql1 = sql1 & &quot; AND pAddress2 = &quot; & Replace(oldAddress2,&quot;'&quot;,&quot;''&quot
sql1 = sql1 & &quot; AND others = &quot; & Replace(oldOthers,&quot;'&quot;,&quot;''&quot & &quot;;&quot;
Response.write(sql1)
rs1.open (sql1)
rs1.open &quot;INSERT INTO contact (e-mail,pAddress1,pAddress2,others) VALUES (&quot; & EMail & &quot;,&quot; & oldAddress1 & &quot;,&quot; & oldAddress2 & &quot;,&quot; & oldOthers & &quot;&quot;
Response.Write(sql1)
oldEMail = EMail
end if

maybe this will give you a better picture of the situation.

And the error the page is giving me is this:-

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'e-mail = 'info@otters.com.mt' AND pAddress1 = ''13, New Street'' AND pAddress2 = ''Victoria'' AND others = ''other info'''.

 

[Tarwn]

I apologize about the post above, I tend to get carried away when I am tired.
Here is your problem, you will have to replace the single quotes when you are setting the variables, otherwise you are replacing not just any quotes in the user entry, but also the ones you yourself are adding. Try This:

oldEMail = "'" & Replace(rs.Fields.Item("e-mail").Value,"'","''") & "'"
oldAddress1 = "'" & Replace(rs.Fields.Item("pAddress1").Value,"'","''") & "'"
oldAddress2 = "'" & Replace(rs.Fields.Item("pAddress2").Value,"'","''") & "'"
oldOthers = "'" & Replace(rs.Fields.Item("others").Value,"'","''") & "'"

and then the delete and insert code is here:-

if EMail <> &quot;''&quot; then
    sql1 = sql1 & &quot;DELETE FROM contact &quot;
    sql1 = sql1 & &quot; WHERE e-mail = &quot; &  oldEMail 
    sql1 = sql1 & &quot; AND pAddress1 = &quot; &  oldAddress1    sql1 = sql1 & &quot; AND pAddress2 = &quot; & oldAddress2    sql1 = sql1 & &quot; AND others = &quot; & oldOthers & &quot;;&quot;
    Response.write(sql1)
    rs1.open (sql1)
    rs1.open &quot;INSERT INTO contact (e-mail,pAddress1,pAddress2,others) VALUES (&quot; & EMail & &quot;,&quot; & oldAddress1 & &quot;,&quot; & oldAddress2 & &quot;,&quot; & oldOthers & &quot;);&quot;
    Response.Write(sql1)
    oldEMail = EMail
end if

It's not obvious, but what your error message is printing is actually double sets of single quotes around your last three values, not double quotes.
Again, I apologize for the ramble above, hope this fixes it for you
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation

 

[roy29]

I just wanted to add something. After reading your 2nd or 3rd post I think I understand what you're trying to accomplish. It looks like you're deleting the old information but then adding it right back into the database with the updated information being changed.
If this is what you're doing then try an update instead and use the email address in the where clause.
Hope this helps

 

[Funka]

you are adding single quotes around your value, and THEN you are doubling these single quotes which results in entirely too many single quotes. i wrote a function called makeSqlSafe which i use all the time to make my life easier:

Function makeSqlSafe(str)
    makeSqlSafe = Replace(str,"'","''")
End Function

and then you can construct your SQL thusly:

sql1 = sql1 & "DELETE FROM contact"
sql1 = sql1 & " WHERE e-mail  = '" & makeSqlSafe(oldEMail)    & "'"
sql1 = sql1 & " AND pAddress1 = '" & makeSqlSafe(oldAddress1) & "'"
sql1 = sql1 & " AND pAddress2 = '" & makeSqlSafe(oldAddress2) & "'"
sql1 = sql1 & " AND others    = '" & makeSqlSafe(oldOthers)   & "'"

Response.Write sql1         ' for debugging our sql syntax
Response.End()              ' for preventing any further errors

also, you never posted what was output from your sql1 string. after adding the Response.Write sql1, be sure to avoid any errors so that it actually gets printed to screen, as in my example above. i cannot tell you how many times this trick has pointed out my improperly constructed sql strings!

good luck!