Hi everyone!
Can anybody help?
I want to have an automated recordID
with the following format:
<last 2 digits of the current year>+<last 2 digits of the current month>+<number series 001-999>
example: 0409001
(where 04 stands for 2004, 09 for the month of september, then 001 refers to the 1st record)
then when month changed to october the number will be reset to 001 so the recordID now must be 0410001
having that format i can't use the database itself by setting the recordID as autonumber only..
please help on how can i have that format through scripting/coding.
Full Version: *SOLVED* - How to automate RecordID?
Hey, leir, welcome to weborum!
Since you mention autonumber, I'm assuming your using Access. You'll have to do some tricks to get that sort of primary key. Here's some code that'll help:
First, define some variables and then you have to determine what month and year it is.
Since you mention autonumber, I'm assuming your using Access. You'll have to do some tricks to get that sort of primary key. Here's some code that'll help:
First, define some variables and then you have to determine what month and year it is.
| CODE |
<% dim theMonth, theYear, theDate dim rs theYear = right(year(now()), 2) theMonth = month(now()) |
Iif the month is less than 10, you must add the "0" to the front.
| CODE |
if theMonth < 10 then theMonth = "0" & theMonth end if theDate = theYear & theMonth |
OK, now we have our year + month value set.
Next, you'll open your db (I didn't include the code...) I'll assume your db connection is named cn
Now, here's the main query. What it does is give us the highest value of recordID where the first 4 characters are equal to the date variable we created. If there are no records for that date, it will return NULL.
I'm assuming that your recordID column is setup as a text field with 7 characters.
| CODE |
sql = "SELECT MAX (recordID) AS theLast FROM theTable WHERE Left(recordID, 4) = '" & theDate & "'" set rs = Server.CreateObject("ADODB.RecordSet") rs.Open sql, cn, , adLockReadonly |
Since this query will ALWAYS return a result, instead of checking for rs.EOF, we check for isNull. If something exists, increment it and check to see if we need leading zeroes. If not, just set it to 001.
| CODE |
dim theLast if NOT isNull(rs("theLast")) then theLast = cInt(rs("theLast")) + 1 if theLast > 999 then 'what do you do here? else do while len(theLast) < 3 theLast = "0" & theLast Loop end if else theLast = "001" end %> |
That's it. You can then use the variable 'theLast' to insert your new record. Keep in mind that when you reach 999, you'll have a problem. You may want to consider making it larger.
Let me know if you need any help deciphering the code.
hi i tried your code and add the following code to add the data into the database
// your code
if NOT isNull(rs("theLast")) then
theLast = cInt(rs("theLast")) + 1
if theLast > 999 then
'what do you do here?
else
do while len(theLast) < 3
theLast = "0" & theLast
Loop
end if
else
theLast = "001"
end if
// my code
rs.AddNew
rs.Fields("last_name") = request("last_name")
rs.Fields("first_name") = request("first_name")
rs.Fields("sales") = request("sales")
rs.Fields("recordID") = theLast
rs.Update
rs.Close
Set rs = Nothing
----------------------------------------------------
then when i run it the ff.error occurred
Error Type:
Microsoft JET Database Engine (0x80040E09)
Cannot update. Database or object is read-only.
/files/add.asp, line 48
wherein line 48 is rs.AddNew
i already added the user to have the write permission but still the error occurred.
// your code
if NOT isNull(rs("theLast")) then
theLast = cInt(rs("theLast")) + 1
if theLast > 999 then
'what do you do here?
else
do while len(theLast) < 3
theLast = "0" & theLast
Loop
end if
else
theLast = "001"
end if
// my code
rs.AddNew
rs.Fields("last_name") = request("last_name")
rs.Fields("first_name") = request("first_name")
rs.Fields("sales") = request("sales")
rs.Fields("recordID") = theLast
rs.Update
rs.Close
Set rs = Nothing
----------------------------------------------------
then when i run it the ff.error occurred
Error Type:
Microsoft JET Database Engine (0x80040E09)
Cannot update. Database or object is read-only.
/files/add.asp, line 48
wherein line 48 is rs.AddNew
i already added the user to have the write permission but still the error occurred.
Are Access and IIS running on the same machine? If so, you need to check to see what rights the user IUSR_MACHINENAME has for your .mdb file itself (where MACHINENAME is the name of the web server).
Yes it is. The IUSR of my machine has a write permission already and i even enable full access.. Actually i can run other programs. Its just that when i tried that code and saved it then add my code so that it will be entered into the database.. that error occurred.
please help!
Try adding rs.close at the end of my code, then add rs.Open SQL, cn, 1,3,1 before your rs.AddNew line.
still, it didn't work.
below is the whole code (add.asp). kindly check.
----------------------------------------------------------------------
<!-- #include file="adovbs.inc" -->
<%
Dim vPath, pPath, ConString
vPath = "database.mdb"
pPath = Server.MapPath( vPath )
ConString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & pPath & ";" & "JET OLEDB:Database Password=foo"
dim theMonth, theYear, theDate
dim rs
theYear = right(year(now()), 2)
theMonth = month(now())
if theMonth < 10 then
theMonth = "0" & theMonth
end if
theDate = theYear & theMonth
sql = "SELECT MAX (recordID) AS theLast FROM tblsam WHERE Left(recordID, 4) = '" & theDate & "'"
set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open sql, ConString , adLockReadonly
dim theLast
if NOT isNull(rs("theLast")) then
theLast = cInt(rs("theLast")) + 1
if theLast > 999 then
'what do you do here?
else
do while len(theLast) < 3
theLast = "0" & theLast
Loop
end if
else
theLast = "001"
end if
rs.Close
rs.Open sql, ConString, 1,3,1
rs.AddNew
rs.Fields("last_name") = request("last_name")
rs.Fields("first_name") = request("first_name")
rs.Fields("sales") = request("sales")
rs.Fields("recordID") = theLast
rs.Update
Set rs = Nothing
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
The record has been added!
</body>
</html>
new error occured:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in JOIN operation.
(in line 25)
where line 25 is: rs.Open sql, ConString , adLockReadonly
i guess it pertains in the sql statement
what does it mean? and what wld. be the solution?
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in JOIN operation.
(in line 25)
where line 25 is: rs.Open sql, ConString , adLockReadonly
i guess it pertains in the sql statement
what does it mean? and what wld. be the solution?
Thanks for posting your code. Couple of things.
I noticed some flaws in my code
theLast = cInt(rs("theLast")) + 1 (line 31)
should be
theLast = cInt(right(rs("theLast"), 3)) + 1
Then after the big if..then chunk ending at line 41
...else
theLast = "001"
end if
This line needs to be added:
theLast = theDate & theLast
OK. Now the error message you're getting doesn't make any sense to me as there is no JOIN in the sql statement. What it looks like I forgot to suggest for you to do is create a new query for your update rs.
Before the rs.Open sql, ConString, 1,3,1 line, add
sql = "SELECT * FROM tblsam WHERE NULL"
So your code should look like
<!-- #include file="adovbs.inc" -->
<%
Dim vPath, pPath, ConString
vPath = "database.mdb"
pPath = Server.MapPath( vPath )
ConString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & pPath & ";" & "JET OLEDB:Database Password=foo"
dim theMonth, theYear, theDate
dim rs
theYear = right(year(now()), 2)
theMonth = month(now())
if theMonth < 10 then
theMonth = "0" & theMonth
end if
theDate = theYear & theMonth
sql = "SELECT MAX (recordID) AS theLast FROM tblsam WHERE Left(recordID, 4) = '" & theDate & "'"
set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open sql, ConString , adLockReadonly
dim theLast
if NOT isNull(rs("theLast")) then
theLast = cInt(right(rs("theLast"), 3)) + 1
if theLast > 999 then
'what do you do here?
else
do while len(theLast) < 3
theLast = "0" & theLast
Loop
end if
else
theLast = "001"
end if
theLast = theDate & theLast
rs.Close
sql = "SELECT * FROM tblsam WHERE NULL"
rs.Open sql, ConString, 1,3,1
rs.AddNew
rs.Fields("last_name") = request("last_name")
rs.Fields("first_name") = request("first_name")
rs.Fields("sales") = request("sales")
rs.Fields("recordID") = theLast
rs.Update
Set rs = Nothing
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
The record has been added!
</body>
</html>
I've loaded that up locally to test and it works. Sorry for the small hiccups along the way
I noticed some flaws in my code
theLast = cInt(rs("theLast")) + 1 (line 31)
should be
theLast = cInt(right(rs("theLast"), 3)) + 1
Then after the big if..then chunk ending at line 41
...else
theLast = "001"
end if
This line needs to be added:
theLast = theDate & theLast
OK. Now the error message you're getting doesn't make any sense to me as there is no JOIN in the sql statement. What it looks like I forgot to suggest for you to do is create a new query for your update rs.
Before the rs.Open sql, ConString, 1,3,1 line, add
sql = "SELECT * FROM tblsam WHERE NULL"
So your code should look like
CODE
<!-- #include file="adovbs.inc" -->
<%
Dim vPath, pPath, ConString
vPath = "database.mdb"
pPath = Server.MapPath( vPath )
ConString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & pPath & ";" & "JET OLEDB:Database Password=foo"
dim theMonth, theYear, theDate
dim rs
theYear = right(year(now()), 2)
theMonth = month(now())
if theMonth < 10 then
theMonth = "0" & theMonth
end if
theDate = theYear & theMonth
sql = "SELECT MAX (recordID) AS theLast FROM tblsam WHERE Left(recordID, 4) = '" & theDate & "'"
set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open sql, ConString , adLockReadonly
dim theLast
if NOT isNull(rs("theLast")) then
theLast = cInt(right(rs("theLast"), 3)) + 1
if theLast > 999 then
'what do you do here?
else
do while len(theLast) < 3
theLast = "0" & theLast
Loop
end if
else
theLast = "001"
end if
theLast = theDate & theLast
rs.Close
sql = "SELECT * FROM tblsam WHERE NULL"
rs.Open sql, ConString, 1,3,1
rs.AddNew
rs.Fields("last_name") = request("last_name")
rs.Fields("first_name") = request("first_name")
rs.Fields("sales") = request("sales")
rs.Fields("recordID") = theLast
rs.Update
Set rs = Nothing
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
The record has been added!
</body>
</html>
I've loaded that up locally to test and it works. Sorry for the small hiccups along the way
thanks a lot! 
but may i ask another help related to this. First i created a view page that displays all the data then recordID has a link to edit a particular record. But once i clicked the link for that particular recordID "The page cannot be found" displayed on the page. I don't know the reason.
Below is my code for the edit.asp
----------------------------------------------------------------------------------
set conn = server.createobject("adodb.connection")
conn.open ConString
set rs = server.createobject("adodb.recordset")
theID = Request.QueryString("id")
strSQL = "SELECT * FROM tblsam WHERE recordID=" & theID & ";"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic, adCmdText
If Not rs.EOF Then
%>
<form action="save.asp" method="post">
<p> <input type="hidden" name="id" value="<%= rs.Fields("recordID").Value %>" />
</p>
<TABLE BORDER="1" CELLPADDING="0" CELLSPACING="0" STYLE="border-collapse: collapse" BORDERCOLOR="#111111" WIDTH="80%" ID="AutoNumber4" ALIGN="CENTER">
<TR>
<TD WIDTH="33%"> <TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" STYLE="border-collapse: collapse" BORDERCOLOR="#111111" WIDTH="100%" ID="AutoNumber5" HEIGHT="85">
<TR>
<TD > Last Name: </TD>
<TD > <INPUT NAME="last_name" VALUE="<%= Server.HTMLEncode(rs.Fields("last_name").Value) %>">
</TD>
</TR>
<TR>
<TD > First Name: </TD>
<TD > <INPUT NAME="first_name" VALUE="<%= Server.HTMLEncode(rs.Fields("first_name").Value) %>">
</TD>
</TR>
<TR>
<TD > Data: </TD>
<TD ><INPUT NAME="sales" VALUE="<%= Server.HTMLEncode(rs.Fields("sales").Value) %>">
</TD>
</TR>
</TABLE></TD>
</TR>
</TABLE>
<p> </p><p><br />
</p></form><%
Else
Response.Write "No record!"
End If
rs.Close
Set rs = Nothing%>
-------------------------------------------------------------------------
please check. Thanks.
but may i ask another help related to this. First i created a view page that displays all the data then recordID has a link to edit a particular record. But once i clicked the link for that particular recordID "The page cannot be found" displayed on the page. I don't know the reason.
Below is my code for the edit.asp
----------------------------------------------------------------------------------
set conn = server.createobject("adodb.connection")
conn.open ConString
set rs = server.createobject("adodb.recordset")
theID = Request.QueryString("id")
strSQL = "SELECT * FROM tblsam WHERE recordID=" & theID & ";"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic, adCmdText
If Not rs.EOF Then
%>
<form action="save.asp" method="post">
<p> <input type="hidden" name="id" value="<%= rs.Fields("recordID").Value %>" />
</p>
<TABLE BORDER="1" CELLPADDING="0" CELLSPACING="0" STYLE="border-collapse: collapse" BORDERCOLOR="#111111" WIDTH="80%" ID="AutoNumber4" ALIGN="CENTER">
<TR>
<TD WIDTH="33%"> <TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" STYLE="border-collapse: collapse" BORDERCOLOR="#111111" WIDTH="100%" ID="AutoNumber5" HEIGHT="85">
<TR>
<TD > Last Name: </TD>
<TD > <INPUT NAME="last_name" VALUE="<%= Server.HTMLEncode(rs.Fields("last_name").Value) %>">
</TD>
</TR>
<TR>
<TD > First Name: </TD>
<TD > <INPUT NAME="first_name" VALUE="<%= Server.HTMLEncode(rs.Fields("first_name").Value) %>">
</TD>
</TR>
<TR>
<TD > Data: </TD>
<TD ><INPUT NAME="sales" VALUE="<%= Server.HTMLEncode(rs.Fields("sales").Value) %>">
</TD>
</TR>
</TABLE></TD>
</TR>
</TABLE>
<p> </p><p><br />
</p></form><%
Else
Response.Write "No record!"
End If
rs.Close
Set rs = Nothing%>
-------------------------------------------------------------------------
please check. Thanks.
Glad it's working for you
Your form doesn't have a submit button, but it looks OK. If you're getting a 'page not found' error, it doesn't point to an error on your edit.asp page. Look at the way you have your <a> tag written on your display page. I have a feeling you might have forgotten something in the querystring parameters like the ? after your file name, or it could be a simple typo in the file name itself.
Your form doesn't have a submit button, but it looks OK. If you're getting a 'page not found' error, it doesn't point to an error on your edit.asp page. Look at the way you have your <a> tag written on your display page. I have a feeling you might have forgotten something in the querystring parameters like the ? after your file name, or it could be a simple typo in the file name itself.
my display page is working...
below is the code, please check.
---------------------------------------------------------------------------------------
Dim CONN_USER
Dim CONN_PASS
CONN_USER = ""
CONN_PASS = ""
Dim iPageSize
Dim iPageCount
Dim iPageCurrent
Dim strOrderBy
Dim strSQL
Dim iRecordsShown
Dim I
iPageSize = 5
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If
strOrderBy = LCase(Request.QueryString("order"))
Select Case strOrderBy
Case "first_name", "last_name"
' A little pointless, but...
strOrderBy = strOrderBy
Case Else
strOrderBy = "recordID"
End Select
strSQL = "SELECT * FROM tblsam ORDER BY " & strOrderBy & ";"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open ConString, CONN_USER, CONN_PASS
Set rs = Server.CreateObject("ADODB.Recordset")
rs.PageSize = iPageSize
rs.CacheSize = iPageSize
rs.Open strSQL, conn, adOpenStatic, adLockReadOnly, adCmdText
iPageCount = rs.PageCount
If iPageCurrent > iPageCount Then iPageCurrent = iPageCount
If iPageCurrent < 1 Then iPageCurrent = 1
If iPageCount = 0 Then
Response.Write "No records found!"
Else
rs.AbsolutePage = iPageCurrent
%>
<p>
<font size="+1">Page <strong><%= iPageCurrent %></strong>
of <strong><%= iPageCount %></strong></font>
</p>
<%
Response.Write vbCrLf
%>
<table width="100%" border="1" align="center" cellpadding="2" cellspacing="2" BORDERCOLOR="black">
<thead>
<tr bgcolor="black">
<th><div align="center"><font color="#FFFFFF" size="2" >Request ID</font></div></th>
<th><div align="center"><font color="#FFFFFF" size="2" >First Name</font></div></th>
</tr>
</thead>
<tbody>
<%
iRecordsShown = 0
Do While iRecordsShown < iPageSize And Not rs.EOF
%>
<tr>
<td><div align="center"><font size="1" >
<a href="edit.asp&id=<%= rs.Fields("recordID").Value %>">
<%= rs.Fields("recordID").Value %></a></font></div></td>
<td><font size="1" ><%= rs.Fields("first_name").Value %></font></td>
</tr>
<%
iRecordsShown = iRecordsShown + 1
rs.MoveNext
Loop
End If
%>
</tbody>
<tfoot>
</tfoot>
</table><br>
<%
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
If iPageCurrent > 1 Then
%>
<a href="view.asp?page=<%= iPageCurrent - 1 %>&order=<%= Server.URLEncode(strOrderBy) %>">[<<
Prev]</a>
<%
End If
For I = 1 To iPageCount
If I = iPageCurrent Then
%>
<%= I %>
<%
Else
%>
<a href="view.asp?page=<%= I %>&order=<%= Server.URLEncode(strOrderBy) %>"><%= I %></a>
<%
End If
Next 'I
If iPageCurrent < iPageCount Then
%>
<a href="view.asp?page=<%= iPageCurrent + 1 %>&order=<%= Server.URLEncode(strOrderBy) %>">[Next
>>]</a>
<%
End If
its working now, i missed the '?'
thanks a lot!!! 
thanks a lot!!!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.