select convert(varchar,y)+'-'+convert(varchar,m) as ym
from (SELECT DISTINCT year(fill_date) as y,month(fill_date) as m FROM wguses) as a order by y,m desc
select convert(varchar,y)+'-'+convert(varchar,m) as ym
from (SELECT DISTINCT year(fill_date) as y,month(fill_date) as m FROM wguses) as a order by y,m desc
<select id="month_fueling" name="month_fueling" >
<%
Dim sql,rstemp,month_fueling
Set rstemp = Server.CreateObject("ADODB.Recordset")
sql = "select convert(varchar,y)+'/'+convert(varchar,m) as ym from (SELECT DISTINCT year(fill_date) as y,month(fill_date) as m FROM wguses) as a order by y,m desc "
rstemp.open sql,sConn
While Not rstemp.EOF
datey=rstemp("y")
datem=rstemp("m")
%>
<option value="<% =datey&datem %>">
<% =datey %> - <% =datem %>
</option>
<%
rstemp.MoveNext
Wend
set rstemp = nothing
%>
</select>
Date :
2012-02-14 13:21:24
By :
imperator
No. 5
Guest
Code (ASP)
<select id="month_fueling" name="month_fueling" >
<%
Dim sql,rstemp,month_fueling
Set rstemp = Server.CreateObject("ADODB.Recordset")
sql = "SELECT DISTINCT year(fill_date) as y,month(fill_date) as m FROM wguses order by y,m desc"
rstemp.open sql,sConn
While Not rstemp.EOF
datey=rstemp("y")
datem=rstemp("m")
%>
<option value="<% =datey&datem %>">
<% =datey %> - <% =datem %>
</option>
<%
rstemp.MoveNext
Wend
set rstemp = nothing
%>
</select>
ลองดูครับ
Date :
2012-02-14 13:29:36
By :
Thep
No. 6
Guest
เปลี่ยนมาใช้อันนี้
Code (ASP)
sql = "SELECT DISTINCT year(fill_date) as y,month(fill_date) as m FROM wguses order by year(fill_date),month(fill_date) desc"
<select id="month_fueling" name="month_fueling" >
<%
Dim sql,rstemp,d1,d2,month_fueling
Set rstemp = Server.CreateObject("ADODB.Recordset")
sql = "SELECT TOP 4 year(fill_date) AS yr, month(fill_date) AS mt FROM wguses GROUP BY year(fill_date) , month(fill_date) ORDER BY yr DESC, mt DESC "
rstemp.open sql,sConn
While Not rstemp.EOF
%>
<option value="<% =rstemp("mt")&","&rstemp("yr") %>">
<% =Monthname(rstemp("mt")) %> - <% =rstemp("yr")%>
</option>
<%
rstemp.MoveNext
Wend
%>
</select>
Set rstemp = Server.CreateObject("ADODB.Recordset")
sql = "SELECT *, ISNULL(qty,0) AS qty2, ISNULL(unit,0) As unit2 FROM wguses Where month(fill_date) = '"&mon_split(0)&"' and year(fill_date) = '"&mon_split(1)&"' ORDER BY fill_date "
mon_split = Split(request.form ("month_fueling"),",")
rstemp.open sql,sConn,1,3
ติดปัญหาว่า....บรรทัด sql Error :
Microsoft VBScript runtime error '800a0009'
Subscript out of range: '[number: 0]'
หาไม่เจอว่า error จากตรงไหน
Date :
2012-02-15 09:51:05
By :
imperator
No. 10
Guest
Code (ASP)
mon_split = Split(request.form("month_fueling"),",")
sql = "SELECT *, ISNULL(qty,0) AS qty2, ISNULL(unit,0) As unit2 FROM wguses Where month(fill_date) = '"&mon_split(0)&"' and year(fill_date) = '"&mon_split(1)&"' ORDER BY fill_date "
rstemp.open sql,sConn,1,3
<select id="month_fueling" name="month_fueling" >
<%
Dim sql,rstemp,d1,d2,month_fueling
Set rstemp = Server.CreateObject("ADODB.Recordset")
sql = "SELECT TOP 4 year(fill_date) AS yr, month(fill_date) AS mt FROM wguses GROUP BY year(fill_date) , month(fill_date) ORDER BY yr DESC, mt DESC "
rstemp.open sql,sConn
While Not rstemp.EOF
If request.form("month_fueling") = rstemp("mt")&"/"&rstemp("yr") Then
strSel = "selected"
Else
strSel = ""
End IF
%>
<option value="<% =rstemp("mt")&"/"&rstemp("yr") %>" <% =strSel %>>
<% =Monthname(rstemp("mt")) %> - <% =rstemp("yr")%>
</option>
<%
rstemp.MoveNext
Wend
Set rstemp = nothing
%>
</select>
____________________________________________________________________________________
Check for List Record
Code (ASP)
<%
Set rstemp = Server.CreateObject("ADODB.Recordset")
If request.form ("month_fueling") <> "" then
mon_split = Split(request.form ("month_fueling"),"/")
sql = "SELECT *, ISNULL(qty,0) AS qty2, ISNULL(unit,0) As unit2 FROM wguses Where month(fill_date) = '"&mon_split(0)&"' and year(fill_date) = '"&mon_split(1)&"' "
rstemp.open sql,sConn,1,3
While not rstemp.EOF
.
.
.
rstemp.movenext
Wend
Set rstemp = nothing
End If
%>
ประวัติการแก้ไข 2012-02-15 10:48:41
Date :
2012-02-15 10:46:42
By :
imperator
No. 12
Guest
<select id="month_fueling" name="month_fueling" >
<%
Dim sql,rstemp,d1,d2,month_fueling
Set rstemp = Server.CreateObject("ADODB.Recordset")
sql = "select convert(varchar,y)+'-'+case when m < 10 then '0' else '' end+''+convert(varchar,m) as ym from
(SELECT DISTINCT year(fill_date) as y,month(fill_date) as m FROM wguses) as a order by y,m desc"
rstemp.open sql,sConn
While Not rstemp.EOF
If request.form("month_fueling") = rstemp("ym") Then
strSel = "selected"
Else
strSel = ""
End IF
%>
____________________________________________________________________________________
Check for List Record
<%
Set rstemp = Server.CreateObject("ADODB.Recordset")
If request.form("month_fueling") <> "" then
sql = "SELECT *, ISNULL(qty,0) AS qty2, ISNULL(unit,0) As unit2 FROM wguses Where convert(char(6),fill_date,112) = '"&replace(request.form("month_fueling"),"-","")&"' "
rstemp.open sql,sConn,1,3
While not rstemp.EOF
.
.
.
rstemp.movenext
Wend
Set rstemp = nothing
End If
%>
sql = "SELECT *, TOP 4 year(fill_date) AS yr, month(fill_date) AS mt FROM wguses WHERE spemp_code='"&request.form("emp_id")&"' GROUP BY year(fill_date) , month(fill_date) ORDER BY yr DESC, mt DESC "
เงื่อนไขแบบนี้ SELECT *, TOP 4 year(fill_date) AS yr, month(fill_date) AS mt ...
มันขัดใจกันยังไงเหลอครับ รันไม่ออกเลย
คืออยากได้ค่า record อื่นๆด้วยจะได้ไม่ต้องเปิด sql หลายๆครั้ง
ปกติ SELECT TOP 4 year(fill_date) AS yr, month(fill_date) AS mt ...
มันรันออกปกติ
Date :
2012-02-15 16:08:43
By :
imperator
No. 14
Guest
เพราะว่า คาสั่ง GROUP BY จะต้องระบุ filed ที่ต้องการ GROUP ลงไป
SELECT TOP 4 year(fill_date) AS yr, month(fill_date) AS mt FROM wguses GROUP BY year(fill_date) , month(fill_date) ORDER BY yr DESC, mt DESC
อย่างนี้ไม่ได้
SELECT *, TOP 4 year(fill_date) AS yr, month(fill_date) AS mt FROM wguses WHERE spemp_code='"&request.form("emp_id")&"' GROUP BY year(fill_date) , month(fill_date) ORDER BY yr DESC, mt DESC
Load balance : Server 00
