i cannot update and addnew a database

hi,
i have a problem with my code which is i cannot update and addnew data into the database but i can delete the data.plz give me an idea.this is my code that i wrote.

Expand|Select|Wrap|Line Numbers

 
<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">

<title>Order Record</title>

<meta name="Microsoft Border" content="tlb, default">

</head>
 
<%

    '-----------------------------------------------------

    'Response.CacheControl = "no-cache" 

    'Response.AddHeader "Pragma", "no-cache" 

    'Response.Expires = -1     

    'if Session("adminLoggedIn")<> true then

    '    response.redirect "noaccess.asp"

    'end if

    '-----------------------------------------------------

    Dim NoSequence

    Dim IDSequence

    Dim ComplyFlag

    Dim StationListNumber

    Dim StationListText

    Dim SMSMessage

    Dim i
 
    Const adOpenStatic = 3

    Const adLockOptimistic = 3

    Const adUseClient = 3

    '------------------------------------------------------------------------------------------

    '<meta http-equiv="refresh" content="60">

    'Response.CacheControl = "no-cache" 

    'Response.AddHeader "Pragma", "no-cache"

    'Response.Expires = -1

    '------------------------------------------------------------------------------------------

    Response.Write("<P><STRONG><FONT color=#FF33FF>Scadatron Telemetry System Mobile Number List As On " & Day(Date) & "/" & Month(Date) & "/"  & Year(Date) & " " & Time & "</STRONG></FONT>&nbsp&nbsp&nbsp&nbsp&nbsp")

    Response.Write("</P>")

    MAxRTU = 0    

    '------------------------------------------------------------------------------------------

    'Set myPageCounter = Server.CreateObject("MSWC.PageCounter")

    'myPageCounter.PageHit

    'Response.Write("The time of your visit is "& Time & "<BR>")

    'Response.Write("You are visitor number ")

    'Response.Write(myPageCounter.Hits)

    '------------------------------------------------------------------------------------------    

    Set connectionToDatabase=Server.CreateObject("ADODB.Connection")

    connectionToDatabase.ConnectionTimeout=60

    connectionToDatabase.Open "DSN=Order"

    Set recordCollection = Server.CreateObject("ADODB.Recordset")    

    recordCollection.ActiveConnection = connectionToDatabase

    recordCollection.Source = "SELECT * FROM Record"

    '************************************

Expand|Select|Wrap|Line Numbers

 
    'CursorType

    '0=adOpenForwardOnly - gives you an updateable, non-scrollable recordset. 

    '1=adOpenKeyset - gives you a scrollable keyset recordset 

    '2=adOpenDynamic - gives you a scrollable, fully dynamic recordset 

    '3=adOpenStatic - gives you a read only, scrollable recordset 

    'LockType

    '0=adLockReadOnly - gives a read only recordset, where no updating is allowed 

    '1=adLockPessimistic - gives an updateable recordset, that locks the records as soon as you start editing 

    '2=adLockOptimistic -gives an updateable recordset, where the lock is only placed just before you try and update the record. 

    '3=adLockBatchOptimistic - gives optimistic locking for batch updates 

    'CursorLocation

    '2=adUseServer

    '3=adUseClient

    '************************************

    recordCollection.CursorType = 1

    recordCollection.CursorLocation = 2

    recordCollection.LockType = 2

    recordCollection.Open()

    '************************************

    NoSequence =0    

    IDSequence =0    

    Response.Write("<TABLE BORDER=2 WIDTH=""100%"" cellspacing=1 cellpadding=0 bgcolor=""#E8E8F8""><TR ALIGN=""LEFT"">")

    Response.Write("<TD>No</TD>")

    Response.Write("<TD>No Mobile</TD>")

    Response.Write("<TD>Service Type</TD>")

    Response.Write("<TD>State </TD>")

    Response.Write("<TD>Location</TD>")

    Response.Write("<TD>Topup Date</TD>")

    Response.Write("<TD>Topup Amount (RM)</TD>")

    Response.Write("<TD>Expected Expired Date</TD>")

    Response.Write("<TD>Simcard Owner</TD>")

    Response.Write("<TD>Remark</TD>")
 
    Response.Write("<TD></TD>")

    Response.Write("<TD></TD>")

    Response.Write("</TR>")

    NoSequence = 0

    Do While NOT RecordCollection.EOF             

       if recordCollection("No") <> EMPTY then            

        '---------------------------------------------------------        

        if Left(LCase(recordCollection("remark")),4) = "paid" then

            Response.Write("<TR bgcolor=""#80DDDD"">")

        elseif Left(LCase(recordCollection("remark")),7) = "wait po" then

            Response.Write("<TR bgcolor=""#80FF80"">")

        elseif Left(LCase(recordCollection("remark")),6) = "cancel" then

            Response.Write("<TR bgcolor=""#FFAAAA"">")                

        elseif Left(LCase(recordCollection("remark")),5) <> "refer" then

            Response.Write("<TR bgcolor=""#DD80DD"">")                            

        end if

        '---------------------------------------------------------        

        if recordCollection("No") > NoSequence then NoSequence= recordCollection("No")

        Response.Write("<FORM NAME=Form1 METHOD=GET ACTION=""Order.asp"">")

        Response.Write("<TD><INPUT VALUE=""" & recordCollection("No")         & """ NAME=S_NO style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 20px"">")

        Response.Write("<TD><INPUT VALUE=""" & recordCollection("MobileNo")         & """ MAXLENGTH=50 NAME=S_MobileNo  style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 80px""></TD>")

        Response.Write("<TD><INPUT VALUE=""" & recordCollection("ServiceType")         & """ MAXLENGTH=50 NAME=S_ServiceType  style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 105px""></TD>")

        Response.Write("<TD><INPUT VALUE=""" & recordCollection("State")         & """ MAXLENGTH=50 NAME=S_State  style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 70px""></TD>")

        Response.Write("<TD><INPUT VALUE=""" & recordCollection("Location")         & """ MAXLENGTH=50 NAME=S_Location style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 300px""></TD>")

        DateStr = Day(recordCollection("TopupDate")) & "/" & Month(recordCollection("TopupDate")) & "/"  & Year(recordCollection("TopupDate"))

        if DateStr = "?/?/????" then

            Response.Write("<TD><INPUT VALUE=""????"" MAXLENGTH=50 NAME=S_TopupDate style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 80px""></TD>")

        else

            Response.Write("<TD><INPUT VALUE=""" & DateStr & """ MAXLENGTH=50 NAME=S_TopupDate style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 70px""></TD>")

        end if
 
        Response.Write("<TD><INPUT VALUE=""" & recordCollection("TopupAmount")     & """ MAXLENGTH=50 NAME=S_TopupAmount style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 60px""></TD>")        

        DateStr = Day(recordCollection("ExpiredDate")) & "/" & Month(recordCollection("ExpiredDate")) & "/"  & Year(recordCollection("ExpiredDate"))

        if DateStr = "?/?/????" then

            Response.Write("<TD><INPUT VALUE=""????"" MAXLENGTH=50 NAME=S_ExpiredDate style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 100px""></TD>")

        else

            Response.Write("<TD><INPUT VALUE=""" & DateStr & """ MAXLENGTH=50 NAME=S_ExpiredDate style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 60px""></TD>")

        end if
 
        Response.Write("<TD><INPUT VALUE=""" & recordCollection("owner")     & """ MAXLENGTH=50 NAME=S_owner style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 190px""></TD>")        
 
        Response.Write("<TD><INPUT VALUE=""" & recordCollection("remark")         & """ MAXLENGTH=50 NAME=S_remark style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 60px""></TD>")

        Response.Write("<TD><INPUT TYPE=submit VALUE=""Update"" id=Submit1 name=Submit style=""HEIGHT: 22px; WIDTH: 60px""></TD>")    

        Response.Write("<TD><INPUT TYPE=submit VALUE=""Delete"" id=Submit2 name=Submit style=""HEIGHT: 22px; WIDTH: 60px""></TD>")    

        Response.Write("</FORM>")                    

        Response.Write("</TR>")        

       end if

           RecordCollection.MoveNext

    Loop    

    '--------------------------------------------------------------------------------------------

        NoSequence = NoSequence +1

        Response.Write("<TR><FORM NAME=Form2 METHOD=GET ACTION=""Order.asp"">")

        Response.Write("<TD>" & NoSequence &"</TD>")

        Response.Write("<INPUT TYPE=""HIDDEN""     VALUE=" & NoSequence & " NAME=S_NO style=""HEIGHT: 16px; WIDTH: 20px"">")

        Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_MobileNo  style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 80px""></TD>")

        Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_ServiceType  style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 105px""></TD>")

        Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_State style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 70px""></TD>")

        Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=180 NAME=S_Location style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 300px""></TD>")

        Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_TopupDate style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 70px""></TD>")

        Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_TopupAmount style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 60px""></TD>")

        Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_ExpiredDate style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 60px""></TD>")

        Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_owner style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 190px""></TD>")

        Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_remark style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 60px""></TD>")
 
        Response.Write("<TD ColSpan=2><INPUT TYPE=submit VALUE=""AddNew"" id=AddNew name=Submit style=""HEIGHT: 22px; WIDTH: 60px""></TD>")                

        Response.Write("</FORM>")                    

        Response.Write("</TR>")        

        Response.Write("</TABLE>")        

    connectionToDatabase.Close

    Set connectionToDatabase=Nothing

    '------------------------------------------------------------------------------------------

    if Request.QueryString("submit") = "Delete" then

        ComplyFlag = 0

        if Request.QueryString("S_NO") <> EMPTY then

            if Isnumeric(Request.QueryString("S_NO")) then

                ComplyFlag = 1

            else    

                Response.Write("NO Error!")    

                ComplyFlag = 0

            end if

        else

            Response.Write("NO Error!")    

            ComplyFlag = 0

        end if

Expand|Select|Wrap|Line Numbers

 
        if ComplyFlag = 1 then        

            Set connectionToDatabase=Server.CreateObject("ADODB.Connection")

            connectionToDatabase.ConnectionTimeout=60

            connectionToDatabase.Open "DSN=Order"

            Set recordSet = Server.CreateObject("ADODB.Recordset")

            recordSet.CursorLocation = adUseClient

            recordSet.Open "SELECT * FROM Record" , connectionToDatabase, adOpenStatic, adLockOptimistic

            strSearchCriteria = "No = " & Request.QueryString("S_No")

            recordSet.Find strSearchCriteria

            recordSet.Delete

            connectionToDatabase.Close

            Set connectionToDatabase=Nothing

            '##########################################################################################

            Response.Status ="301 Moved Permanently"

            Response.AddHeader "Location", "Order.asp"

            Response.Write("</html>")

            Response.end

            '##########################################################################################

        end if

    end if    

    '------------------------------------------------------------------------------------------

    if Request.QueryString("submit") = "AddNew" then

        ComplyFlag = 1

        'Response.Write(ComplyFlag)

        if Request.QueryString("S_No") <> EMPTY then

            if Isnumeric(Request.QueryString("S_No")) then

                'ComplyFlag = 1

            else    

                Response.Write("NO Error!")    

                ComplyFlag = 0

            end if

        else

            Response.Write("NO Error!")    

            ComplyFlag = 0

        end if
 
        if ComplyFlag = 1 then    

            '************************************

            Set connectionToDatabase=Server.CreateObject("ADODB.Connection")

            connectionToDatabase.ConnectionTimeout=60

            connectionToDatabase.Open "DSN=Order"

            Set recordSet = Server.CreateObject("ADODB.Recordset")

            recordSet.ActiveConnection = connectionToDatabase

            recordSet.Source = "SELECT * FROM Record"

            '************************************

            'CursorType

            '0=adOpenForwardOnly - gives you an updateable, non-scrollable recordset. 

            '1=adOpenKeyset - gives you a scrollable keyset recordset 

            '2=adOpenDynamic - gives you a scrollable, fully dynamic recordset 

            '3=adOpenStatic - gives you a read only, scrollable recordset 

            'LockType

            '0=adLockReadOnly - gives a read only recordset, where no updating is allowed 

            '1=adLockPessimistic - gives an updateable recordset, that locks the records as soon as you start editing 

            '2=adLockOptimistic -gives an updateable recordset, where the lock is only placed just before you try and update the record. 

            '3=adLockBatchOptimistic - gives optimistic locking for batch updates 

            'CursorLocation

            '2=adUseServer

            '3=adUseClient

            '************************************

            recordSet.CursorType = 1

            recordSet.CursorLocation = 2

            recordSet.LockType = 2

            recordSet.Open()

            '************************************

            NoSequence = 1

            Do While NOT recordSet.EOF             

                if recordSet("No") > NoSequence then

                    NoSequence = recordSet("No")

                end if

                       recordSet.MoveNext

            Loop    

            NoSequence = NoSequence +1

            '************************************

            '###########################################################################################        

            if CInt(Request.QueryString("S_No")) <> NoSequence then

                connectionToDatabase.Close

                Set connectionToDatabase=Nothing

                Response.Status ="301 Moved Permanently"            

                Response.AddHeader "Location", "Order.asp"

                Response.Write("</html>")

                Response.end

            end if            

            '###########################################################################################

            recordSet.Addnew

            '----------------------------------------------------------------

            recordSet("No") = IDSequence ' recordSet.RecordCount 'Request.QueryString("No")

            recordSet("MobileNo") = "????"

            recordSet("ServiceType") = "????"

            recordSet("State") = "????"

            recordSet("Location") =" ????"

            recordSet("TopupDate") = "????"

            recordSet("TopupAmount") = 0        

            recordSet("ExpiredDate") = "????"

            recordSet("owner") = "????"

            recordSet("remark") = "????"
 
            '----------------------------------------------------------------            
 
            if Request.QueryString("S_MobileNo") <> EMPTY then

                recordSet("MobileNo") = Request.QueryString("S_MobileNo")

            end if

            if Request.QueryString("S_ServiceType") <> EMPTY then

                recordSet("ServiceType") = Request.QueryString("S_SeviceType")
 
            end if

            if Request.QueryString("S_State") <> EMPTY then

                recordSet("State") = Request.QueryString("S_State")

            end if

            if Request.QueryString("S_Location") <> EMPTY then

                recordSet("Description") = Request.QueryString("S_Location")

            end if

            if Request.QueryString("S_TopupDate") <> EMPTY then

                if IsDate(Request.QueryString("S_TopupDate")) then            

                    DateStr = Day(Request.QueryString("S_TopupDate")) & "/" & Month(Request.QueryString("S_TopupDate")) & "/" & Year(Request.QueryString("S_TopupDate"))

                    recordSet("TopupDate") = DateStr

                end if

            end if    

            if Request.QueryString("S_TopupAmount") <> EMPTY then

                if isnumeric(Request.QueryString("S_TopupAmount")) then

                     recordSet("TopAmount") = Request.QueryString("S_TopAmount")

                end if

            end if

            if Request.QueryString("S_ExpiredDate") <> EMPTY then

                if IsDate(Request.QueryString("S_ExpiredDate")) then            

                    DateStr = Day(Request.QueryString("S_ExpiredDate")) & "/" & Month(Request.QueryString("S_ExpiredDate")) & "/" & Year(Request.QueryString("S_ExpiredDate"))

                    recordSet("ExpiredDate") = DateStr

                end if

            end if

            if Request.QueryString("S_owner") <> EMPTY then

                recordSet("owner") = Request.QueryString("S_owner")

            end if

            if Request.QueryString("S_remark") <> EMPTY then

                recordSet("remark") = Request.QueryString("S_remark")

            end if
 
            recordSet.Update

            connectionToDatabase.Close

            Set connectionToDatabase=Nothing

            '##########################################################################################

            Response.Status ="301 Moved Permanently"

            Response.AddHeader "Location", "Order.asp"

            Response.Write("</html>")

            Response.end

            '##########################################################################################

        end if

    end if    
 
    '------------------------------------------------------------------------------------------

    if Request.QueryString("S_No") <> EMPTY AND Request.QueryString("Submit") = "Update" then        

        'Response.Write "Button:" & Request.QueryString("Submit") 

        'Response.Write "No:" & Request.QueryString("S_No")

        Set connectionToDatabase=Server.CreateObject("ADODB.Connection")

        connectionToDatabase.ConnectionTimeout=60

        connectionToDatabase.Open "DSN=Order"

        Set recordSet = Server.CreateObject("ADODB.Recordset")

        recordSet.Open "SELECT * FROM Record WHERE No = " & Request.QueryString("S_No"),connectionToDatabase,1,2        

        if recordset.EOF then

            Response.Write("no such ID!")            

        else            

            recordSet("No") = Request.QueryString("S_No")

            if Request.QueryString("S_MobileNo") <> EMPTY then

                recordSet("MobileNo") = Request.QueryString("S_MobileNo")

            end if

            if Request.QueryString("S_ServiceType") <> EMPTY then

                recordSet("ServiceType") = Request.QueryString("S_ServiceType")

            end if

            if Request.QueryString("S_State") <> EMPTY then

                recordSet("State") = Request.QueryString("S_State")

            end if

            if Request.QueryString("S_Location") <> EMPTY then

                recordSet("Location") = Request.QueryString("S_Location")

            end if

            if Request.QueryString("S_TopupDate") <> EMPTY then

                if IsDate(Request.QueryString("S_TopupDate")) then            

                    DateStr = Day(Request.QueryString("S_TopupDate")) & "/" & Month(Request.QueryString("S_TopupDate")) & "/" & Year(Request.QueryString("S_TopupDate"))

                    recordSet("TopupDate") = DateStr

                end if

            end if    

            if Request.QueryString("S_TopupAmount") <> EMPTY then

                if isnumeric(Request.QueryString("S_TopupAmount")) then

                    recordSet("TopupAmount") = Request.QueryString("S_TopupAmount")

                end if

            end if

            if Request.QueryString("S_ExpiredDate") <> EMPTY then

                if IsDate(Request.QueryString("S_ExpiredDate")) then            

                    DateStr = Day(Request.QueryString("S_ExpiredDate")) & "/" & Month(Request.QueryString("S_ExpiredDate")) & "/" & Year(Request.QueryString("S_ExpiredDate"))

                    recordSet("ExpiredDate") = DateStr

                end if

            end if

            if Request.QueryString("S_owner") <> EMPTY then

                recordSet("owner") = Request.QueryString("S_owner")

                end if
 
            if Request.QueryString("S_remark") <> EMPTY then

                recordSet("remark") = Request.QueryString("S_remark")

            end if
 
            recordSet.Update

        end if        

        connectionToDatabase.Close

        Set connectionToDatabase=Nothing

        '##########################################################################################

        Response.Status ="301 Moved Permanently"

        Response.AddHeader "Location", "Order.asp"

        Response.Write("</html>")

        Response.end

        '##########################################################################################

    end if    

    '------------------------------------------------------------------------------------------

%>        

Last modified: August 9, 2007</font></b><!--mstheme--></font></h5>

</BODY>

</HTML>

Aug 20 '07 #1

Follow Post Reply

2292

jhardman

3,406

Expert 2GB

Hiya, and welcome to the scripts developer network.

I'm afraid the code you posted is too long for me to look thru. let's shorten it. Write the shortest code sample you can that should work but doesn't, and the same with an example of code which does work. I hope I will be able to help you out with this.

Jared

Aug 21 '07 #2

Post your reply

Similar topics