I have created an asp page which is used to insert new contract numbers to the list box. I am not sure what I am doing wrong in this code. The table name is correct and so are the fields. Am I missing something? Here is the code. Thank you

Function addContract()
    ' Add Contract
    set rs = server.CreateObject("adodb.recordset")
    SQL = "select * from tblJPAContracts where wasdcontractno = '" & strContractNo & "'"
    ' Run Query
    rs.open sql, cn, 0, 3

    ' Check to see if contract exists
    If rs.eof Then
        ' Contract does not exist, add new record
        With rs
            .addnew
            .Fields("wasdcontractno") = strContractNo
                .update
                .close
        End With
    Else
        ' adding process
        ' Contract already exists, display error message
        'Response.Write "Error: Contract " & strContractNo & " already exists!"
    End If
    set rs = nothing
End Function

Any suggestions I would greatly appreciate.

slypunk

__________________
slypunk

Hello,

Are you getting any error?


  Make sure where are you opening the connection and did you specify the ADO constants? and also sure that where you are calling the function.

I think the recordset opened is not writeable.
use Rs.supports("Addnew") to check.
set the cursor type to dynamic, by default type is Forward-only and read-only.

It does not give me an error at all. In fact, it does nothing at all when I hit the add button. Let me be more specific on my code by showing you exactly what I did:

' --- FUNCTIONS STARTS BELOW

Function addContract()
    ' Add Contract
    set rs = server.CreateObject("adodb.recordset")
    SQL = "select * from tblJPAContracts where wasdcontractno = '" & strContractNo & "'"
    ' Run Query
    rs.open sql, cn, 0, 3

    ' Check to see if contract exists
    If rs.eof Then
        ' Contract does not exist, add new record
        With rs
        .addnew
        .Fields("wasdcontractno") = strContractNo
            '.Fields("dateentered") = now
            .update
            .close
        End With
      Else
        End If
    set rs = nothing
End Function

Function deleteContract()
set rs = server.CreateObject("adodb.recordset")
    SQL = "select * from tblJPAContracts where wasdcontractno = '" & strContractNo & "'"
    ' Run Query
    rs.open sql, cn, 0, 3
    ' Check to see if contract exists
    If not rs.eof Then
        ' deletes record
        With rs
            .delete
            .movenext
            .close
        End With
    Else
    End If
    set rs = nothing
End Function

Function searchContract()
    ' Search for a contract
    Set rs = SearchContractNo(strContractNo)
    ' Check for end of file (eof)
    if rs.EOF then
        'same as below
        'response.write "<script language=javascript>alert('contract Number Not Found');</script>"
        %>
        <script language=javascript>
            alert("Contract Number Not Found");
        </script>
        <%
    else
        wasdcontractno = rs("wasdcontractno")
    end if
end function

' --- PROCESSING LOGIC STARTS BELOW

Dim strContractNo, rs, wasdcontractno, btnAction

strContractNo = request("searchvalue")

' Process the Add/Update/Delete/Search functions
btnAction = request("btnAction")

'TESTmp
'response.write "TEST3 " & strContractNo
'response.end

select case btnAction
    case "Add"
        ' Add the contract
        if strContractNo <> "" then ' Make sure user entered a value
            addContract()
            ' After the add, perform a search to reload contract :)
            searchContract
        end if
    case "Delete"
        'Delete the contract
        if strContractNo <> "" then
            deleteContract()
        end if
    case ""
        ' Process the search
        if strContractNo <> "" then
            searchContract
        end if
end select

I hope this information helps. :D Thanks

slypunk

  I didnt find any error in your code except the opening connection(cn). But you said that you are not getting any error.

And also,

  In the function AddContract, if rs is not EOF you will defly get error coz you are releasing the resources without closing the recordset. And after executing the addContract function you are calling the searchContract function.


' Search for a contract
    Set rs = SearchContractNo(strContractNo)
    ' Check for end of file (eof)
    if rs.EOF then
        'same as below
        'response.write "<script language=javascript>alert('contract Number Not Found');</script>"
        %>
        <script language=javascript>
            alert("Contract Number Not Found");
        </script>
        <%
    else



Do you have any statement like on error resume next in the code?

No I do not but wouldn't that skip the error and not function correctly? What is the best way to rework my way around this code to make it work? Thanks

slypunk

  Or else you do like this,



   ...
    if rs.EOF then
       strQry="INSERT INTO tablename(fld1,fld2) VALUES(" & val1 & "," & val2 & ")"

    conn.execute strQry
    end if
   ...