Author |
Topic |
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 25 March 2002 : 02:37:01
|
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
|
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 |
|
|
em_
Starting Member
23 Posts |
Posted - 25 March 2002 : 11:36:29
|
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 |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 25 March 2002 : 15:08:47
|
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
|
|
|
em_
Starting Member
23 Posts |
Posted - 25 March 2002 : 15:33:33
|
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 |
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 25 March 2002 : 17:53:22
|
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 |
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 25 March 2002 : 18:27:39
|
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 ====== |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 25 March 2002 : 18:35:45
|
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 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 25 March 2002 : 18:37:49
|
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 |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
em_
Starting Member
23 Posts |
Posted - 25 March 2002 : 20:04:40
|
I'm a gal....
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 26 March 2002 : 02:25:15
|
Thanks (both of ya) I knew I should really have looked BOF up rather than just assuming it was Bottom !
www.daoc-halo.com |
|
|
|
Topic |
|