Problem with recordset..

Hi!
This is what I'm trying to do.
A user should be able to receive an email with his/hers username and password throug the use of a page where they fill in their email. My thougt is that I let them fill out a form that contains one textfield where they fill in their email and I than check if that email is in the database and if so return an email to the same adress containing the users username and password.

This is how far I have got.
I have the regular loginpage.asp that contains a href that go to mail.asp.
Mail.asp contains the form the user fills in. On submit Mail.asp calls mailpassword.asp.

This far it's working:-) I can recieve an email containing the value inserted in the field epost in the form in Mail.asp.

For now I'm just trying to check if the email posted in the form is in the database. That's why I don't look for username and password anywhere!

But I don't get it to work with the database.
I'm sure it's the syntax that's wrong. I've tested my connection outside of this application and it works. But I don't get this to work.

This is some of my code in mailpassword.asp
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/mail.asp" -->
<%
Response.Expires = 0
%>
<%
Dim sAnvNamn, sAnvLosen, sAnvepost
Dim iErr, sRedirectUrl
iErr = 1

' *** Check if epost is in database
  if Request.Form.Count > 0 then
     Dim rs, sQuery , flag, usermail
     
     Usermail= Request.querystring("epost")    
     flag="ADODB.Recordset"
       set rs = Server.CreateObject(flag)
       rs.ActiveConnection = mail_STRING
     rs.Source = "SELECT Email"    
       rs.CursorType = 0
      rs.CursorLocation = 2
       rs.LockType = 3
       rs.Open    
     'rs.Source = rs.Source & " FROM dbo.UserData WHERE Email='" & Replace(MM_valUsername,"'","''") & "'"
     rs.Source = select Email from dbo.UserData where Email='" & Request.querystring("epost") & "'"
     
       
          if rs.RecordCount > 0 then
               sAnvepost = rs("Email")
               iErr = 0
          end if          
     rs.Close
     if iErr <> 0 then 'gör om en felsida
          sRedirectUrl = ""
     end if    


Epost = Request.querystring("epost")
 
'***Send the results via email to someone
 dim smtp
 Set smtp = Server.CreateObject("CDONTS.NewMail")
 
 smtp.MailFormat = 0 '(Mime format)
 smtp.From = Epost
 smtp.To = Epost
 smtp.Subject = ""
 smtp.Body = ": " & vbcrlf&_
                + "" & vbcrlf&_
               + "Username= " + Epost& vbcrlf&_    
               + "Password= " + Epost& vbcrlf&_
               + "" & vbcrlf&_
               + "Good luck! " 

               
  smtp.Send
  Set smtp = Nothing
end if
%>

Is there anyone outthere that can help me with this?
Is it just the string rs.Source = select Email from dbo.UserData where Email='" & Request.querystring("epost") & "'" or is it something else?
The query works fine in analyzer.

Help please!!
     
AnnaCflAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlfaNoMoreCommented:
Your old code and new code is all a bit messed up. Tyr this instead:

if Request.Form.Count > 0 then
    Dim rs, sQuery , flag, usermail
   
    Usermail= Request.querystring("epost")    
    flag="ADODB.Recordset"
      set rs = Server.CreateObject(flag)
      rs.ActiveConnection = mail_STRING
      rs.Source = select Email from dbo.UserData where Email='" & Usermail & "'"
      rs.CursorType = 0
      rs.CursorLocation = 2
      rs.LockType = 3

      rs.Open()

You were opening the Recordset with the statement "SELECT email", and once your rs was open, you performed the actual string!
0
AnnaCflAuthor Commented:
Uupps, but now I've tried the code you posted, and it still doesn't work:-(

This is how my code looks now.

<%
if Request.Form.Count > 0 then
   Dim rs, sQuery , flag, Usermail, sAnvepost
   
   Usermail= Request.querystring("epost")    
   flag="ADODB.Recordset"
     set rs = Server.CreateObject(flag)
     rs.ActiveConnection = mail_STRING
     rs.Source = select Email from dbo.UserData where Email='" & Usermail & "'"
     rs.CursorType = 0
     rs.CursorLocation = 2
     rs.LockType = 3
     rs.Open()
if rs.RecordCount > 0 then
          sAnvepost = rs("Email")
end if    
Epost = Request.querystring("epost")
 
'***Send the results via email to someone
 dim smtp
 Set smtp = Server.CreateObject("CDONTS.NewMail")
 
 smtp.MailFormat = 0 '(Mime format)
 smtp.From = Epost
 smtp.To = Epost
 smtp.Subject = ""
 smtp.Body = ": " & vbcrlf&_
                + "" & vbcrlf&_
               + "Username= " + Epost& vbcrlf&_
               + "Password= " + Epost& vbcrlf&_
               + "" & vbcrlf&_
               + "Good luck! " 

               
  smtp.Send
  Set smtp = Nothing
end if
%>
0
AlfaNoMoreCommented:
So what's not happening? Any errors?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

MannSoftCommented:
Something looks weird to me:

--- Snippet of your code ---
if Request.Form.Count > 0 then
--- End snipped ---
   
First you check to see if Request.Form.Count is greater than zero, which is fine.  That implies you are using the POST method in the form where the user submits their email.

--- Snippet of your code ---
rs.Source = select Email from dbo.UserData where Email='" & Request.querystring("epost") & "'"
--- End snippet ---

But further down you have the above code.  On that line you are pulling the epost variable from Request.QueryString, which implies you used the GET method in the form where the user submits their email.

So you have to decide which form method you are going to use.  If its GET, use QueryString and not Form.  If its POST, use Form and not QueryString.

Thats about all I can see wrong.
0
DesertWarriorCommented:
you can use both the POST and GET method in your form it doesn't matter. It only depends on the way you want to collect the values of the variables. I don't think that's the problem.

I have one advice for you :
put all the information that you get through Requests into variables once and for all and use only the variables afterwards to get the information. I see you use Request.querystring("epost") too often while you can just put it in a variable in the beginning of your code and then use only this variable. This minimizes A LOT the time of execution cause you don't always access the information on the server.
0
MannSoftCommented:
You are right that you can use either GET or POST to send the form.  But it does matter what you use since your ASP scripts needs to correspond with your choice.  Each method has its own way to "collect the data" as you put it.

As I pointed out, if you use the GET method then the Request.QueryString collection is populated with the form data.

If you use the POST method then the Request.Form collection is populated with the form data.

If you want to get the form data no matter what method was used, Request("epost") would work.  Ive never actually seen anyone recommend you do it that way, but its always an option.
0
DesertWarriorCommented:
I agree but you can get some variables through the querystring statement and others through the request.form statement in the SAME FORM. You're not obliged to use one method and stick with it in the form.
for example :
in a page named info.asp you could have a submit button that calls a page verify_info.asp?email=bob@bob.com

in the verify_info.asp page you would access the email with the querystring method while you get other values that the user entered through the request.form method...
right?

0
DesertWarriorCommented:
I agree but you can get some variables through the querystring statement and others through the request.form statement in the SAME FORM. You're not obliged to use one method and stick with it in the form.
for example :
in a page named info.asp you could have a submit button that calls a page verify_info.asp?email=bob@bob.com

in the verify_info.asp page you would access the email with the querystring method while you get other values that the user entered through the request.form method...
right?

0
MannSoftCommented:
Yes, you're right.  Im not sure what your point in relation to this question is though.
0
DesertWarriorCommented:
I agree but you can get some variables through the querystring statement and others through the request.form statement in the SAME FORM. You're not obliged to use one method and stick with it in the form.
for example :
in a page named info.asp you could have a submit button that calls a page verify_info.asp?email=bob@bob.com

in the verify_info.asp page you would access the email with the querystring method while you get other values that the user entered through the request.form method...
right?

0
AnnaCflAuthor Commented:
Hi, I've read your interesting discussions. To bad for me, so far none of it helps me with my problem:-(

Yes, I use the get method in the form that calls mailpassword.asp.

I've now changed to if Request.querystring.Count > 0 then
as suggested.
The error I receive when I run the page is, page not found
HTTP 500 - Internal servererror

Anyone, any new suggestion what the problem can be?

Hopefully waiting for a good suggestion :-)

/Anna

My code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/mail.asp" -->
<%
Response.Expires = 0
%>
some html
<Body>
<Form>
<%
if Request.querystring.Count > 0 then
   Dim rs, flag, Usermail, SAnvepost
   
   Usermail= Request.querystring("epost")    
   flag="ADODB.Recordset"
     set rs = Server.CreateObject(flag)
     rs.ActiveConnection = mail_STRING
     rs.Source = select Email from dbo.UserData where Email='" & Usermail & "'"
     if rs.RecordCount > 0 then
          sAnvepost = rs("Email")
     end if    
     rs.CursorType = 0
     rs.CursorLocation = 2
     rs.LockType = 3

     rs.Open()

 
'***Send the results via email to someone
 dim smtp
 Set smtp = Server.CreateObject("CDONTS.NewMail")
 
 smtp.MailFormat = 0 '(Mime format)
 smtp.From = "someone"
 smtp.To = sAnvepost
 smtp.Subject = ""
 smtp.Body = ": " & vbcrlf&_
                + "" & vbcrlf&_
               + "Username= " + Usermail& vbcrlf&_
               + "Password= " + Usermail& vbcrlf&_
               + "" & vbcrlf&_
               + "Good luck! " 

               
  smtp.Send
  Set smtp = Nothing
end if
%>
..some html
0
AnnaCflAuthor Commented:
Hi, I've now tested a few new things. And that led me to another error message. It seems it is in the string
rs.Source = select Email from dbo.UserData where Email='" & Usermail & "'"
the problem is.

This is the error message I receive if I exclude the string.

'rs.Source = select Email from dbo.UserData where Email='" & Usermail & "'"

Microsoft OLE DB Provider for ODBC Drivers error '80040e0c'

This is the error message I recive when I include the string.
Microsoft VBScript compilation error '800a03ea'

Syntax error

/mailpassword.asp, line 95

rs.Source = Select Email from dbo.UserData where Email='" & Usermail & "'"

Command text was not set for the command object.

I've also tested that the if statement works. It does.

0
MannSoftCommented:
I've never worked with recordset objects that way, but I would imagine that you need to put ""'s around the RS.Source line.  IE:

RS.Source = "SELECT EMail FROM DBO.UserData WHERE Email='" & Usermail & "'"
0
AnnaCflAuthor Commented:
Yes it seems that I forgot the"" part!!!

But something is still wrong. Now I get this error when I'm trying to create a variabel of what selected in the database.

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.

/mailpassword.asp, line 102

How do I do to be able to use the selected part in db?

<%
Dim rs, flag, Usermail, Userpwd, SAnvepost,iErr, SQL
iErr = 1  
Usermail= Request.querystring("epost")
 
if Request.querystring.Count > 0 then    
     
   flag="ADODB.Recordset"
   set rs = Server.CreateObject(flag)
   rs.ActiveConnection = mail_STRING
 
   SQL = "Select Email from UserData where Email='" & Usermail & "'"
   if rs.RecordCount > 0 then
     sAnvepost = rs("Email")
          'Userpwd = rs("Password")
   iErr = 0    
   end if    
   rs.CursorType = 0
   rs.CursorLocation = 2
   rs.LockType = 3
   rs.Open()
....same code as before

If I had more points to give you, I would!!
Hopefully it's an easy fix for someone who has done this before?!
/Anna
0
MannSoftCommented:
One new problem is that in your most recent change you went from assigning the SQL query to the RS.Source variable to the SQL variable.  But what that error message is probably referring to is the fact that you check RS.RecordCount before calling RS.Open.

So try this:

if Request.querystring.Count > 0 then    
  flag="ADODB.Recordset"
  set rs = Server.CreateObject(flag)
  rs.ActiveConnection = mail_STRING
  RS.Source = "Select Email from UserData where Email='" & Usermail & "'"
  rs.CursorType = 0
  rs.CursorLocation = 2
  rs.LockType = 3
  rs.Open()
  if rs.RecordCount > 0 then
    sAnvepost = rs("Email")
         'Userpwd = rs("Password")
  iErr = 0    
  end if    
0
AnnaCflAuthor Commented:
Yes, you were right about that (rs.Open()), thank you!
But one new problem arised..
RecordCount never becomes > 0.
So it seems somethings still wrong...cause in the analyzer query still works fine. And what I'm trying to find is in the database! Now I don't get any errors but it seems it never checks in the database or what do you think? When I run this I always get my "else" message "Count no".

/Anna

This is how my code looks now.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/mail.asp" -->
<%
Response.Expires = -1
%>

..some html...
<%
Dim rs, flag, Usermail, Userpwd, SAnvepost,iErr
iErr = 1
Usermail= Request.querystring("epost")
if Request.querystring.Count > 0 then    
 flag="ADODB.Recordset"
 set rs = Server.CreateObject(flag)
 rs.ActiveConnection = mail_STRING
 RS.Source = "Select Email from dbo.UserData where Email='" & Usermail & "'"
 rs.CursorType = 0
 rs.CursorLocation = 2
 rs.LockType = 3
 rs.Open()
 if rs.RecordCount > 0 then    
         sAnvepost = rs(Email)
        'Userpwd = rs("Password")
       iErr = 0    
 else
     response.write("Count no")
 end if  

'***Send the results via email to someone
 if iErr = 0 then

 dim smtp
 Set smtp = Server.CreateObject("CDONTS.NewMail")
 
 smtp.MailFormat = 0 '(Mime format)
 smtp.From = "someone"
 smtp.To = Usermail
 smtp.Subject = ""
 smtp.Body = ": " & vbcrlf&_
                + "" & vbcrlf&_
               + "Username= " + Usermail& vbcrlf&_
               + "Password= " + Usermail& vbcrlf&_
               + "" & vbcrlf&_
               + "Good luck! " 

     
  smtp.Send
  Set smtp = Nothing
  end if
end if
%>

...some html...
0
AnnaCflAuthor Commented:
Hello again!!
I've now tried to use a dsnless connection to see if that worked better. But no:-( Maybe someone have any suggestion how to move on from here. Either using my old code or this one with dsnless connection. Both result in the same problem as mentioned before. The recordcount never becomes > 0

Please help me!!!

This is my new code:

<%
Dim Usermail, sql, sAnvepost, iErr
iErr = 1
Usermail= Request.querystring("epost")
if Request.querystring.Count > 0 then

   Dim strConn
   Dim objConnection
   Dim rs

   set objConnection = Server.CreateObject("ADODB.Connection")
   strConn = "Provider=SQLOLEDB; Data Source=name of my server; Initial Catalog=my database; ;User Id=; Password="  
 
   objConnection.open strConn
   
   set rs=Server.CreateObject("ADODB.recordset")
   
   sql = "Select Email from dbo.UserData where Email='" & Usermail & "'"
   rs.Open sql, strConn
   objConnection.Execute sql
     if rs.RecordCount > 0 then    
              sAnvepost = rs(Email)
             'Userpwd = rs("Password")
            iErr = 0    
      else
          response.write("Count no")
      end if
   rs.Close
   objConnection.close

'end if


'***Send the results via email to someone
if iErr = 0 then
 
 dim smtp
 Set smtp = Server.CreateObject("CDONTS.NewMail")
 
 smtp.MailFormat = 0 '(Mime format)
 smtp.From = ""
 smtp.To = Usermail
 smtp.Subject = ""
 smtp.Body = ": " & vbcrlf&_
                + "" & vbcrlf&_
               + "Username= " + Usermail& vbcrlf&_
               + "Password= " + Usermail& vbcrlf&_
               + "" & vbcrlf&_
               + "Good luck! " 

     
  smtp.Send
  Set smtp = Nothing
end if
end if
%>
0
AnnaCflAuthor Commented:
Yes, it's me again...
When I changed my If statement to this,
 if rs.RecordCount > 0 then  
    iErr = 0    
 else
 response.write("Count no")
 sAnvepost = rs(Email)
 end if

I receive this message.
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.

So my question is can the problem be in
sAnvepost = rs(Email)

Please help me to get this right.

Best regards Anna
0
MannSoftCommented:
You must be using a cursor type that doesnt support the RecordCount property, so you'll need to change it (Ive never used anything but the default so dont know what you should change it to).  I would just avoid it altogether and check EOF instead.  IE:

if (RS.RecordCount > 0) then

is the equivalent of

if Not(RS.EOF) then
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MannSoftCommented:
That should be RS("Email"), assuming EMail is the name of the column in the table.
0
AnnaCflAuthor Commented:
Finally!! It's working.
Thank you for your good advices, they all added up to a good result. My last change was to add these lines.
Const adOpenStatic = 3
Const adLockOptimistic = 3
rs.Open sql, strConn, adOpenStatic, adLockOptimistic
I can do it this way as well and exclude const ..
rs.Open sql, strConn, 3, 3

Thanks again
/Anna
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.