Solved

Updating e-mail address success/failure

Posted on 2006-11-14
14
270 Views
Last Modified: 2010-04-16
I am writing a code where I take in a old e-mail address and then a new e-mail address and then I update the old with the new from the database. My issue I am having is that I need to print out success or failure depending on whether the old e-mail that the user inputs matches the e-mail addresses in the database. If the old e-mail address matches the database, then the update will go through and the result will be a success. But if the old e-mail address does not match the database, then the update will NOT got through and the result will be a failure.

My stored procedure is as follows:
CREATE procedure ChangeEmailAddress @oldemail varchar (75),@newemail varchar (75)
AS
      update physicianspanel..email
      set email = @newemail
      where email = @oldemail
print @@rowcount
end
go

My ASP code is as follows:
<%
      Dim ConnectionString
      Dim ofso
      Dim otmp

        ConnectionString="Provider=SQLOLEDB;Data Source=TEST_W2K_SQL2K;Database=PhysiciansPanel;UID=paramtestuser;PWD=paramtestuser;Network Library=dbmssocn;"

      Dim objconn, objRS, adocmd
    Set objconn = Server.CreateObject("ADODB.Connection")
    objconn.Open (ConnectionString)

      Set adocmd = Server.CreateObject("ADODB.Command")
      adocmd.CommandText = "ChangeEmailAddress"
      adocmd.CommandType = adCmdStoredProc

      objRS = Server.CreateObject("ADODB.Recordset")
      adocmd.activeConnection = objconn
    adocmd.Parameters.Append adocmd.CreateParameter("@oldemail",adVarChar, adParamInput, 75)
    adocmd.Parameters.Append adocmd.CreateParameter("@newemail",adVarChar, adParamInput, 75)
    adocmd.Parameters("@oldemail") = Request.Form("old_email")
    adocmd.Parameters("@newemail") = Request.Form("new_email")
    set objRS = adocmd.Execute

         if Request.Form("old_email") = objRS("email") then
               Response.Write "Success"
         else
               Response.Write "Failure"
         end if

      objconn.Close
%>

I know the if-then statement is somewhat correct but I know the part with objRS("email") is definitely incorrect and I am not sure how to go upon to fixing it.
Hopefully someone will be able to help me out. I know this is probably a easy resolution that I can't come across to so any help will be appreciated. Thanks
0
Comment
Question by:pimpp1184
  • 8
  • 6
14 Comments
 

Author Comment

by:pimpp1184
ID: 17941262
Can someone assist me with this? Thanks
0
 
LVL 6

Expert Comment

by:DonKyles
ID: 17941532
i'm not sure if my solution is vary efficiency or not. So my algorithm are

check the existing email first
if no record found then display "Failure"
else Update the record and display "Success

So my code would be

<%
' get the input from the form
newEmail = Request.Form("new_email")
oldEmail = Request.Form("old_email")

' set the sql connection object
Set objconn = Server.CreateObject("ADODB.Connection")
objconn.Open (ConnectionString)

qry = "SELECT physicianspanelID FROM physicianspanel WHERE email = '" & oldEmail & "'"

' execute query statement
set getResult =  objconn.execute(qry)

if (getResult.EOF) then
       ' no record found/ email doesn't  match in the database
       Response.Write "Failure"
else
       qry = "UPDATE physicianspanel SET email = '" & newEmail & "' WHERE email = '" & oldEmail & "'"
       ' execute update query
       set updateEmail = objconn.execute(qry)
       Response.write "Success"
       
       ' close the object
       updateEmail.Close
       set updateEmail = nothing
end if

' close the object
getResult.Close
set getResult = nothing
%>





0
 

Author Comment

by:pimpp1184
ID: 17941712
DonKyles, thanks for your comment. Sadly, I have actually thought and tried of the same logic that you suggested. And the way I need this to work is through the stored procedure so I can't create SQL statements as you posted above. I have to pull the parameters already created in the stored procedures and use them accordingly. The thing is that the update works perfectly when i pull the stored procedure variables and assign them the user inputted values. But what I am trying to do is notify the user if the update went through successfully or not. The way my logic for that is that whatever the user inputs as a old value will be matched up by the e-mails within the table "e-mail". If the old e-mail user value matches the one in the table "e-mail" then it is a success; if it isn't, then it is a failure.
At the moment, I can't come up with a way to make it fully work which is why I need your consultation.
Any other suggestions or ideas?
0
 

Author Comment

by:pimpp1184
ID: 17946125
Can anyone assist me on this?
0
 
LVL 6

Expert Comment

by:DonKyles
ID: 17949796
Try to get the value of this line

print @@rowcount

if rowcount > 0 then
       success
else
       Failure
end if

I'm never used stored procedure before, but I will try.
0
 

Author Comment

by:pimpp1184
ID: 17955994
I tried that as well. It just won't work. It just extremely confusing. It works fine when the program executes the procedure but when it comes to success/failure code it just fails.
0
 
LVL 6

Expert Comment

by:DonKyles
ID: 17959328
set objRS = adocmd.Execute  ' get rid of set objRs

adocmd.Execute
rowUpdate = adocmd.Parameters("rowcount").Value
if (rowUpdate > 0)
         success
else
         Failure
End if

if you already did this code...can you change the Store Procedure?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 6

Expert Comment

by:DonKyles
ID: 17959428
In case, if you cannot change the store Procedure.
You mention that the store procedure works fine just only the success/failure part doesn't work
why don't you just run another query to check the old email. If the old email exists, this means the record should be updated then display success else failure (because no record found).

qry = "SELECT physicianspanelID FROM physicianspanel WHERE email = '" & oldEmail & "'"

' execute query statement
set getResult =  objconn.execute(qry)

if (getResult.EOF) then
       Response.Write "Failure"
else
       Response.write "Success"
end if

' close the object
getResult.Close
set getResult = nothing
0
 

Author Comment

by:pimpp1184
ID: 17982192
DonKyles,

Thanks for you responses. I tried your methods and couldn't get them to work successfully. I am sort of trying a different approach but still am getting an error:
The code is as follows:
      qry = "SELECT email FROM physicianspanel WHERE email"
      set getresult = objconn.execute(qry)

      if Request.Form("old_email") <> (getresult.eof) then
            Respone.Write "Failure"
      else
            objRS = Server.CreateObject("ADODB.Recordset")
            adocmd.activeConnection = objconn
            adocmd.Parameters.Append adocmd.CreateParameter("@oldemail",adVarChar, adParamInput, 75)
            adocmd.Parameters.Append adocmd.CreateParameter("@newemail",adVarChar, adParamInput, 75)
            adocmd.Parameters("@oldemail") = Request.Form("old_email")
            adocmd.Parameters("@newemail") = Request.Form("new_email")

            adocmd.Execute
            Response.Write "Success"
        end if

Do you think this should work or not?
The error I am getting is at this line: set getresult = objconn.execute(qry)
It is stating the following:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near 'email'.
/param/PPanel_update_email_results.asp, line 23

Any suggestions to this one? Thanks for your help.
0
 

Author Comment

by:pimpp1184
ID: 17997425
Any suggestions, anyone? I'm desperate here. Thanks
0
 
LVL 6

Expert Comment

by:DonKyles
ID: 18001357
qry = "SELECT email FROM physicianspanel WHERE email" won't work because you need email = 'somthing'.

qry = "SELECT email FROM physicianspanel WHERE email = '" & Request.Form("old_email") & "'"
0
 

Author Comment

by:pimpp1184
ID: 18003907
Ok I had to change the qry statement to make it work in that it will show me either 'success' or 'failure' BUT now the statement does not work even after I enter the legit email address that is in table. The code that I have right now is:
     qry = "SELECT email FROM email WHERE email = '" & Request.Form("old_email") & "'"
     set getresult = objconn.execute(qry)

     if Request.Form("old_email") = (getresult.eof) then
        objRS = Server.CreateObject("ADODB.Recordset")
          adocmd.activeConnection = objconn
          adocmd.Parameters.Append adocmd.CreateParameter("@oldemail",adVarChar, adParamInput, 75)
          adocmd.Parameters.Append adocmd.CreateParameter("@newemail",adVarChar, adParamInput, 75)
          adocmd.Parameters("@oldemail") = Request.Form("old_email")
          adocmd.Parameters("@newemail") = Request.Form("new_email")
          adocmd.Execute

        Response.Write "Success"
      else
      Response.Write "Failure"
      end if

When I run the program, it will give me a response of "failure" even though the old email address that I enter is correct yet it will be execute the program that I have created in that it will update the old e-mail address with the new. Is the if then statement incorrect somehow because at the moment, I do not see anything incorrect with it? Let me know if you can figure it out. I will try to figure it out in the meantime as well. Thanks.
0
 
LVL 6

Accepted Solution

by:
DonKyles earned 200 total points
ID: 18006912
Ok it's wrong at if else statement (since you already compare the old email in SQL statement then you don't need to compare again in if else statement) you missunderstood the EOF (eof means end of file this will return true when you execute sql statement and the result return nothing or end of the result)

you should do this way

qry = "SELECT email FROM email WHERE email = '" & Request.Form("old_email") & "'"
set getresult = objconn.execute(qry)

if (getresult.EOF) then
        Response.write "Failure"
else
         objRS = Server.CreateObject("ADODB.Recordset")
         adocmd.activeConnection = objconn
         adocmd.Parameters.Append adocmd.CreateParameter("@oldemail",adVarChar, adParamInput, 75)
         adocmd.Parameters.Append adocmd.CreateParameter("@newemail",adVarChar, adParamInput, 75)
         adocmd.Parameters("@oldemail") = Request.Form("old_email")
         adocmd.Parameters("@newemail") = Request.Form("new_email")
         adocmd.Execute

        Response.Write "Success"

end if

getresult.close()
set getresult = nothing  
0
 

Author Comment

by:pimpp1184
ID: 18009102
Stupid me. I had tried that as well but might have typed something else incorrectly. But all in all, it is working perfectly. Thank you for your help. Much appreciated.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem to setup 18 79
sumHeights2  challenge 7 76
for loop with Set 4 45
Device same like our heart 12 47
RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now