?
Solved

Problem with recordset..

Posted on 2003-03-19
21
Medium Priority
?
198 Views
Last Modified: 2012-05-04
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!!
     
0
Comment
Question by:AnnaCfl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 4
  • +1
21 Comments
 
LVL 9

Expert Comment

by:AlfaNoMore
ID: 8166825
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
 

Author Comment

by:AnnaCfl
ID: 8167060
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
 
LVL 9

Expert Comment

by:AlfaNoMore
ID: 8167252
So what's not happening? Any errors?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Expert Comment

by:MannSoft
ID: 8167384
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
 
LVL 5

Expert Comment

by:DesertWarrior
ID: 8167582
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
 
LVL 6

Expert Comment

by:MannSoft
ID: 8167709
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
 
LVL 5

Expert Comment

by:DesertWarrior
ID: 8167991
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
 
LVL 5

Expert Comment

by:DesertWarrior
ID: 8168669
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
 
LVL 6

Expert Comment

by:MannSoft
ID: 8168922
Yes, you're right.  Im not sure what your point in relation to this question is though.
0
 
LVL 5

Expert Comment

by:DesertWarrior
ID: 8169404
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
 

Author Comment

by:AnnaCfl
ID: 8172121
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
 

Author Comment

by:AnnaCfl
ID: 8172238
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
 
LVL 6

Expert Comment

by:MannSoft
ID: 8173691
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
 

Author Comment

by:AnnaCfl
ID: 8174330
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
 
LVL 6

Expert Comment

by:MannSoft
ID: 8175166
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
 

Author Comment

by:AnnaCfl
ID: 8179365
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
 

Author Comment

by:AnnaCfl
ID: 8180020
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
 

Author Comment

by:AnnaCfl
ID: 8180680
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
 
LVL 6

Accepted Solution

by:
MannSoft earned 60 total points
ID: 8180756
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
 
LVL 6

Expert Comment

by:MannSoft
ID: 8180819
That should be RS("Email"), assuming EMail is the name of the column in the table.
0
 

Author Comment

by:AnnaCfl
ID: 8194602
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

752 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