jlcannon
asked on
Add records to database only if an entry has not been made for that day
I have an ASP page where I am using vbscript to add records to an access database and that is working well, however I only want to add the records if there has been no entry for that day. The access table has a field called EntryDate and its default value if the short date so I was wanting to use this field to determine if anything had been written to the database on the current day based on this. Below is the code that adds the record.
was thinking if using something like IF NOT EXISTS(Select * FROM tblData WHERE EntryDate < Date();)
<%
sql = "Select * FROM tblData;"
'set OpARS = server.createobject("adodb.recordset")
OpARS.open sql,dbAConn,adopenkeyset,adlockoptimistic
'Server Side form validation to keep our database clean
dim Site, Availability, Reliability, Efficiency, NminusXST, ProdctLoss, MoneyLoss, Comments
User = vFullName
'If we pass through validation then store the information in the db
OpARs.AddNew
OpARs("User") = User
OpARs("Site") = Site
OpARs("Availability") = Availability
OpARs("Reliability") = Reliability
OpARs("Efficiency") = EffSite
OpARs("NminusXST") = NminusXST
OpARs("ProductLoss") = ProductLoss
OpARs("MoneyLoss") = MoneyLoss
OpARs.update
RecID = OpARs("ID")
%>was thinking if using something like IF NOT EXISTS(Select * FROM tblData WHERE EntryDate < Date();)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This took me down the path! Thank you!
sql = "Select count (Site) FROM tblData WHERE [EntryDate] = Date();"
Response.write Getdate
Response.write sql
set OpARS = dbAConn.Execute( sql )
dim recordCount : recordCount = CInt( OpARS( 0 ) )
response.write recordcount & " This is the record count"
response.write OpARs.state
if OpARS.State <> 0 then
OpARS.Close
if recordCount = 0 then '--if no records found, do the insert
'*************************
sql = "Select count (Site) FROM tblData WHERE [EntryDate] = Date();"
Response.write Getdate
Response.write sql
set OpARS = dbAConn.Execute( sql )
dim recordCount : recordCount = CInt( OpARS( 0 ) )
response.write recordcount & " This is the record count"
response.write OpARs.state
if OpARS.State <> 0 then
OpARS.Close
if recordCount = 0 then '--if no records found, do the insert
'*************************
ASKER