Help in Select Query
I have one table for our project team, which Project team has to update on a daily basis.
The table has 4 columns and some 20-25 rows.
-----------
COLUMN
-----------
Project Name Emp_Code Status Remarks
The column 'STATUS' has three variables like Completed, WIP, Pending.
Team has to update in 'REMARKS' column where status is either 'In Progress' or 'Pending' individually. The form is working fine.
However, I would like to hide all rows, where status is 'COMPLETED'. Can you help me please.
e.g. If RecSta1, RecSta2, RecSta3 or RecSta4 is 'Completed' those row should not be displayed.
Can you help me please.
I have attached the code & db file for your reference.
The table has 4 columns and some 20-25 rows.
-----------
COLUMN
-----------
Project Name Emp_Code Status Remarks
The column 'STATUS' has three variables like Completed, WIP, Pending.
Team has to update in 'REMARKS' column where status is either 'In Progress' or 'Pending' individually. The form is working fine.
However, I would like to hide all rows, where status is 'COMPLETED'. Can you help me please.
e.g. If RecSta1, RecSta2, RecSta3 or RecSta4 is 'Completed' those row should not be displayed.
Can you help me please.
I have attached the code & db file for your reference.
"SELECT RecID, RecCode, RecSta1, RecRem1, RecSta2, RecRem2, RecSta3, RecRem3, RecSta4, RecRem4 FROM Project where RecCode='10409' ORDER BY RecID ASC"
*****MY CODE******
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
If Request("Submit") <> "" Then
intRecIDs = Replace(Request("hidRecIDs"), "*", "") ' remove all the asterisks, to create a list like this: 2, 5, 8, 9 etc.
arrRecIDs = Split(intRecIDs, ", ") ' Create an array, wich will contain just the IDs of the records we need to update
For i = 0 to Ubound(arrRecIDs) ' Loop trough the array
strTexa = Replace(Request("txtRem1" & arrRecIDs(i)), "'", "''")
strTexb = Replace(Request("txtRem2" & arrRecIDs(i)), "'", "''")
strTexc = Replace(Request("txtRem3" & arrRecIDs(i)), "'", "''")
strTexd = Replace(Request("txtRem4" & arrRecIDs(i)), "'", "''")
set commUpdate = Server.CreateObject("ADODB.Command")
commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("Project.mdb") & ";Persist Security Info=False"
commUpdate.CommandText = "UPDATE ProjectT SET RecRem1 = '" & strTexa & "', RecRem2 = '" & strTexb & "', RecRem3 = '" & strTexc & "', RecRem4 = '" & strTexd & "' WHERE RecID = " & arrRecIDs(i)
commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()
Next
strMessage = i & " Records Updated"
Response.Redirect("Project.asp?Message=" & strMessage)
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("Project.mdb") & ";Persist Security Info=False"
Recordset1.Source = "SELECT RecID, RecSta1, RecRem1, RecSta2, RecRem2, RecSta3, RecRem3, RecSta4, RecRem4 FROM ProjectT where RecCode='10409' ORDER BY RecID ASC"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Attendance Management</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">
<!--
// When the value in a textfield is changed, notice the onChange="RecUpdate('<%= intRecID %>')"
// on each of the textfields, the value of the Record ID associated with that field
// is passed to the RecUpdate function. First the value is surounded with 2 asterisks e.g. *6*
// This is so that *1* can be distinguished from *10*, *11* etc.
function RecUpdate(RecID){
var ThisID = "*" + (RecID) + "*"
if (document.form1.hidRecIDs.value == ""){ // If the hidden field is empty
document.form1.hidRecIDs.value = (ThisID) // Store the value in the hidden field (hidRecIDs) as it is.
}
if (document.form1.hidRecIDs.value != ""){ // If the hidden field isn't empty
var str = document.form1.hidRecIDs.value; // Store the contents of the hidden field in the variable str
var pos = str.indexOf(ThisID); // Search str to see if this RecID is allready in it.
if (pos == -1) { // If the position returned is -1 it isn't allredy in there,
document.form1.hidRecIDs.value = document.form1.hidRecIDs.value + ", " + (ThisID)
} // so add ", " and this ID to what is already in hidRecIDs
} // to create a list like this *2*, *5*, *8* etc.
}
//-->
</script>
</head>
<body>
<font size="2" face="Arial, Helvetica, sans-serif"><%= Request.QueryString("Message") %></font>
<form name="form1" method="post" action="Project.asp">
<table width="613" border="0" cellpadding="0" cellspacing="0">
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<% intRecID =(Recordset1.Fields.Item("RecID").Value) ' Store the current RecordID in a variable %> <tr>
<td colspan="3" bgcolor="#FFCC99"> <font size="2" face="Arial"> Emp ID.:<%= intRecID %><input name="hidRecID<%= intRecID %>" type="hidden" value="<%= intRecID %>" size="5" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC">
</td>
</tr>
<tr>
<td align="center" width="227" bgcolor="#CCFFCC">
<font size="1" face="Arial">Date</font></td>
<td align="center" width="74" bgcolor="#CCFFCC">
<font size="1" face="Arial">Status</font></td>
<td align="center" width="311" bgcolor="#CCFFCC">
<font size="1" face="Arial">Remarks</font></td>
</tr>
<tr>
<td align="center" width="227" bgcolor="#CCFFCC"> </td>
<td align="center" width="74" bgcolor="#CCFFCC"> </td>
<td align="center" width="311" bgcolor="#CCFFCC"> </td>
</tr>
<tr>
<td width="227" bgcolor="#CCFFCC">
<font size="1" face="Arial"> Project - 1</font></td>
<td width="74" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
<input name="txtSta1<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecSta1").Value)%>" size="10" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC; text-align:center"></font></td>
<td width="311" align="center" bgcolor="#CCCC00"><font size="1" face="Arial, Helvetica, sans-serif">
<input name="txtRem1<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecRem1").Value)%>" size="45" style="font-family: Arial; font-size: 8pt; background-color:#FFFF00"></font></td>
</tr>
<tr>
<td width="227" bgcolor="#CCFFCC">
<font size="1" face="Arial"> Project - 2</font></td>
<td width="74" align="center" bgcolor="#CCCCCC">
<font size="1" face="Arial, Helvetica, sans-serif">
<input name="txtSta2<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecSta2").Value)%>" size="10" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC; text-align:center"></font></td>
<td width="311" align="center" bgcolor="#CCCC00"><font size="1" face="Arial, Helvetica, sans-serif">
<input name="txtRem2<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecRem2").Value)%>" size="45" style="font-family: Arial; font-size: 8pt; background-color:#FFFF00"></font></td>
</tr>
<tr>
<td width="227" bgcolor="#CCFFCC">
<font size="1" face="Arial"> Project - 3</font></td>
<td width="74" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
<input name="txtSta3<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecSta3").Value)%>" size="10" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC; text-align:center"></font></td>
<td width="311" align="center" bgcolor="#CCCC00"><font size="1" face="Arial, Helvetica, sans-serif">
<input name="txtRem3<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecRem3").Value)%>" size="45" style="font-family: Arial; font-size: 8pt; background-color:#FFFF00"></font></td>
</tr>
<tr>
<td width="227" bgcolor="#CCFFCC">
<font size="1" face="Arial"> Project - 4</font></td>
<td width="74" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
<input name="txtSta4<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecSta4").Value)%>" size="10" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC; text-align:center"></font></td>
<td width="311" align="center" bgcolor="#CCCC00"><font size="1" face="Arial, Helvetica, sans-serif">
<input name="txtRem4<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecRem4").Value)%>" size="45" style="font-family: Arial; font-size: 8pt; background-color:#FFFF00"></font></td>
</tr>
<tr>
<td width="613" colspan="3"><b> </b></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table>
<br>
<input name="hidRecIDs" type="text" size="40">
<font size="2" face="Arial, Helvetica, sans-serif">Selected Records</font><br>
<br>
<font size="2" face="Arial, Helvetica, sans-serif">
<input type="submit" name="Submit" value="Update">
</font>
<input name="hidRecID<%= intRecID %>" type="hidden" value="<%= intRecID %>" size="5" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC">
</form>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
add
RecSta1<>'COMPLETED' and RecSta2<>'COMPLETED' and RecSta3<>'COMPLETED' and RecSta4<>'COMPLETED'
to your select query
RecSta1<>'COMPLETED' and RecSta2<>'COMPLETED' and RecSta3<>'COMPLETED' and RecSta4<>'COMPLETED'
to your select query
Recordset1.Source = "SELECT RecID, RecSta1, RecRem1, RecSta2, RecRem2, RecSta3, RecRem3, RecSta4, RecRem4 FROM ProjectT where RecCode='10409' and RecSta1<>'COMPLETED' and RecSta2<>'COMPLETED' and RecSta3<>'COMPLETED' and RecSta4<>'COMPLETED' ORDER BY RecID ASC"
Modify your SELECT statement like this:
Recordset1.Source = "SELECT RecID, RecSta1, RecRem1, RecSta2, RecRem2, RecSta3, RecRem3, RecSta4, RecRem4 FROM ProjectT where RecCode='10409'
and (RecSta1<>'COMPLETED' or RecSta2<>'COMPLETED' or RecSta3<>'COMPLETED' or RecSta4<>'COMPLETED') ORDER BY RecID ASC"
Recordset1.Source = "SELECT RecID, RecSta1, RecRem1, RecSta2, RecRem2, RecSta3, RecRem3, RecSta4, RecRem4 FROM ProjectT where RecCode='10409'
and (RecSta1<>'COMPLETED' or RecSta2<>'COMPLETED' or RecSta3<>'COMPLETED' or RecSta4<>'COMPLETED') ORDER BY RecID ASC"
Premium Content
You need an Expert Office subscription to comment.Start Free Trial