Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Code Support: ASP (Non-Forum Related)
 SQL Server - select records
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

redbrad0
Advanced Member

USA
3725 Posts

Posted - 25 March 2002 :  02:37:01  Show Profile  Visit redbrad0's Homepage  Send redbrad0 an AOL message
Ok I am using the following select statment but its not finding anything..

SELECT CustomersProfiles_EmailAddress FROM CustomersProfiles WHERE CustomersProfiles_CustomerNumber='10064' AND CustomersProfiles_MasterProfile='Y'

Here is the table layout..
CustomersProfiles_EmailAddress nvarchar(50)
CustomersProfiles_CustomerNumber nvarchar(10)CustomersProfiles_MasterProfile nvarchar(1)

And This is the record I am trying to return...
CustomersProfiles_EmailAddress = tim@dproducts.com
CustomersProfiles_CustomerNumber = 10064
CustomersProfiles_MasterProfile = Y

Can anyone see whats wrong?


Brad
Web Hosting with SQL Server @ $24.95 per month

Gremlin
General Help Moderator

New Zealand
7528 Posts

Posted - 25 March 2002 :  05:03:54  Show Profile  Visit Gremlin's Homepage
What Column type is CustomersProfiles_CustomerNumber ?? If its Int or numeric you might want to remove the quotes from the comparison string e.g CustomersProfiles_CustomerNumber= 10064

www.daoc-halo.com
Go to Top of Page

em_
Starting Member

23 Posts

Posted - 25 March 2002 :  11:36:29  Show Profile
Its nvarchar(10) according to the table description he put up.

The statement looks ok to me. Are you sure you're in the right database when making the query? And the table does contain the data you're hunting for?

Is the recordset at end of file? How do you know its not returning anything?

What kind of database is this? Access or SQL?



Edited by - em_ on 25 March 2002 11:37:22
Go to Top of Page

redbrad0
Advanced Member

USA
3725 Posts

Posted - 25 March 2002 :  15:08:47  Show Profile  Visit redbrad0's Homepage  Send redbrad0 an AOL message
Here is the exact code in action...


strSql = "SELECT CustomersProfiles_EmailAddress"
strSql = strSql & " FROM CustomersProfiles"
strSql = strSql & " WHERE CustomersProfiles_CustomerNumber='" & strQueryStringDealer & "'"
strSql = strSql & " AND CustomersProfiles_MasterProfile='Y'"
Response.Write "strSql-" & strSql & "<br>"
set rs = my_Conn.Execute (strSql)

if rs.eof or rs.bof then
strDBCustomersProfiles_EmailAddress = rs("CustomersProfiles_EmailAddress")

strSql = "UPDATE CustomersProfiles "
strSql = strSql & " SET CustomersProfiles_UserProfileStatus='S'"
strSql = strSql & " WHERE CustomersProfiles_CustomerNumber='" & strQueryStringDealer & "'"
my_Conn.Execute (strSql)

strEmailBody = "Your account at DisplayProducts.com has been suspended. "
strEmailBody = strEmailBody & "If you feel you have reached this message an error please contact your "
strEmailBody = strEmailBody & "Display Products Rep." & vbCrLf & vbCrLf
strEmailBody = strEmailBody & "Thanks for your supprt," & vbCrLf
strEmailBody = strEmailBody & "DisplayProducts.com" & vbCrLf

SendEmail "Webmaster", "webmaster@dproducts.com", strFormEmailAddress, strFormEmailAddress, "Your account at DisplayProducts.com", strEmailBody
else
Response.Write "We could not find the email address to send out an email to tell them they have been locked out, so the dealers status has not been updated."
end if


and its sql server

Brad
Web Hosting with SQL Server @ $24.95 per month
Go to Top of Page

em_
Starting Member

23 Posts

Posted - 25 March 2002 :  15:33:33  Show Profile
I think your conditions are messed up.

The condition 'rs.eof or rs.bof' indicates 'no records found' and your action for that is to continue processing. Your action for 'records found' is to display the error message. This should be reversed.

In other words
If rs.eof or rs.bof then
display error 'no records found'
else
continue processing
end if


Em.



Edited by - em_ on 25 March 2002 15:36:17
Go to Top of Page

Gremlin
General Help Moderator

New Zealand
7528 Posts

Posted - 25 March 2002 :  17:53:22  Show Profile  Visit Gremlin's Homepage
possibly just change that line to if not rs.EOF

If your ever unsure whether a query is working, response.write it out and then cut and paste into Query Analyser and run it, you'll soon tell if a recordset is being returned. I had incorrectly assumed that you were running the query from there rather than code.

I've never been too sure on rs.BOF (bottom of file I think) does that mean the 'last record in the recordset' ? I generally just check for EOF and not BOF but I've seen a lot of example code which always does. Just wondering which is more 'correct'

www.daoc-halo.com

Edited by - Gremlin on 25 March 2002 17:54:33
Go to Top of Page

Doug G
Support Moderator

USA
6493 Posts

Posted - 25 March 2002 :  18:27:39  Show Profile
quote:
I've never been too sure on rs.BOF (bottom of file I think) does that mean the 'last record in the recordset' ? I generally just check for EOF and not BOF but I've seen a lot of example code which always does. Just wondering which is more 'correct'

rs.BOF = Beginning Of File
rs.EOF = End of File

If you have a recordset with data, and you are positioned to the first record in the data, both rs.BOF and rs.EOF will be false.

If you are positioned to the first record and issue rs.MovePrevious, rs.BOF becomes true and you're not pointed to a valid record.

If you're at the last record in the recordset and issue rs.MoveNext, rs.EOF becomes true.

If you have received an empty recordsed from your query, both rs.BOF and rs.EOF are true.


======
Doug G
======
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 25 March 2002 :  18:35:45  Show Profile  Send ruirib a Yahoo! Message
Actually BOF is Beggining of File. It has the same use of EOF, but used when you navigate the recordset backwards...

-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 25 March 2002 :  18:37:49  Show Profile  Send ruirib a Yahoo! Message
Well I got quite late on this one... That's what happens when you open more topics that the ones you can read...

-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Go to Top of Page

redbrad0
Advanced Member

USA
3725 Posts

Posted - 25 March 2002 :  19:02:05  Show Profile  Visit redbrad0's Homepage  Send redbrad0 an AOL message
lol my god, this is what happens when you try to code at 5 am while drunk. thanks guy i feel sooooo stupid that i didnt notice that.



Brad
Web Hosting with SQL Server @ $24.95 per month
Go to Top of Page

em_
Starting Member

23 Posts

Posted - 25 March 2002 :  20:04:40  Show Profile
I'm a gal....

Go to Top of Page

Gremlin
General Help Moderator

New Zealand
7528 Posts

Posted - 26 March 2002 :  02:25:15  Show Profile  Visit Gremlin's Homepage
Thanks (both of ya) I knew I should really have looked BOF up rather than just assuming it was Bottom !

www.daoc-halo.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 1.67 seconds. Powered By: Snitz Forums 2000 Version 3.4.07