By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

How to view only the latest topup

P: 18
hello,
i need a help regarding sql query, i would like to make my website which contain a database only display a latest topup date that had been made by a person.IF try to make a quey like this recordCollection.Source = "SELECT * FROM Latest Order by TopupDate LIMIT 1" but got error...there will be no error if i put the query only recordCollection.Source = "SELECT * FROM Latest Order by TopupDate" but when i include LIMIT 1,there will be an error..the error is Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in ORDER BY clause.
/Mobile/Mobile.asp, line 69

Plz anyone help me since i really in trouble now..i need to finish my work as soon as possible..
Tq
Sep 17 '07 #1
Share this Question
Share on Google+
15 Replies


FishVal
Expert 2.5K+
P: 2,653
hello,
i need a help regarding sql query, i would like to make my website which contain a database only display a latest topup date that had been made by a person.IF try to make a quey like this recordCollection.Source = "SELECT * FROM Latest Order by TopupDate LIMIT 1" but got error...there will be no error if i put the query only recordCollection.Source = "SELECT * FROM Latest Order by TopupDate" but when i include LIMIT 1,there will be an error..the error is Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in ORDER BY clause.
/Mobile/Mobile.asp, line 69

Plz anyone help me since i really in trouble now..i need to finish my work as soon as possible..
Tq
Hi, Tq.

You may use the following syntax.
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 * FROM Latest ORDER BY TopupDate DESC;
  2.  
Sep 17 '07 #2

P: 18
Hi, Tq.

You may use the following syntax.
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 * FROM Latest ORDER BY TopupDate DESC;
  2.  
Thanks..the code was ok but i want to know if i would like to display all mobile number that had been enter but only the latest topup..that mean i have a lot of mobile number that topup their mobile but i just want to display the latest for each mobile number..how can i do this???

Plzz help me!!
Sep 18 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Thanks..the code was ok but i want to know if i would like to display all mobile number that had been enter but only the latest topup..that mean i have a lot of mobile number that topup their mobile but i just want to display the latest for each mobile number..how can i do this???

Plzz help me!!
Could you give an example?
Sep 18 '07 #4

P: 18
Could you give an example?
I make a system which can view,update,delete and add a data,the data was a topup that had been made by each station,each station will topup and i just want the page to display the latest topup that had been made by each station..for example for Johor,they topup 3 times and i just want to display the latest topup,then for Perak they topup 5 times and i also want to diplay the latest topup that had been made by Perak,..can u understand my explaination and example??

Tq
Sep 18 '07 #5

FishVal
Expert 2.5K+
P: 2,653
I make a system which can view,update,delete and add a data,the data was a topup that had been made by each station,each station will topup and i just want the page to display the latest topup that had been made by each station..for example for Johor,they topup 3 times and i just want to display the latest topup,then for Perak they topup 5 times and i also want to diplay the latest topup that had been made by Perak,..can u understand my explaination and example??

Tq
Please include the MetaData for all relevant datasets. Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Sep 18 '07 #6

P: 18
Please include the MetaData for all relevant datasets. Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Table Name : Latest

ID;Number
MobileNo; Text
State; Text
Location; Text
TopupDate;Date/Time
TopupAmount; Currency
ExpiredDate; Date/Time
Owner; Text
Remark; Text

I want to make a system which is when user enter toupDate and TopupAmount,then they click update button the ExpiredDate will autogenerate and i stuck because i want to display only a latest topup that had been made for all user,they are a lot of user and they make a lot of topup but i just want to display all the latest topup that had been made by each of the user..ill try this command recordCollection.Source = "SELECT * FROM Latest WHERE TopupDate = ( Select Top 1 (TopupDate) FROM Latest as f Where f.Location = Latest.Location )" but this just choose the most top not the most recent Topup...

Plzz Help me..
Sep 19 '07 #7

FishVal
Expert 2.5K+
P: 2,653
Table Name : Latest

ID;Number
MobileNo; Text
State; Text
Location; Text
TopupDate;Date/Time
TopupAmount; Currency
ExpiredDate; Date/Time
Owner; Text
Remark; Text

I want to make a system which is when user enter toupDate and TopupAmount,then they click update button the ExpiredDate will autogenerate and i stuck because i want to display only a latest topup that had been made for all user,they are a lot of user and they make a lot of topup but i just want to display all the latest topup that had been made by each of the user..ill try this command recordCollection.Source = "SELECT * FROM Latest WHERE TopupDate = ( Select Top 1 (TopupDate) FROM Latest as f Where f.Location = Latest.Location )" but this just choose the most top not the most recent Topup...

Plzz Help me..
Hi, there.

Try something like this

Expand|Select|Wrap|Line Numbers
  1. SELECT Latest.*
  2. FROM Latest INNER JOIN (SELECT Latest.Location, Max(Latest.TopupDate) AS MaxOffTopupDate
  3. FROM Latest
  4. GROUP BY Latest.Location) AS TopDates ON (TopDates.MaxOffTopupDate = Latest.TopupDate) AND (TopDates.Location = Latest.Location);
  5.  
Sep 19 '07 #8

P: 18
Hi, there.

Try something like this

Expand|Select|Wrap|Line Numbers
  1. SELECT Latest.*
  2. FROM Latest INNER JOIN (SELECT Latest.Location, Max(Latest.TopupDate) AS MaxOffTopupDate
  3. FROM Latest
  4. GROUP BY Latest.Location) AS TopDates ON (TopDates.MaxOffTopupDate = Latest.TopupDate) AND (TopDates.Location = Latest.Location);
  5.  

Thanx FishVal..

it working now since i used the command that u had suggest..and now i would like to ask u how to make an error handling for mobile no..if user enter a invalid mobile number..an error msg will appear..is this command like this because i try to use this but it seem not functioning if Request.QueryString("S_MobileNo") <> EMPTY then
if isnumeric(Request.QueryString("S_MobileNo")) then
if len(Request.QueryString("S_MobileNo")) = 10 then
if left(Request.QueryString("S_MobileNo"),2) = "01" then
recordSet("MobileNo") = Request.QueryString("S_MobileNo")
ComplyFlag = 1
else
Response.Write("Please enter correct Mobile Number! (019xxxxxxx)")
ComplyFlag = 0
end if
else
Response.Write("Please enter 10 digit Mobile Number!")
ComplyFlag = 0
end if
else
Response.Write("Please enter numeric Mobile Number! (10 digit)")
ComplyFlag = 0
end if
else
Response.Write("Please enter Mobile Number! (10 digit)")
ComplyFlag = 0
end if
would you correct or suggest me something..
TQ
Sep 20 '07 #9

FishVal
Expert 2.5K+
P: 2,653
What programming language is this?
Sep 20 '07 #10

P: 18
What programming language is this?
hi there..
the language that i used
vbscript and asp..
Sep 21 '07 #11

FishVal
Expert 2.5K+
P: 2,653
So what is the problem with the code?
Sep 21 '07 #12

P: 18
So what is the problem with the code?
The error message doesn't appear and the still can add the data with the missing of data..for example,i didnt put any phone number but i still can add the data.
Sep 26 '07 #13

FishVal
Expert 2.5K+
P: 2,653
The error message doesn't appear and the still can add the data with the missing of data..for example,i didnt put any phone number but i still can add the data.
Well.
Code logic seems to be Ok. Though put breakpoint to
Expand|Select|Wrap|Line Numbers
  1. recordSet("MobileNo") = Request.QueryString("S_MobileNo")
  2.  
to check whether this is ever executed. If it is being executed but does not modify underlying table, then check whether you properly open/update/close the recordset.
Sep 26 '07 #14

P: 18
Well.
Code logic seems to be Ok. Though put breakpoint to
Expand|Select|Wrap|Line Numbers
  1. recordSet("MobileNo") = Request.QueryString("S_MobileNo")
  2.  
to check whether this is ever executed. If it is being executed but does not modify underlying table, then check whether you properly open/update/close the recordset.
can you show me how to initialize the complyflag = 0, I've initialize the complyflag=1 already which will connect to the database
if ComplyFlag = 1 then
Set connectionToDatabase=Server.CreateObject("ADODB.Co nnection")
connectionToDatabase.ConnectionTimeout=60
connectionToDatabase.Open "DSN=Mobile"
Set recordSet = Server.CreateObject("ADODB.Recordset")
recordSet.ActiveConnection = connectionToDatabase
recordSet.Source = "SELECT * FROM Latest "
but i dont really know how to initiliaze the complyflag =0
'************************************
Sep 27 '07 #15

FishVal
Expert 2.5K+
P: 2,653
can you show me how to initialize the complyflag = 0, I've initialize the complyflag=1 already which will connect to the database
if ComplyFlag = 1 then
Set connectionToDatabase=Server.CreateObject("ADODB.Co nnection")
connectionToDatabase.ConnectionTimeout=60
connectionToDatabase.Open "DSN=Mobile"
Set recordSet = Server.CreateObject("ADODB.Recordset")
recordSet.ActiveConnection = connectionToDatabase
recordSet.Source = "SELECT * FROM Latest "
but i dont really know how to initiliaze the complyflag =0
'************************************
What does it mean?

complyFlag is a variable being set to 1 or 0 according to validation logic.
The code block you've posted in msg#9 does this. So where is the problem?
Sep 28 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.