The online form will allow employees to enter the meter information.
Employee will enter EmployeeID, Select region from drop down, Select dates from dropdown, and enter Meter No and select MeterIN/OUT(sometime employee will have 20 meters at a time and they have to enter meter no's and select MeterIN/OUT.) with same Employee ID and region information.
My suggestion was to give 5 text boxes for the Meter No's and and Selection for MeterIN/OUT.
If they have more, They will load 2nd time form and enter employee Id , region,dates and meterno's
Please help me , if you have any better ideas to perform this requirement succesfully.
I have to create a new form and new database. I will appreciate, if you can give datbase suggestion also. I am thinking of Access database. Please help me with database structure for the feilds.
Please give me suggestions, How to design the form and database with this kind of requirement.
I will really appreciate your help on this
Thank
Suruku
This is how I did the DB structure (the fine tune)...
ID - AutoNumber
EmployeeID - Text [7]
MeterDate - Date/Time
Region - Text [25]
MeterSerialNo - Text [10]
Status - Text [3]
AddMeter.asp ==========================
<%@ Language=VBScript %>
<%Option Explicit%>
<%
'Function / Sub Variables
Dim objCon
Dim sTempStr, sTmpStr, iTempVal, iTmpVal
'Page Variables
Dim sForm(2), sData(9,1), sErrMsg, sQuery
Dim iLp, iLoop
Dim bIsMeter
OpenDB
'Load IN / OUT Deafult
For iLp = 0 to 9
sData(iLp,1) = "OUT"
Next
'Get Passed Back Form Data After INSERT
If CBool(Request.QueryString(
sForm(0) = Request.QueryString("empid
sForm(1) = Request.QueryString("date"
sForm(2) = Request.QueryString("reg")
End If
'Form Has Been Submitted
If Request.Form("Action") = "Add" then
sErrMsg = ""
'Collect Up The General Info
For iLp = 0 to 2
sForm(iLp) = Trim(Request.Form("f_"&iLp
Next
'Collect Up The Meter Info
For iLp = 0 to 9
For iLoop = 0 to 1
sData(iLp,iLoop) = Trim(Request.Form("m_"&iLo
Next
Next
'Start To Validate Form
If sErrMsg = "" then
'Employee ID
If sForm(0) = "" then
sErrMsg = "Error: Employee ID - required field."
Else
'Date
If sForm(1) = "" then
sErrMsg = "Error: Date - required field."
Else
sForm(1) = PadDate(sForm(1))
If ValidateDate(sForm(1),sErr
sForm(1) = sErrMsg
sErrMsg = ""
'Region
If sForm(2) = "" then
sErrMsg = "Error: Region - required field."
Else
sForm(2) = SetCap(sForm(2))
'Meter Info
bIsMeter = False
For iLp = 0 to 9
If sData(iLp,0) = "7" or sData(iLp,0) = "" then
sData(iLp,0) = ""
Else
If Len(sData(iLp,0)) <> 7 then
sErrMsg = "Error: Meter No (" & sData(iLp,0) & ") - wrong length."
Else
bIsMeter = True
End If
End If
If sErrMsg <> "" then
iLp = 9
End If
Next
If sErrMsg = "" then
'All Ok - INSERT Details into DB
If bIsMeter = False then
sErrMsg = "Error: No Meter details - required."
Else
If sErrMsg = "" then
For iLp = 0 to 9
If sData(iLp,0) <> "" then
sQuery = "INSERT INTO GasMeterTrack (EmployeeID, MeterDate, Region, MeterSerialNo, Status) VALUES " &_
"('" & SQL(sForm(0)) & "','" & sForm(1) & "','" & SQL(sForm(2)) & "'," &_
"'" & SQL(sData(iLp,0)) & "','" & sData(iLp,1) & "')"
objCon.Execute sQuery
End If
Next
Response.Redirect "AddMeter.asp?pass=True&em
End If
End If
End If
End If
End If
End If
End If
End If
End If
CloseDB
%>
<html>
<head>
<title>Meter Information</title>
<script language="JavaScript">
<!--
function AddPrefix(Number, Object)
{
if(Object.value=="") Object.value=7
}
//-->
</script>
</head>
<body>
<table width="500" cellspacing="1" cellpadding="0" border="0" bgcolor="#000000">
<form name="Reading" method="post" action="AddMeter.asp">
<tr bgcolor="#FFFFFF">
<td>
<table width="500" cellspacing="0" cellpadding="2" border="0">
<tr>
<td height="30"><b>M E T E R I N F O R M A T I O N</b></td>
</tr>
</table>
<td>
</tr>
<%If sErrMsg <> "" then%>
<tr bgcolor="#FFFFFF">
<td>
<table width="500" cellspacing="0" cellpadding="2" border="0">
<tr>
<td height="30"><font color="#FF0000"><%=sErrMsg
</tr>
</table>
</td>
</tr>
<%End If%>
<%If CBool(Request.QueryString(
<tr bgcolor="#FFFFFF">
<td>
<table width="500" cellspacing="0" cellpadding="2" border="0">
<tr>
<td height="30">Notice: Info Successfully inserted into DB.</td>
</tr>
</table>
</td>
</tr>
<%End If%>
<tr bgcolor="#FFFFFF">
<td>
<table width="500" cellspacing="0" cellpadding="2" border="0">
<tr>
<td colspan="2" height="30"><b>General Details:</b></td>
</tr>
<tr>
<td width="100" height="25">Employee ID:</td>
<td width="400">
<input type="text" name="f_0" size="5" maxlength="5" value="<%=sForm(0)%>">
</td>
</tr>
<tr>
<td height="25">Date:</td>
<td>
<input type="text" name="f_1" size="10" maxlength="10" value="<%=sForm(1)%>">
</td>
</tr>
<tr>
<td height="25">Region:</td>
<td>
<input type="text" name="f_2" size="25" maxlength="25" value="<%=sForm(2)%>">
</td>
</tr>
</table>
</td>
</tr>
<tr bgcolor="#FFFFFF">
<td>
<table width="500" cellspacing="0" cellpadding="2" border="0">
<tr>
<td colspan="4" height="30"><b>Meter Readings:</b></td>
</tr>
<%For iLp = 0 to 9%>
<tr>
<td width="100" height="25">Meter No:</td>
<td width="125">
<input type="text" name="m_0_<%=iLp%>" size="6" maxlength="7" value="<%=sData(iLp,0)%>" onMouseDown="AddPrefix(7,t
</td>
<td width="50">Status:</td>
<td width="225">
<input type="radio" name="m_1_<%=iLp%>" value="OUT" <%If sData(iLp,1) = "OUT" then%>checked<%End If%>> OUT
<input type="radio" name="m_1_<%=iLp%>" value="IN" <%If sData(iLp,1) = "IN" then%>checked<%End If%>> IN
</td>
</tr>
<%Next%>
</table>
</td>
</tr>
<tr bgcolor="#FFFFFF">
<td>
<table width="500" cellspacing="0" cellpadding="2" border="0">
<tr>
<td height="30" align="center">
<input type="hidden" name="Action" value="Add">
<input type="submit" name="Button" value="Click to store">
</td>
</tr>
</table>
</td>
</tr>
</form>
</table>
</body>
</html>
<%
Function PadDate(Value)
If Len(Value) = 0 then
PadDate = Value
Else
If IsDate(Value) then
sTmpStr = ""
sTempStr = ""
iTempVal = 0
For iLp = 1 to Len(Value)
If IsNumeric(Mid(Value,iLp,1)
sTempStr = sTempStr & Mid(Value,iLp,1)
Else
If Len(sTempStr) > 2 then
PadDate = Value
Else
sTempStr = Left("00",2-Len(sTempStr))
sTmpStr = sTmpStr & sTempStr
sTempStr = ""
End If
End If
Next
If Len(sTempStr) > 4 then
PadDate = Value
Else
PadDate = sTmpStr & Left("2000",4-Len(sTempStr
End If
Else
PadDate = Value
End If
End If
End Function
Function ValidateDate(Value, sErrMsg)
If Len(Value) <> 10 then
ValidateDate = False
sErrMsg = "Error: Date - invalid."
Else
If IsNumeric(Mid(Value,1,2)) and IsNumeric(Mid(Value,4,2)) and IsNumeric(Mid(Value,7,4)) then
Value = Mid(Value,1,2) & "/" & Mid(Value,4,2) & "/" & Mid(Value,7,4)
If IsDate(Value) then
ValidateDate = True
sErrMsg = Value
Else
ValidateDate = False
sErrMsg = "Error: Date - invalid."
End If
Else
ValidateDate = False
sErrMsg = "Error: Date - invalid."
End If
End If
End Function
Sub OpenDB
Set objCon = Server.CreateObject("ADODB
objCon.Open "PROVIDER=Microsoft.Jet.OL
End Sub
Sub CloseDB
Set objCon = Nothing
End Sub
Function SQL(Value)
SQL = Replace(Value,"'","''")
End Function
Function SetCap(Value)
If Value <> "" Then
sTempStr = ""
sTempStr = sTempStr & UCase(Mid(Value, 1, 1))
For iTempval = 2 To Len(Value)
If Mid(Value, iTempval-1, 1) = " " Then
sTempStr = sTempStr & UCase(Mid(Value, iTempval, 1))
Else
sTempStr = sTempStr + Mid(Value, iTempval, 1)
End If
Next
SetCap = sTempStr
End If
End Function
%>
==========================