Results 1 to 2 of 2
  1. 01-15-2010, 10:34 AM #1
    KLynch0803
    KLynch0803 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    37

    Post Accessing Table via asp Errors

    Can someone please help me Im having trouble with two pages on site to access my db... Im not really sure if I'm putting this in the right section of this forum as well but maybe someone can help me. I have verified my tbl names, field names are correct and the mappath is correct and that it has read write access. Im not sure what information you realy need to help except the following to start with.

    First page I have problem:

    It returns the error **ERROR: Operation is not allowed when the object is closed.**

    Code:
    <%
    Option Explicit
    On Error Resume Next     Dim sMsg, sErr, sMDB, sConn, vID,vService_Company_Name,vContact_Name,vAddress,vCity,vState,vZip,vPhone,vEmail,vCoverageArea,vServices_Provided,vApproved,vApproval_Date,vPer_Lead_Rate,vCC_Num,vCC_Expiration,vCC_Security_Code,vBiling_Zip_Code    vService_Company_Name = Request.Form("txtService_Company_Name")
    vContact_Name = Request.Form("txtContact_Name")
    vAddress = Request.Form("txtAddress")
    vCity = Request.Form("txtCity")
    vState = Request.Form("txtState")
    vZip = Request.Form("txtZip")
    vPhone = Request.Form("txtPhone")
    vEmail = Request.Form("txtEmail")
    vCoverageArea = Request.Form("txtCoverageArea")
    vServices_Provided = Request.Form("txtServices_Provided")
    vApproved = Request.Form("txtApproved")
    vApproval_Date = Request.Form("txtApproval_Date")
    vPer_Lead_Rate = Request.Form("txtPer_Lead_Rate")
    vCC_Num = Request.Form("txtCC_Num")
    vCC_Expiration = Request.Form("txtCC_Expiration")
    vCC_Security_Code = Request.Form("txtCC_Security_Code")
    vBiling_Zip_Code = Request.Form("txtBiling_Zip_Code")
    vID = Request.QueryString("ID")
    If vID = "" Then vID = 0
    If Request("btnNew") <> "" Then 
        vID = -1
        vService_Company_Name = ""
        vContact_Name = ""
        vAddress = ""
        vCity = ""
        vState = ""
        vZip = ""
        vPhone = ""
        vEmail = ""
        vCoverageArea = ""
        vServices_Provided = ""
        vApproved = ""
        vApproval_Date = ""
        vPer_Lead_Rate = ""
        vCC_Num = ""
        vCC_Expiration = ""
        vCC_Security_Code = ""
        vBiling_Zip_Code = ""
        sMsg = "<font color=darkgreen><b>New Record</b></font>"
    End If    If Request("btnGet") <> "" Then vID=-99    If Request("btnSave") <> "" Then
        If vID = "-1" Then
            vID = InsertNewRecord()
        Else
            Call UpdateRecord(vID)
            sMsg = "<font color=darkred><b>Updated Record #" & vID & "</b></font>"
        End If
    Else
        Call RetrieveRecord(vID)
        If cLng(vID) > 0 Then sMsg = "<font color=darkblue><b>Retrieved Record #" & vID & "</b></font>"
        If cLng(vID) = -99 Then sMsg = "<font color=darkpurple><b>Lookup Max (last) Record</b></font>"
    End If    If sErr = "" Then sErr = Err.Description
    If sErr <> "" Then sMsg = sErr%>
<html>
<FORM method=post action=_Service_Providers.asp?ID=<%=vID%> id=frm_Service_Providers & name=frm_Service_Providers>    <table border="1" width="440" id="_Service_Providers">
        <tr valign="top"><td width="180"> <INPUT type="submit" value="Save" id="btnSave" name="btnSave"> &nbsp; 
            <INPUT type="submit" value="New" id="btnNew" name="btnNew"> &nbsp; 
            <INPUT type="submit" value="Last" id="btnGet" name="btnGet">
        </td><td width="260"> 
            <%=sMsg%> <INPUT type="hidden" id="txtID" name="txtID" value="<%=Trim(vID)%>">
        </td></tr><tr><td colspan=2 align=center>&nbsp;</td></tr>        <tr valign="top"><td width="180">  Service  Company  Name
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtService_Company_Name" name="txtService_Company_Name" value="<%=Trim(vService_Company_Name)%>">
        </td></tr>        <tr valign="top"><td width="180">  Contact  Name
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtContact_Name" name="txtContact_Name" value="<%=Trim(vContact_Name)%>">
        </td></tr>        <tr valign="top"><td width="180">  Address
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtAddress" name="txtAddress" value="<%=Trim(vAddress)%>">
        </td></tr>        <tr valign="top"><td width="180">  City
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtCity" name="txtCity" value="<%=Trim(vCity)%>">
        </td></tr>        <tr valign="top"><td width="180">  State
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtState" name="txtState" value="<%=Trim(vState)%>">
        </td></tr>        <tr valign="top"><td width="180">  Zip
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtZip" name="txtZip" value="<%=Trim(vZip)%>">
        </td></tr>        <tr valign="top"><td width="180">  Phone
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtPhone" name="txtPhone" value="<%=Trim(vPhone)%>">
        </td></tr>        <tr valign="top"><td width="180">  Email
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtEmail" name="txtEmail" value="<%=Trim(vEmail)%>">
        </td></tr>        <tr valign="top"><td width="180">  Coverage Area
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 75px" id="txtCoverageArea" name="txtCoverageArea" value="<%=Trim(vCoverageArea)%>">
        </td></tr>        <tr valign="top"><td width="180">  Services  Provided
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 75px" id="txtServices_Provided" name="txtServices_Provided" value="<%=Trim(vServices_Provided)%>">
        </td></tr>        <tr valign="top"><td width="180">  Approved
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 75px" id="txtApproved" name="txtApproved" value="<%=Trim(vApproved)%>">
        </td></tr>        <tr valign="top"><td width="180">  Approval  Date
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 75px" id="txtApproval_Date" name="txtApproval_Date" value="<%=Trim(vApproval_Date)%>">
        </td></tr>        <tr valign="top"><td width="180">  Per  Lead  Rate
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 75px" id="txtPer_Lead_Rate" name="txtPer_Lead_Rate" value="<%=Trim(vPer_Lead_Rate)%>">
        </td></tr>        <tr valign="top"><td width="180">  C C  Num
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtCC_Num" name="txtCC_Num" value="<%=Trim(vCC_Num)%>">
        </td></tr>        <tr valign="top"><td width="180">  C C  Expiration
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtCC_Expiration" name="txtCC_Expiration" value="<%=Trim(vCC_Expiration)%>">
        </td></tr>        <tr valign="top"><td width="180">  C C  Security  Code
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtCC_Security_Code" name="txtCC_Security_Code" value="<%=Trim(vCC_Security_Code)%>">
        </td></tr>        <tr valign="top"><td width="180">  Biling  Zip  Code
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtBiling_Zip_Code" name="txtBiling_Zip_Code" value="<%=Trim(vBiling_Zip_Code)%>">
        </td></tr>    </table>
</Html><%    Function InsertNewRecord()
        Dim sSQL, vID, sConnect, cnnDBS, rsDataTable        Set cnnDBS = server.CreateObject("adodb.connection")
 
        sMDB = Replace(LCase(Server.MapPath("_Service_Providers.asp")),"_service_providers.asp","/fpdb/ServiceBidProwarehouse.mdb")
        sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & sMDB        cnnDBS.Open sConn         sSQL = "INSERT INTO tbl_Service_Providers (Service_Company_Name,Contact_Name,Address,City,State,Zip,Phone,Email,CoverageArea,Services_Provided,Approved,Approval_Date,Per_Lead_Rate,CC_Num,CC_Expiration,CC_Security_Code,Biling_Zip_Code) "
        sSQL = sSQL & " VALUES('" & Left(Replace(vService_Company_Name,"'","''"),255) & "','" & Left(Replace(vContact_Name,"'","''"),255) & "','" & Left(Replace(vAddress,"'","''"),255) & "','" & Left(Replace(vCity,"'","''"),255) & "','" & Left(Replace(vState,"'","''"),255) & "','" & Left(Replace(vZip,"'","''"),255) & "','" & Left(Replace(vPhone,"'","''"),255) & "','" & Left(Replace(vEmail,"'","''"),255) & "','" & Left(Replace(vCoverageArea,"'","''"),0) & "','" & Left(Replace(vServices_Provided,"'","''"),0) & "'," & vApproved & ",#" & vApproval_Date & "#," & vPer_Lead_Rate & ",'" & Left(Replace(vCC_Num,"'","''"),255) & "','" & Left(Replace(vCC_Expiration,"'","''"),255) & "','" & Left(Replace(vCC_Security_Code,"'","''"),255) & "','" & Left(Replace(vBiling_Zip_Code,"'","''"),255) & "') "        sSQL = Replace(sSQL,"'Null'","Null")
        sSQL = Replace(sSQL,",,",",Null,")
        sSQL = Replace(sSQL,",,",",Null,")
        cnnDBS.Execute (sSQL)        sSQL = "SELECT Max([ID]) As ID FROM tbl_Service_Providers"
        Set rsDataTable = server.CreateObject("adodb.recordset") 
        Set rsDataTable = cnnDBS.Execute(sSQL)
        InsertNewRecord = rsDataTable("ID")        If rsDataTable.BOF And rsDataTable.EOF Then
            InsertNewRecord = 0
        Else
            InsertNewRecord = rsDataTable("ID")
        End If        Set rsDataTable = Nothing
        Set cnnDBS = Nothing    End Function    Function UpdateRecord(vID)
        Dim sSQL, sConnect, cnnDBS        Set cnnDBS = server.CreateObject("adodb.connection")
 
        sMDB = Replace(LCase(Server.MapPath("_Service_Providers.asp")),"_service_providers.asp","/fpdb/ServiceBidProwarehouse.mdb")
        sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & sMDB        cnnDBS.Open sConn         sSQL = "UPDATE tbl_Service_Providers SET "
        sSQL = sSQL & "Service_Company_Name='" & Left(Replace(vService_Company_Name,"'","''"),255) & "',"
        sSQL = sSQL & "Contact_Name='" & Left(Replace(vContact_Name,"'","''"),255) & "',"
        sSQL = sSQL & "Address='" & Left(Replace(vAddress,"'","''"),255) & "',"
        sSQL = sSQL & "City='" & Left(Replace(vCity,"'","''"),255) & "',"
        sSQL = sSQL & "State='" & Left(Replace(vState,"'","''"),255) & "',"
        sSQL = sSQL & "Zip='" & Left(Replace(vZip,"'","''"),255) & "',"
        sSQL = sSQL & "Phone='" & Left(Replace(vPhone,"'","''"),255) & "',"
        sSQL = sSQL & "Email='" & Left(Replace(vEmail,"'","''"),255) & "',"
        sSQL = sSQL & "CoverageArea='" & Left(Replace(vCoverageArea,"'","''"),0) & "',"
        sSQL = sSQL & "Services_Provided='" & Left(Replace(vServices_Provided,"'","''"),0) & "',"
        sSQL = sSQL & "Approved=" & vApproved & ","
        sSQL = sSQL & "Approval_Date=#" & vApproval_Date & "#,"
        sSQL = sSQL & "Per_Lead_Rate=" & vPer_Lead_Rate & ","
        sSQL = sSQL & "CC_Num='" & Left(Replace(vCC_Num,"'","''"),255) & "',"
        sSQL = sSQL & "CC_Expiration='" & Left(Replace(vCC_Expiration,"'","''"),255) & "',"
        sSQL = sSQL & "CC_Security_Code='" & Left(Replace(vCC_Security_Code,"'","''"),255) & "',"
        sSQL = sSQL & "Biling_Zip_Code='" & Left(Replace(vBiling_Zip_Code,"'","''"),255) & "'"
        sSQL = sSQL & " WHERE ID = " & vID
        sSQL = Replace(sSQL,"=''","=Null")
        sSQL = Replace(sSQL,"=,","=Null,")
        cnnDBS.Execute (sSQL)        Set cnnDBS = Nothing    End Function
    Function RetrieveRecord(vID)
        Dim sSQL, sConnect, cnnDBS, rsDataTable, sFilter
        On Error Resume Next         Set cnnDBS = server.CreateObject("adodb.connection")
 
        sMDB = Replace(LCase(Server.MapPath("_Service_Providers.asp")),"_service_providers.asp","/fpdb/ServiceBidProwarehouse.mdb")
        sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & sMDB        cnnDBS.Open sConn         If Err.number <> 0 Then sErr = "<font color=red><b>ERROR:  Assign WRITE permissions to Web Folder containing mdb file<br>" & Err.Description  & "</b></font>"        Set rsDataTable = server.CreateObject("adodb.recordset")
        If vID=-99 Then sFilter = "(DMax('ID','tbl_Service_Providers'))" Else sFilter = vID
        sSQL = "SELECT * FROM tbl_Service_Providers WHERE ID = " & sFilter
        Set rsDataTable = cnnDBS.Execute(sSQL)        If Err.number <> 0 Then sErr = "<font color=red><b>ERROR: " & Err.Description  & "</b></font>"        If Not rsDataTable.BOF And Not rsDataTable.EOF Then
            vID=rsDataTable("ID")
            vService_Company_Name=rsDataTable("Service_Company_Name")
            vContact_Name=rsDataTable("Contact_Name")
            vAddress=rsDataTable("Address")
            vCity=rsDataTable("City")
            vState=rsDataTable("State")
            vZip=rsDataTable("Zip")
            vPhone=rsDataTable("Phone")
            vEmail=rsDataTable("Email")
            vCoverageArea=rsDataTable("CoverageArea")
            vServices_Provided=rsDataTable("Services_Provided")
            vApproved=rsDataTable("Approved")
            vApproval_Date=rsDataTable("Approval_Date")
            vPer_Lead_Rate=rsDataTable("Per_Lead_Rate")
            vCC_Num=rsDataTable("CC_Num")
            vCC_Expiration=rsDataTable("CC_Expiration")
            vCC_Security_Code=rsDataTable("CC_Security_Code")
            vBiling_Zip_Code=rsDataTable("Biling_Zip_Code")        End If
        Set rsDataTable = Nothing
        Set cnnDBS = Nothing    End Function%></FORM>
    Second Im having trouble with is Error 500
    Code:
    <%
    Option Explicit
    On Error Resume Next     Dim sMsg, sErr, sMDB, sConn, vID, vID,vFirstName,vLastName,vAddress,vCity,vState,vZipCode,vPhone,vEmail,vServicesType,vLeadTime,vAdditionalNotes,vServiceMatchedTo,vMatchedDate    vFirstName = Request.Form("txtFirstName")
    vLastName = Request.Form("txtLastName")
    vAddress = Request.Form("txtAddress")
    vCity = Request.Form("txtCity")
    vState = Request.Form("txtState")
    vZipCode = Request.Form("txtZipCode")
    vPhone = Request.Form("txtPhone")
    vEmail = Request.Form("txtEmail")
    vServicesType = Request.Form("txtServicesType")
    vLeadTime = Request.Form("txtLeadTime")
    vAdditionalNotes = Request.Form("txtAdditionalNotes")
    vServiceMatchedTo = Request.Form("txtServiceMatchedTo")
    vMatchedDate = Request.Form("txtMatchedDate")
    vID = Request.QueryString("ID")
    If vID = "" Then vID = 0
    If Request("btnNew") <> "" Then 
        vID = -1
        vFirstName = ""
        vLastName = ""
        vAddress = ""
        vCity = ""
        vState = ""
        vZipCode = ""
        vPhone = ""
        vEmail = ""
        vServicesType = ""
        vLeadTime = ""
        vAdditionalNotes = ""
        vServiceMatchedTo = ""
        vMatchedDate = ""
        sMsg = "<font color=darkgreen><b>New Record</b></font>"
    End If    If Request("btnGet") <> "" Then vID=-99    If Request("btnSave") <> "" Then
        If vID = "-1" Then
            vID = InsertNewRecord()
        Else
            Call UpdateRecord(vID)
            sMsg = "<font color=darkred><b>Updated Record #" & vID & "</b></font>"
        End If
    Else
        Call RetrieveRecord(vID)
        If cLng(vID) > 0 Then sMsg = "<font color=darkblue><b>Retrieved Record #" & vID & "</b></font>"
        If cLng(vID) = -99 Then sMsg = "<font color=darkpurple><b>Lookup Max (last) Record</b></font>"
    End If    If sErr = "" Then sErr = Err.Description
    If sErr <> "" Then sMsg = sErr%>
<FORM method=post action=customer_Records.asp?ID=<%=vID%> id=frm_Customers & name=frm_Customers>    <table border="1" width="440" id="_Customers">
        <tr valign="top"><td width="180"> <INPUT type="submit" value="Save" id="btnSave" name="btnSave"> &nbsp; 
            <INPUT type="submit" value="New" id="btnNew" name="btnNew"> &nbsp; 
            <INPUT type="submit" value="Last" id="btnGet" name="btnGet">
        </td><td width="260"> 
            <%=sMsg%> <INPUT type="hidden" id="txtID" name="txtID" value="<%=Trim(vID)%>">
        </td></tr><tr><td colspan=2 align=center>&nbsp;</td></tr>        <tr valign="top"><td width="180">  First Name
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtFirstName" name="txtFirstName" value="<%=Trim(vFirstName)%>">
        </td></tr>        <tr valign="top"><td width="180">  Last Name
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtLastName" name="txtLastName" value="<%=Trim(vLastName)%>">
        </td></tr>        <tr valign="top"><td width="180">  Address
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtAddress" name="txtAddress" value="<%=Trim(vAddress)%>">
        </td></tr>        <tr valign="top"><td width="180">  City
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtCity" name="txtCity" value="<%=Trim(vCity)%>">
        </td></tr>        <tr valign="top"><td width="180">  State
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 75px" id="txtState" name="txtState" value="<%=Trim(vState)%>">
        </td></tr>        <tr valign="top"><td width="180">  Zip Code
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 75px" id="txtZipCode" name="txtZipCode" value="<%=Trim(vZipCode)%>">
        </td></tr>        <tr valign="top"><td width="180">  Phone
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtPhone" name="txtPhone" value="<%=Trim(vPhone)%>">
        </td></tr>        <tr valign="top"><td width="180">  Email
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 260px" id="txtEmail" name="txtEmail" value="<%=Trim(vEmail)%>">
        </td></tr>        <tr valign="top"><td width="180">  Services Type
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 75px" id="txtServicesType" name="txtServicesType" value="<%=Trim(vServicesType)%>">
        </td></tr>        <tr valign="top"><td width="180">  Lead Time
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 75px" id="txtLeadTime" name="txtLeadTime" value="<%=Trim(vLeadTime)%>">
        </td></tr>        <tr valign="top"><td width="180">  Additional Notes
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 75px" id="txtAdditionalNotes" name="txtAdditionalNotes" value="<%=Trim(vAdditionalNotes)%>">
        </td></tr>        <tr valign="top"><td width="180">  Service Matched To
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 75px" id="txtServiceMatchedTo" name="txtServiceMatchedTo" value="<%=Trim(vServiceMatchedTo)%>">
        </td></tr>        <tr valign="top"><td width="180">  Matched Date
        </td><td width="260"> 
            <INPUT style="FONT-SIZE: xx-small; WIDTH: 75px" id="txtMatchedDate" name="txtMatchedDate" value="<%=Trim(vMatchedDate)%>">
        </td></tr>    </table><%     Function InsertNewRecord()
        Dim sSQL, vID, sConnect, cnnDBS, rsDataTable        Set cnnDBS = server.CreateObject("adodb.connection")
 
        sMDB = Replace(LCase(Server.MapPath("customer_Records.asp")),"customer_Records.asp","/fpdb/ServiceBidProwarehouse.mdb")
        sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & sMDB        cnnDBS.Open sConn         sSQL = "INSERT INTO tbl_Customers (FirstName,LastName,Address,City,State,ZipCode,Phone,Email,ServicesType,LeadTime,AdditionalNotes,ServiceMatchedTo,MatchedDate) "
        sSQL = sSQL & " VALUES('" & Left(Replace(vFirstName,"'","''"),255) & "','" & Left(Replace(vLastName,"'","''"),255) & "','" & Left(Replace(vAddress,"'","''"),255) & "','" & Left(Replace(vCity,"'","''"),255) & "'," & vState & "," & vZipCode & ",'" & Left(Replace(vPhone,"'","''"),255) & "','" & Left(Replace(vEmail,"'","''"),255) & "'," & vServicesType & "," & vLeadTime & ",'" & Left(Replace(vAdditionalNotes,"'","''"),0) & "','" & Left(Replace(vServiceMatchedTo,"'","''"),0) & "',#" & vMatchedDate & "#) "        sSQL = Replace(sSQL,"'Null'","Null")
        sSQL = Replace(sSQL,",,",",Null,")
        sSQL = Replace(sSQL,",,",",Null,")
        cnnDBS.Execute (sSQL)        sSQL = "SELECT Max([ID]) As ID FROM tbl_Customers"
        Set rsDataTable = server.CreateObject("adodb.recordset") 
        Set rsDataTable = cnnDBS.Execute(sSQL)
        InsertNewRecord = rsDataTable("ID")        If rsDataTable.BOF And rsDataTable.EOF Then
            InsertNewRecord = 0
        Else
            InsertNewRecord = rsDataTable("ID")
        End If        Set rsDataTable = Nothing
        Set cnnDBS = Nothing    End Function    Function UpdateRecord(vID)
        Dim sSQL, sConnect, cnnDBS        Set cnnDBS = server.CreateObject("adodb.connection")
 
        sMDB = Replace(LCase(Server.MapPath("customer_Records.asp")),"customer_Records.asp","/fpdb/ServiceBidProwarehouse.mdb")
        sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & sMDB        cnnDBS.Open sConn         sSQL = "UPDATE tbl_Customers SET "
        sSQL = sSQL & "FirstName='" & Left(Replace(vFirstName,"'","''"),255) & "',"
        sSQL = sSQL & "LastName='" & Left(Replace(vLastName,"'","''"),255) & "',"
        sSQL = sSQL & "Address='" & Left(Replace(vAddress,"'","''"),255) & "',"
        sSQL = sSQL & "City='" & Left(Replace(vCity,"'","''"),255) & "',"
        sSQL = sSQL & "State=" & vState & ","
        sSQL = sSQL & "ZipCode=" & vZipCode & ","
        sSQL = sSQL & "Phone='" & Left(Replace(vPhone,"'","''"),255) & "',"
        sSQL = sSQL & "Email='" & Left(Replace(vEmail,"'","''"),255) & "',"
        sSQL = sSQL & "ServicesType=" & vServicesType & ","
        sSQL = sSQL & "LeadTime=" & vLeadTime & ","
        sSQL = sSQL & "AdditionalNotes='" & Left(Replace(vAdditionalNotes,"'","''"),0) & "',"
        sSQL = sSQL & "ServiceMatchedTo='" & Left(Replace(vServiceMatchedTo,"'","''"),0) & "',"
        sSQL = sSQL & "MatchedDate=#" & vMatchedDate & "#"
        sSQL = sSQL & " WHERE ID = " & vID
        sSQL = Replace(sSQL,"=''","=Null")
        sSQL = Replace(sSQL,"=,","=Null,")
        cnnDBS.Execute (sSQL)        Set cnnDBS = Nothing    End Function
    Function RetrieveRecord(vID)
        Dim sSQL, sConnect, cnnDBS, rsDataTable, sFilter
        On Error Resume Next         Set cnnDBS = server.CreateObject("adodb.connection")
 
        sMDB = Replace(LCase(Server.MapPath("customer_Records.asp")),"customer_Records.asp","fpdb/ServiceBidProwarehouse.mdb")
        sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & sMDB        cnnDBS.Open sConn         If Err.number <> 0 Then sErr = "<font color=red><b>ERROR:  Assign WRITE permissions to Web Folder containing mdb file<br>" & Err.Description  & "</b></font>"        Set rsDataTable = server.CreateObject("adodb.recordset")
        If vID=-99 Then sFilter = "(DMax('ID','tbl_Customers'))" Else sFilter = vID
        sSQL = "SELECT * FROM tbl_Customers WHERE ID = " & sFilter
        Set rsDataTable = cnnDBS.Execute(sSQL)        If Err.number <> 0 Then sErr = "<font color=red><b>ERROR: " & Err.Description  & "</b></font>"        If Not rsDataTable.BOF And Not rsDataTable.EOF Then
            vID=rsDataTable("ID")
            vFirstName=rsDataTable("FirstName")
            vLastName=rsDataTable("LastName")
            vAddress=rsDataTable("Address")
            vCity=rsDataTable("City")
            vState=rsDataTable("State")
            vZipCode=rsDataTable("ZipCode")
            vPhone=rsDataTable("Phone")
            vEmail=rsDataTable("Email")
            vServicesType=rsDataTable("ServicesType")
            vLeadTime=rsDataTable("LeadTime")
            vAdditionalNotes=rsDataTable("AdditionalNotes")
            vServiceMatchedTo=rsDataTable("ServiceMatchedTo")
            vMatchedDate=rsDataTable("MatchedDate")        End If
        Set rsDataTable = Nothing
        Set cnnDBS = Nothing    End Function%></FORM>

    Last edited by KLynch0803; 01-15-2010 at 10:47 AM. Reason: Fixed code had duplicate on accident. Still have issue
    Reply With Quote Reply With Quote
  2. 01-17-2010, 09:59 AM #2
    KLynch0803
    KLynch0803 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    37
    I was missing some expressions that were needed...
    Reply With Quote Reply With Quote
Please reply to this thread with any new information or opinions.
« Previous Thread | Next Thread »

Similar Threads

  1. Write Conflict Errors with SQL-Access DB
    By trb5016 in forum Access
    Replies: 5
    Last Post: 01-14-2010, 02:10 PM
  2. .ocx Files Errors
    By darshita in forum Import/Export Data
    Replies: 9
    Last Post: 12-07-2009, 07:36 AM
  3. Bugs, errors in 2007
    By Canadiangal in forum Access
    Replies: 3
    Last Post: 09-17-2009, 11:25 AM
  4. dialog box errors in report
    By Jane in forum Programming
    Replies: 0
    Last Post: 11-20-2008, 01:06 PM
  5. Errors when they generate their reports at the same
    By admaldo in forum Security
    Replies: 1
    Last Post: 03-11-2006, 07:38 AM

Posting Permissions

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

Forum Rules

Other Forums: Microsoft Office Forums