Solved

Check record exist before using AddNew

Posted on 2006-11-08
7
314 Views
Last Modified: 2012-08-13
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("/WklySchedule.mdb")
            strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
          & " Data Source= " & strDataPath & ";"_
          & " Mode=Share Deny None;User Id=admin;PASSWORD=;"


            set Conn=Server.CreateObject("ADODB.Connection")
            Conn.ConnectionTimeout = 15
            Conn.CommandTimeout =  10
            Conn.Mode = 3 'adModeReadWrite
            
            if Conn.state = 0 then
                Conn.Open strConnectString
            end if
              
              set rs=Server.CreateObject("ADODB.recordset")
              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(BackToForm)

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?
0
Comment
Question by:acaraciolo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 1

Accepted Solution

by:
thedwalker earned 500 total points
ID: 17901675
try this:
sql="SELECT * FROM WHERE EmpCode = " & EmpCode & " AND  sDate= " & sDate"

If rs.BOF And rs.EOF Then
     rs.AddNew
End If
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 17901860
This should work for you

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

0
 
LVL 54

Expert Comment

by:b0lsc0tt
ID: 17901885
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:thedwalker
ID: 17902080
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.
0
 
LVL 54

Expert Comment

by:b0lsc0tt
ID: 17902115
@Thedwalker - I see what you mean and I didn't think of that.  Thanks for the correction.

bol
0
 
LVL 1

Expert Comment

by:thedwalker
ID: 17902116
So your code would look like this:

 strDataPath = Server.MapPath("/WklySchedule.mdb")
          strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
          & " Data Source= " & strDataPath & ";"_
          & " Mode=Share Deny None;User Id=admin;PASSWORD=;"


          set Conn=Server.CreateObject("ADODB.Connection")
          Conn.ConnectionTimeout = 15
          Conn.CommandTimeout =  10
          Conn.Mode = 3 'adModeReadWrite
         
          if Conn.state = 0 then
              Conn.Open strConnectString
          end if
           
            set rs=Server.CreateObject("ADODB.recordset")
            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(BackToForm)
0
 

Author Comment

by:acaraciolo
ID: 17902253
Perfect thedwalker Thanks
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question