acaraciolo
asked on
Check record exist before using AddNew
Hello all: I have a form that inputs data to a database to be used to create a work schedule. I am entering:
EmpCode 'the employee code
sTime 'start time
eTime 'end time
sDate 'start date
eDate 'end date
lead 'yes if lead no if not
EmpName 'full name
city 'location city of technician
I do the following to add this data to the database:
strDataPath = Server.MapPath("/WklySched ule.mdb")
strConnectString = "Provider=Microsoft.Jet.OL EDB.4.0;"_
& " Data Source= " & strDataPath & ";"_
& " Mode=Share Deny None;User Id=admin;PASSWORD=;"
set Conn=Server.CreateObject(" ADODB.Conn ection")
Conn.ConnectionTimeout = 15
Conn.CommandTimeout = 10
Conn.Mode = 3 'adModeReadWrite
if Conn.state = 0 then
Conn.Open strConnectString
end if
set rs=Server.CreateObject("AD ODB.record set")
sql = "SELECT * FROM tblSchedule"
rs.Open sql, Conn,3,3
rs.AddNew
rs.Fields("EmpCode") = EmpCode
rs.Fields("sTime") = sTime
rs.Fields("eTime") = eTime
rs.Fields("sDate") = sDate
rs.Fields("eDate") = eDate
rs.Fields("lead") = lead
rs.Fields("EmpName") = EmpName
rs.Fields("city") = city
rs.Fields("ipa") = ipa
rs.Fields("date") = dos
rs.fields("time") = tos
rs.Update
rs.close
Conn.close
response.clear
response.redirect(BackToFo rm)
This works but I want the data to overwrite existing data where the EmpCode and sDate already exist. For instance if a manager schedules an employee to work 0000 - 0800 on 11/11/2006 but later decides to change the times to 0930 - 1800 on 11/11/2006... I want the program to simply overwrite the orignal data. Is there a simple way to see if the EmpCode and sDate already exist and update rather then add?
EmpCode 'the employee code
sTime 'start time
eTime 'end time
sDate 'start date
eDate 'end date
lead 'yes if lead no if not
EmpName 'full name
city 'location city of technician
I do the following to add this data to the database:
strDataPath = Server.MapPath("/WklySched
strConnectString = "Provider=Microsoft.Jet.OL
& " Data Source= " & strDataPath & ";"_
& " Mode=Share Deny None;User Id=admin;PASSWORD=;"
set Conn=Server.CreateObject("
Conn.ConnectionTimeout = 15
Conn.CommandTimeout = 10
Conn.Mode = 3 'adModeReadWrite
if Conn.state = 0 then
Conn.Open strConnectString
end if
set rs=Server.CreateObject("AD
sql = "SELECT * FROM tblSchedule"
rs.Open sql, Conn,3,3
rs.AddNew
rs.Fields("EmpCode") = EmpCode
rs.Fields("sTime") = sTime
rs.Fields("eTime") = eTime
rs.Fields("sDate") = sDate
rs.Fields("eDate") = eDate
rs.Fields("lead") = lead
rs.Fields("EmpName") = EmpName
rs.Fields("city") = city
rs.Fields("ipa") = ipa
rs.Fields("date") = dos
rs.fields("time") = tos
rs.Update
rs.close
Conn.close
response.clear
response.redirect(BackToFo
This works but I want the data to overwrite existing data where the EmpCode and sDate already exist. For instance if a manager schedules an employee to work 0000 - 0800 on 11/11/2006 but later decides to change the times to 0930 - 1800 on 11/11/2006... I want the program to simply overwrite the orignal data. Is there a simple way to see if the EmpCode and sDate already exist and update rather then add?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
acaraciolo,
Thedwalker's response shows a key and may be all you need. However to actually do what you wanted you will want to include an Else in that If to update the record. The test will be true if no record is found. If it is found then you can use Update to update the record with the new values. Make sure that you have opened the database to allow updating.
If rs.EOF then
' steps to add the record
Else
' update the fields with something like this rs.fieldname = variableWithNewValue
rs.update
End if
Let me know if you have any questions or need more information.
b0lsc0tt
Thedwalker's response shows a key and may be all you need. However to actually do what you wanted you will want to include an Else in that If to update the record. The test will be true if no record is found. If it is found then you can use Update to update the record with the new values. Make sure that you have opened the database to allow updating.
If rs.EOF then
' steps to add the record
Else
' update the fields with something like this rs.fieldname = variableWithNewValue
rs.update
End if
Let me know if you have any questions or need more information.
b0lsc0tt
Maybe I should have written the whole thing out the snippet of code that I wrote was to replace the plain AddNew line. It doesn't need an Else clause because when there is a record then the existing code will just update that. At least that is how I have done it before and it worked fine.
@Thedwalker - I see what you mean and I didn't think of that. Thanks for the correction.
bol
bol
So your code would look like this:
strDataPath = Server.MapPath("/WklySched ule.mdb")
strConnectString = "Provider=Microsoft.Jet.OL EDB.4.0;"_
& " Data Source= " & strDataPath & ";"_
& " Mode=Share Deny None;User Id=admin;PASSWORD=;"
set Conn=Server.CreateObject(" ADODB.Conn ection")
Conn.ConnectionTimeout = 15
Conn.CommandTimeout = 10
Conn.Mode = 3 'adModeReadWrite
if Conn.state = 0 then
Conn.Open strConnectString
end if
set rs=Server.CreateObject("AD ODB.record set")
sql = "SELECT * FROM WHERE EmpCode = '" & EmpCode & "' AND sDate= '" & sDate"'"
rs.Open sql, Conn,3,3
if rs.BOF and rs.EOF then
rs.AddNew
end if
rs.Fields("EmpCode") = EmpCode
rs.Fields("sTime") = sTime
rs.Fields("eTime") = eTime
rs.Fields("sDate") = sDate
rs.Fields("eDate") = eDate
rs.Fields("lead") = lead
rs.Fields("EmpName") = EmpName
rs.Fields("city") = city
rs.Fields("ipa") = ipa
rs.Fields("date") = dos
rs.fields("time") = tos
rs.Update
rs.close
Conn.close
response.clear
response.redirect(BackToFo rm)
strDataPath = Server.MapPath("/WklySched
strConnectString = "Provider=Microsoft.Jet.OL
& " Data Source= " & strDataPath & ";"_
& " Mode=Share Deny None;User Id=admin;PASSWORD=;"
set Conn=Server.CreateObject("
Conn.ConnectionTimeout = 15
Conn.CommandTimeout = 10
Conn.Mode = 3 'adModeReadWrite
if Conn.state = 0 then
Conn.Open strConnectString
end if
set rs=Server.CreateObject("AD
sql = "SELECT * FROM WHERE EmpCode = '" & EmpCode & "' AND sDate= '" & sDate"'"
rs.Open sql, Conn,3,3
if rs.BOF and rs.EOF then
rs.AddNew
end if
rs.Fields("EmpCode") = EmpCode
rs.Fields("sTime") = sTime
rs.Fields("eTime") = eTime
rs.Fields("sDate") = sDate
rs.Fields("eDate") = eDate
rs.Fields("lead") = lead
rs.Fields("EmpName") = EmpName
rs.Fields("city") = city
rs.Fields("ipa") = ipa
rs.Fields("date") = dos
rs.fields("time") = tos
rs.Update
rs.close
Conn.close
response.clear
response.redirect(BackToFo
ASKER
Perfect thedwalker Thanks
sql = "SELECT * FROM tblSchedule where EmpCode= " & EmpCode & " and stime = #" & sTime &"# and eTime=#" & etime & "#"
rs.Open sql, Conn,3,3
If rs.eof ' new recored
rs.addNew
end if