Update Record Issue - Please Help

[hotflash]

Hi CroCrew et All,

I am working on a project that will allow user to search for their opened projects (means the CompleteDate is NULL) and be able to update their WorkDescription . Once the record (ProjectID) is created, user can search for their opened projects using either their Username or ProjectID ok without problem because the CompleteDate is NULL at this time. One thing here is when you use your Username you can only search for opened projects and when you use the ProjectID you can search either closed project (means the CompleteDate is filled ex. 2/1/2008) and opened projects (means the field is NULL).

The issue that I have here as soon as the User updates their WorkDescription (just the WorkDescription field and WITHOUT put in the CompleteDate because the project is not completed yet), it will allow the User update OK however, when he/she goes back and searches by their Username again, the project that they just UPDATED does not show up anymore even though there is nothing in the CompleteDate.

The thing here is when you remove the CompleteDate from the UpdateProject.a sp file, there will be no problem. It looks to me like when you click on the Update button, something still sent to the CompleteDate field knowing that the User hasn't put in the completedate yet that makes my search script thing that the Project is NOT NULL anymore that's why they can't search anymore. Thanks for your help.


Here is the CompleteDate field defined in the MS Access database:

FieldName: Text
Field Size: 50
Required: No
Allow Zero Length: Yes
Indexed: No

Here is the UpdateProject.a sp file:

<%
Dim strGDNIEngineer
Dim strCompleteDate
Dim strWorkDescript ion

Dim adocon
Dim strSQL

ProjectID = Request.Form("P rojectID")
strGDNIEngineer = trim(Request.Fo rm("GDNIEnginee r"))
strCompleteDate = trim(Request.Fo rm("CompleteDat e"))
strWorkDescript ion = trim(Request.Fo rm("WorkDescrip tion"))

If ProjectID <> "" Then
Set adocon = Server.CreateOb ject("ADODB.Con nection")

adocon.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & Server.MapPath( "PTSystem.m db")

strSQL = "UPDATE PTSProjects SET CompleteDate = '"& strCompleteDate &"', WorkDescription = '"& strWorkDescript ion &"'"
strSQL = strSQL & " WHERE ProjectID = " & ProjectID
adocon.Execute( strSQL)

adocon.Close
Set adocon = Nothing
%>

When you click the Search button, it will then call the DisplayProject. asp file to display the search. Here is the DisplayProject. asp file SEARCH ONLY PORTION.

<%
Dim strGDNIEngineer
Dim strCompleteDate
Dim Conn
Dim strSQL
Dim RS

strGDNIEngineer = trim(Request.Fo rm("GDNIEnginee r"))
strCompleteDate = trim(Request.Fo rm("CompleteDat e"))
If len(strGDNIEngi neer)=0 then
strGDNIEngineer =0
End if

Set Conn = Server.CreateOb ject("ADODB.Con nection")
Set RS = Server.CreateOb ject("ADODB.Rec ordset")

Conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & Server.MapPath( "PTSystem.m db")

If isNumeric(strGD NIEngineer) then
strSQL = "Select * FROM PTSProjects WHERE ProjectID=" & strGDNIEngineer
Else
if strGDNIEngineer ="ALL OPENED PROJECTS" then
strSQL = "Select * FROM PTSProjects WHERE CompleteDate IS NULL"' ORDER BY ProjectID'
else
strSQL = "Select * FROM PTSProjects WHERE GDNIEngineer='" & strGDNIEngineer & "' AND CompleteDate IS NULL"' ORDER BY ProjectID'
end if
End If

RS.Open strSQL, Conn

If RS.EOF Then
Response.Redire ct("SearchProje ct.asp?error=So rry ... Your Project ID does not exist in our database. Please try again.")
End If
%>

[DrBunchman]

Hi hotflash,

In the scenario you described the user wants to update the WorkDescription and leave the CompleteDate null. I think the problem might be that when the user doesn't enter a CompleteDate you are passing "" into your query as the variable strCompleteDate . "" is not the same as NULL so when you update your record the database changes from NULL to "" and your second query doesn't pick the record up

A possible solution would be to add an If..Then before updating the CompleteDate to check if anything has been entered for it.

strSQL = "UPDATE PTSProjects SET " 
 
If strCompleteDate <> "" Then
     strSQL = strSQL & " CompleteDate = '" & strCompleteDate & "', "
End If
 
strSQL = strSQL & " WorkDescription = '"& strWorkDescription &"' "
strSQL = strSQL & " WHERE ProjectID = " & ProjectID
adocon.Execute(strSQL)
 

I hope that this helps to sort your problem out. Let me know how it goes.

Dr B

[hotflash]

Hi DrB,

Thanks for your outstanding recommendation. THING WORKS AS DESIGNED.

However, I ran into a minor problem. The issue is, if you put in the CompleteDate let's say, 2/1/2008, it will UPDATE OK and you can no longer search for the project because it is consider CLOSED. Which is OK.

If I go back and REOPEN it by removing the CompleteDate, and click UPDATE, it does not REMOVE the CompleteDate. I thought if you remove the CompleteDate, the project will consider OPENED because the CompleteDate becomes NULL again. Please help. Thanks.

[CroCrew]

Hello hotflash,

The two values (NULL and “”) are not the same. Many people make this mistake it thinking they are. Logically speaking it seems that it would serve you better if you had another field within your database that would answer your query in if the “project” was open or closed. A boolean field (true or false) that you could switch on or off really would be more efficient too.

Hope that helps~

[hotflash]

Hi CroCrew,

Your new method sounds good but it is over my head since I am new to this stuff unless you can give me example to follow.

If you look at my reply to DrB, I got it work that way too but the only show stopper now is let's say if I fill out a completedate let's say 2/2/2008, of course, I won't be able to search for opened project anymore because this is consider closed. However, if I REMOVE the completedate to make it blank, for some reason the date still stay in the CompleteDate field. I thought when you do that, the fill will become BLANK again.

Please advise and thanks once again for your help.

[CroCrew]

Hello hotflash,

Can you show us your code that you are using to removed the date. That might help.

What I was saying was to create another field in your database that stores a value of true or false. Call that field something like ‘Completed’ and just update that field to determine if the record is completed or not rather then using a date field.

Have fun~

[hotflash]

Hi CroCrew,

I just go into MS Access and remove the CompleteDate manually. Because if there is a need to reopen a record, instead of opening a new one, I just want to get into the database, remove the CompleteDate to make the record becomes OPENED again. When you have a chance to look at the Search using Case method instead of Nested IF, can you include the search for GDNIEngineer and OPENED records (CompleteDate is blank)? Thanks for your outstanding support.

[hotflash]

Hi Master CroCrew,

Sorry for the confusion. I have too much going on that's why don't remember the issue. If I manually remove the field in the database then it will search fine for the OPENED RECORDS, what actually does not work is when I search for a closed record, REMOVE the CompleteDate field, the script does not allow me to update the CompleteDate field to make it becomes "BLANK". Thanks for your help.

[CroCrew]

Hello hotflash,

Take a look at the full running example I sent you. I think you will get a better understanding after looking at it.

After extracting the files make sure that you check your permissions on appropriate .mdb file and its directory, set them to read-write for the ISUR user that your site is using.

This example uses a two page process Example.asp and Post_Update.asp Make sure you take a look at the attached database to see how the tables are set up. Password to the database is “happy” (without the quotes).

Hope this helps~

For others that might want to see what I had sent hotflash here is the code:

Database:

-Field Name-, -Data Type-
KeyId, AutoNumber
Completed, Yes/No
FieldOne, Text
FieldTwo, Text
FieldThree, Text
CompletedDate, Text
 

Example.asp

<%
Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Database.mdb") & "; Jet OLEDB:Database Password=happy"
    Set myRS = Server.CreateObject("ADODB.Recordset")
    SQL = "SELECT * FROM TheTable"
    myRS.CursorType = 1
    myRS.LockType = 3
    myRS.Open SQL, Conn
%>
<html>
    <head>
        <title>Example</title>
    </head>
    <body>
        <table border="0">
            <%If (Request.Querystring("UpdatedRecord")) Then%>
                <tr><td colspan="6" align="center"><font color="#FF0000"><b>Record has been updated!</b></font></td></tr>
            <%End If%>
            <tr>
                <td>Completed</td>
                <td>Field One</td>
                <td>Field Two</td>
                <td>Field Three</td>
                <td>Completed Date</td>
                <td>&nbsp;</td>
            </tr>
            <%i=1%>
            <%Do Until (myRS.EOF)%>
                <form method="post" action="Post_Update.asp" name="xform<%=i%>">
                    <input type="hidden" name="RecordKeyID" value="<%Response.Write(myRS("KeyID"))%>">
                    <tr>
                        <td align="center"><input type="checkbox" name="xCompleted" value="true"<%If (myRS("Completed")) Then%> checked<%End If%>></td>
                        <td><input type="text" name="xFieldOne" value="<%Response.Write(myRS("FieldOne"))%>"></td>
                        <td><input type="text" name="xFieldTwo" value="<%Response.Write(myRS("FieldTwo"))%>"></td>
                        <td><input type="text" name="xFieldThree" value="<%Response.Write(myRS("FieldThree"))%>"></td>
                        <td><input type="text" name="xCompletedDate" value="<%Response.Write(myRS("CompletedDate"))%>"></td>
                        <td><input type="submit" value="Update"></td>
                    </tr>
                </form>
                <%i=(i+1)%>
                <%myRS.MoveNext%>
            <%Loop%>
        </table>
    </body>
</html>
<%
    myRS.close
    Conn.close
%>
 

Post_Update.asp

<%
    Set Conn = Server.CreateObject("ADODB.Connection")
        Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Database.mdb") & "; Jet OLEDB:Database Password=happy"
    Set rsUpdate = Server.CreateObject("ADODB.Recordset")
    SQL = "SELECT * FROM TheTable WHERE KeyID = " & Request.Form("RecordKeyID")
    rsUpdate.CursorType = 1
    rsUpdate.LockType = 3
    rsUpdate.Open SQL, Conn
 
    If (Request.Form("xCompleted")) Then
        rsUpdate.Fields("Completed") = "true"
    Else
        rsUpdate.Fields("Completed") = "false"
    End If
    rsUpdate.Fields("FieldOne") = Request.Form("xFieldOne")
    rsUpdate.Fields("FieldTwo") = Request.Form("xFieldTwo")
    rsUpdate.Fields("FieldThree") = Request.Form("xFieldThree")
    rsUpdate.Fields("CompletedDate") = Request.Form("xCompletedDate")
 
    rsUpdate.Update    
    rsUpdate.close
    Conn.close
 
    Response.Redirect("Example.asp?UpdatedRecord=true")
%>