SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    May 2000
    Location
    Saint John, NB, Canada
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I am using forms to add, delete or modify records in an access database. Add Record and Delete Record functions are working. Update function APPENDS to a record instead of editing it. I have come to my wits end in trying to figure this one out.. please help!

    Code bit:

    Dim oCONj, oRSu, strSQL, strID, editSQL, deleteSQL, rID
    Set oCONj = Server.CreateObject("ADODB.Connection")
    oCONj.Open "jobs"

    strSQL = "SELECT * FROM tblRecruit"

    deleteSQL = "DELETE * FROM tblRecruit WHERE rID= " & CInt(request.form("rID"))

    editSQL = "UPDATE tblRecruit SET "
    editSQL = editSQL & "Facility = '" & request.form("Facility") & "'"
    editSQL = editSQL & ", " & "PosClass = '" & request.form("PosClass") & "'"
    editSQL = editSQL & ", " & "EmpType = '" & request.form("EmpType") & "'"
    editSQL = editSQL & ", " & "DeptUnit = '" & request.form("DeptUnit") & "'"
    editSQL = editSQL & ", " & "PartShifts = '" & request.form("PartShifts") & "'"
    editSQL = editSQL & ", " & "TempLength = '" & request.form("TempLength") & "'"
    editSQL = editSQL & ", " & "Shifts = '" & request.form("Shifts") & "'"
    editSQL = editSQL & ", " & "Bilingual = '" & request.form("Bilingual") & "'"
    editSQL = editSQL & ", " & "ResultOf = '" & request.form("ResultOf") & "'"
    editSQL = editSQL & ", " & "ResultOther = '" & request.form("ResultOther") & "'"
    editSQL = editSQL & ", " & "CurNameTemp = '" & request.form("CurNameTemp") & "'"
    editSQL = editSQL & ", " & "SpecInstruct = '" & request.form("SpecInstruct") & "'"
    editSQL = editSQL & ", " & "Approved = '" & request.form("Approved") & "'"
    editSQL = editSQL & " WHERE rID = " & CInt(request.form("rID"))

    Set oRSu = CreateObject("ADODB.Recordset")
    oRSu.Open strSQL,oCONj,3,3

    If (Request.Form("Edit") <>"") Then
    oCONj.execute editSQL
    Else
    If (Request.Form("Delete") <>"") Then
    oCONj.execute deleteSQL
    Else
    If (Request.Form("Add") = "Add") Then
    oRSu.AddNew
    oRSu("Facility") = Request.Form("lstFacility")
    oRSu("PosClass") = Request.Form("posclass")
    oRSu("DeptUnit") = Request.Form("deptunit")
    oRSu("EmpType") = Request.Form("emptype")
    oRSu("PartShifts") = Request.Form("partshifts")
    oRSu("TempLength") = Request.Form("templength")
    oRSu("Shifts") = Request.Form("shifts")
    oRSu("Bilingual") = Request.Form("bilingual")
    oRSu("ResultOf") = Request.Form("resultof")
    oRSu("ResultOther") = Request.Form("resultother")
    oRSu("CurNameTemp") = Request.Form("curnametemp")
    oRSu("SpecInstruct") = Request.Form("specinstruct")
    oRSu("Authorizor") = Request.Form("authorizor")
    oRSu("SecurID") = Session("SecurID")
    End If
    End If
    End If



    strID = oRSu("rID")
    oRSu.Close
    Set oRSu = Nothing
    oCONj.Close
    Set oCONj = Nothing

    %>


    Thanks in advance,

    Shelley

  2. #2
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The following code is from ASP101.com and shows how to properly update a record using the ADO object. Instructions are in the code comments.

    Code:
    <%' Defining some constants to make my life easier! (Same as Sample 1 & 2)
    ' Begin Constant Definition
    	
    	' DB Configuration constants
    	' Fake const so we can use the MapPath to make it relative.
    	' After this, strictly used as if it were a Const.
    	Dim DB_CONNECTIONSTRING
    
    	' ODBC
    	'DB_CONNECTIONSTRING = "DRIVER={Microsoft Access Driver (*.mdb)};" _
    	'	& "DBQ=" & Server.Mappath("./db_scratch.mdb") & ";"
    
    	' OLE DB
    	DB_CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;" _
    		& "Data Source=" & Server.Mappath("db_dsn.mdb") & ";"
    
    	' We don't use these, but we could if we neeeded to.
    	'Const DB_USERNAME = "username"
    	'Const DB_PASSWORD = "password"
    
    	'Now we override the above settings to use our SQL server.
    	'Delete the following line to use the sample Access DB.
    	DB_CONNECTIONSTRING = Application("SQLConnString")
    
    	' ADODB Constants
    	' You can find these in the adovbs.inc file
    	' Do a search for it and it should turn up somewhere on the server
    	' If you can't find it you can download our copy from here:
    	'     http://www.asp101.com/samples/download/adovbs.inc
    	' It may not be the most recent copy so use it at your own risk.
    %>
    	<!-- #INCLUDE FILE="adovbs.inc" -->
    <%
    ' End Constant Definition
    %>
    
    <%
    Dim I               ' Standard looping var
    Dim iRecordToUpdate ' Id of deleted record
    Dim strSQL          ' String variable for building our query
    
    'We're going to keep this as simple as we can.
    '  1. Create a Recordset object
    '  2. Connect the Recordset to the table
    '  3. Find the record to update
    '  4. Update the record
    '  5. Update the table
    '  6. Close the Recordset
    
    'Step 1:
    	Dim objRecordset
    	Set objRecordset = Server.CreateObject("ADODB.Recordset")
    
    'Step 2:
    	' Get the Id of the record to update
    	iRecordToUpdate = Request.QueryString("id")
    	
    	' If the record ID passed in isn't a number, we set it to
    	' one so we don't cause SQL query errors.  I use 0 since I
    	' know there's no record in the DB with an id of 0
    	If IsNumeric(iRecordToUpdate) Then
    		iRecordToUpdate = CLng(iRecordToUpdate)
    	Else
    		iRecordToUpdate = 0
    	End If
    	
    	' I'm prebuilding our SQL query so it's easier to print
    	' out in case we need to debug later.  I'm using a query
    	' that will return just the record we want to update.
    	strSQL = "SELECT * FROM scratch WHERE id=" & iRecordToUpdate & ";"
    
    	' The syntax for the open command is
    	' recordset.Open Source, ActiveConnection, CursorType, LockType, Options 
    	objRecordset.Open strSQL, DB_CONNECTIONSTRING, adOpenKeyset, adLockPessimistic, adCmdText
    
    'Step 3:		
    	' The recordset should only have the one record so:
    	If Not objRecordset.EOF Then
    		objRecordset.MoveFirst
    
    'Step 4:
    	'Only update if we've got a record, o/w we never run this
    
    		' String / Text Data Type
    		objRecordset.Fields("text_field") = CStr(WeekdayName(WeekDay(Date())))
    
    		' Integer Data Type
    		objRecordset.Fields("integer_field") = CInt(Day(Now()))
    
    		' Date / Time Data Type
    		objRecordset.Fields("date_time_field") = Now()
    
    'Step 5:
    	'Only update if we've got a record, o/w we never run this
    		objRecordset.Update
    	
    		Response.Write "Record id " & iRecordToUpdate & " updated!"
    	End If
    
    'Step 6:
    	' Finally we close the recordset and release the memory used by the
    	' object variable by setting it to Nothing (a VBScript keyword)
    	objRecordset.Close
    	Set objRecordset = Nothing
    Wayne Luke
    ------------



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •