By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,709 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,709 IT Pros & Developers. It's quick & easy.

i cannot update and addnew a database

P: 18
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
  1. <html>
  2. <head>
  3. <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
  4. <title>Order Record</title>
  5. <meta name="Microsoft Border" content="tlb, default">
  6. </head>
  7.  
  8. <%
  9.     '-----------------------------------------------------
  10.     'Response.CacheControl = "no-cache" 
  11.     'Response.AddHeader "Pragma", "no-cache" 
  12.     'Response.Expires = -1     
  13.     'if Session("adminLoggedIn")<> true then
  14.     '    response.redirect "noaccess.asp"
  15.     'end if
  16.     '-----------------------------------------------------
  17.     Dim NoSequence
  18.     Dim IDSequence
  19.     Dim ComplyFlag
  20.     Dim StationListNumber
  21.     Dim StationListText
  22.     Dim SMSMessage
  23.     Dim i
  24.  
  25.     Const adOpenStatic = 3
  26.     Const adLockOptimistic = 3
  27.     Const adUseClient = 3
  28.     '------------------------------------------------------------------------------------------
  29.     '<meta http-equiv="refresh" content="60">
  30.     'Response.CacheControl = "no-cache" 
  31.     'Response.AddHeader "Pragma", "no-cache"
  32.     'Response.Expires = -1
  33.     '------------------------------------------------------------------------------------------
  34.     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")
  35.     Response.Write("</P>")
  36.     MAxRTU = 0    
  37.     '------------------------------------------------------------------------------------------
  38.     'Set myPageCounter = Server.CreateObject("MSWC.PageCounter")
  39.     'myPageCounter.PageHit
  40.     'Response.Write("The time of your visit is "& Time & "<BR>")
  41.     'Response.Write("You are visitor number ")
  42.     'Response.Write(myPageCounter.Hits)
  43.     '------------------------------------------------------------------------------------------    
  44.     Set connectionToDatabase=Server.CreateObject("ADODB.Connection")
  45.     connectionToDatabase.ConnectionTimeout=60
  46.     connectionToDatabase.Open "DSN=Order"
  47.     Set recordCollection = Server.CreateObject("ADODB.Recordset")    
  48.     recordCollection.ActiveConnection = connectionToDatabase
  49.     recordCollection.Source = "SELECT * FROM Record"
  50.     '************************************
Expand|Select|Wrap|Line Numbers
  1.     'CursorType
  2.     '0=adOpenForwardOnly - gives you an updateable, non-scrollable recordset. 
  3.     '1=adOpenKeyset - gives you a scrollable keyset recordset 
  4.     '2=adOpenDynamic - gives you a scrollable, fully dynamic recordset 
  5.     '3=adOpenStatic - gives you a read only, scrollable recordset 
  6.     'LockType
  7.     '0=adLockReadOnly - gives a read only recordset, where no updating is allowed 
  8.     '1=adLockPessimistic - gives an updateable recordset, that locks the records as soon as you start editing 
  9.     '2=adLockOptimistic -gives an updateable recordset, where the lock is only placed just before you try and update the record. 
  10.     '3=adLockBatchOptimistic - gives optimistic locking for batch updates 
  11.     'CursorLocation
  12.     '2=adUseServer
  13.     '3=adUseClient
  14.     '************************************
  15.     recordCollection.CursorType = 1
  16.     recordCollection.CursorLocation = 2
  17.     recordCollection.LockType = 2
  18.     recordCollection.Open()
  19.     '************************************
  20.     NoSequence =0    
  21.     IDSequence =0    
  22.     Response.Write("<TABLE BORDER=2 WIDTH=""100%"" cellspacing=1 cellpadding=0 bgcolor=""#E8E8F8""><TR ALIGN=""LEFT"">")
  23.     Response.Write("<TD>No</TD>")
  24.     Response.Write("<TD>No Mobile</TD>")
  25.     Response.Write("<TD>Service Type</TD>")
  26.     Response.Write("<TD>State </TD>")
  27.     Response.Write("<TD>Location</TD>")
  28.     Response.Write("<TD>Topup Date</TD>")
  29.     Response.Write("<TD>Topup Amount (RM)</TD>")
  30.     Response.Write("<TD>Expected Expired Date</TD>")
  31.     Response.Write("<TD>Simcard Owner</TD>")
  32.     Response.Write("<TD>Remark</TD>")
  33.  
  34.     Response.Write("<TD></TD>")
  35.     Response.Write("<TD></TD>")
  36.     Response.Write("</TR>")
  37.     NoSequence = 0
  38.     Do While NOT RecordCollection.EOF             
  39.        if recordCollection("No") <> EMPTY then            
  40.         '---------------------------------------------------------        
  41.         if Left(LCase(recordCollection("remark")),4) = "paid" then
  42.             Response.Write("<TR bgcolor=""#80DDDD"">")
  43.         elseif Left(LCase(recordCollection("remark")),7) = "wait po" then
  44.             Response.Write("<TR bgcolor=""#80FF80"">")
  45.         elseif Left(LCase(recordCollection("remark")),6) = "cancel" then
  46.             Response.Write("<TR bgcolor=""#FFAAAA"">")                
  47.         elseif Left(LCase(recordCollection("remark")),5) <> "refer" then
  48.             Response.Write("<TR bgcolor=""#DD80DD"">")                            
  49.         end if
  50.         '---------------------------------------------------------        
  51.         if recordCollection("No") > NoSequence then NoSequence= recordCollection("No")
  52.         Response.Write("<FORM NAME=Form1 METHOD=GET ACTION=""Order.asp"">")
  53.         Response.Write("<TD><INPUT VALUE=""" & recordCollection("No")         & """ NAME=S_NO style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 20px"">")
  54.         Response.Write("<TD><INPUT VALUE=""" & recordCollection("MobileNo")         & """ MAXLENGTH=50 NAME=S_MobileNo  style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 80px""></TD>")
  55.         Response.Write("<TD><INPUT VALUE=""" & recordCollection("ServiceType")         & """ MAXLENGTH=50 NAME=S_ServiceType  style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 105px""></TD>")
  56.         Response.Write("<TD><INPUT VALUE=""" & recordCollection("State")         & """ MAXLENGTH=50 NAME=S_State  style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 70px""></TD>")
  57.         Response.Write("<TD><INPUT VALUE=""" & recordCollection("Location")         & """ MAXLENGTH=50 NAME=S_Location style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 300px""></TD>")
  58.         DateStr = Day(recordCollection("TopupDate")) & "/" & Month(recordCollection("TopupDate")) & "/"  & Year(recordCollection("TopupDate"))
  59.         if DateStr = "?/?/????" then
  60.             Response.Write("<TD><INPUT VALUE=""????"" MAXLENGTH=50 NAME=S_TopupDate style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 80px""></TD>")
  61.         else
  62.             Response.Write("<TD><INPUT VALUE=""" & DateStr & """ MAXLENGTH=50 NAME=S_TopupDate style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 70px""></TD>")
  63.         end if
  64.  
  65.         Response.Write("<TD><INPUT VALUE=""" & recordCollection("TopupAmount")     & """ MAXLENGTH=50 NAME=S_TopupAmount style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 60px""></TD>")        
  66.         DateStr = Day(recordCollection("ExpiredDate")) & "/" & Month(recordCollection("ExpiredDate")) & "/"  & Year(recordCollection("ExpiredDate"))
  67.         if DateStr = "?/?/????" then
  68.             Response.Write("<TD><INPUT VALUE=""????"" MAXLENGTH=50 NAME=S_ExpiredDate style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 100px""></TD>")
  69.         else
  70.             Response.Write("<TD><INPUT VALUE=""" & DateStr & """ MAXLENGTH=50 NAME=S_ExpiredDate style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 60px""></TD>")
  71.         end if
  72.  
  73.  
  74.         Response.Write("<TD><INPUT VALUE=""" & recordCollection("owner")     & """ MAXLENGTH=50 NAME=S_owner style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 190px""></TD>")        
  75.  
  76.         Response.Write("<TD><INPUT VALUE=""" & recordCollection("remark")         & """ MAXLENGTH=50 NAME=S_remark style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 60px""></TD>")
  77.         Response.Write("<TD><INPUT TYPE=submit VALUE=""Update"" id=Submit1 name=Submit style=""HEIGHT: 22px; WIDTH: 60px""></TD>")    
  78.         Response.Write("<TD><INPUT TYPE=submit VALUE=""Delete"" id=Submit2 name=Submit style=""HEIGHT: 22px; WIDTH: 60px""></TD>")    
  79.         Response.Write("</FORM>")                    
  80.         Response.Write("</TR>")        
  81.        end if
  82.            RecordCollection.MoveNext
  83.     Loop    
  84.     '--------------------------------------------------------------------------------------------
  85.         NoSequence = NoSequence +1
  86.         Response.Write("<TR><FORM NAME=Form2 METHOD=GET ACTION=""Order.asp"">")
  87.         Response.Write("<TD>" & NoSequence &"</TD>")
  88.         Response.Write("<INPUT TYPE=""HIDDEN""     VALUE=" & NoSequence & " NAME=S_NO style=""HEIGHT: 16px; WIDTH: 20px"">")
  89.         Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_MobileNo  style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 80px""></TD>")
  90.         Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_ServiceType  style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 105px""></TD>")
  91.         Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_State style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 70px""></TD>")
  92.         Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=180 NAME=S_Location style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 300px""></TD>")
  93.         Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_TopupDate style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 70px""></TD>")
  94.         Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_TopupAmount style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 60px""></TD>")
  95.         Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_ExpiredDate style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 60px""></TD>")
  96.         Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_owner style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 190px""></TD>")
  97.         Response.Write("<TD><INPUT VALUE="""" MAXLENGTH=25 NAME=S_remark style=""border:0px solid #cccccc HEIGHT: 16px; WIDTH: 60px""></TD>")
  98.  
  99.         Response.Write("<TD ColSpan=2><INPUT TYPE=submit VALUE=""AddNew"" id=AddNew name=Submit style=""HEIGHT: 22px; WIDTH: 60px""></TD>")                
  100.         Response.Write("</FORM>")                    
  101.         Response.Write("</TR>")        
  102.         Response.Write("</TABLE>")        
  103.     connectionToDatabase.Close
  104.     Set connectionToDatabase=Nothing
  105.     '------------------------------------------------------------------------------------------
  106.     if Request.QueryString("submit") = "Delete" then
  107.         ComplyFlag = 0
  108.         if Request.QueryString("S_NO") <> EMPTY then
  109.             if Isnumeric(Request.QueryString("S_NO")) then
  110.                 ComplyFlag = 1
  111.             else    
  112.                 Response.Write("NO Error!")    
  113.                 ComplyFlag = 0
  114.             end if
  115.         else
  116.             Response.Write("NO Error!")    
  117.             ComplyFlag = 0
  118.         end if
  119.  
Expand|Select|Wrap|Line Numbers
  1.         if ComplyFlag = 1 then        
  2.             Set connectionToDatabase=Server.CreateObject("ADODB.Connection")
  3.             connectionToDatabase.ConnectionTimeout=60
  4.             connectionToDatabase.Open "DSN=Order"
  5.             Set recordSet = Server.CreateObject("ADODB.Recordset")
  6.             recordSet.CursorLocation = adUseClient
  7.             recordSet.Open "SELECT * FROM Record" , connectionToDatabase, adOpenStatic, adLockOptimistic
  8.             strSearchCriteria = "No = " & Request.QueryString("S_No")
  9.             recordSet.Find strSearchCriteria
  10.             recordSet.Delete
  11.             connectionToDatabase.Close
  12.             Set connectionToDatabase=Nothing
  13.             '##########################################################################################
  14.             Response.Status ="301 Moved Permanently"
  15.             Response.AddHeader "Location", "Order.asp"
  16.             Response.Write("</html>")
  17.             Response.end
  18.             '##########################################################################################
  19.         end if
  20.     end if    
  21.     '------------------------------------------------------------------------------------------
  22.     if Request.QueryString("submit") = "AddNew" then
  23.         ComplyFlag = 1
  24.         'Response.Write(ComplyFlag)
  25.         if Request.QueryString("S_No") <> EMPTY then
  26.             if Isnumeric(Request.QueryString("S_No")) then
  27.                 'ComplyFlag = 1
  28.             else    
  29.                 Response.Write("NO Error!")    
  30.                 ComplyFlag = 0
  31.             end if
  32.         else
  33.             Response.Write("NO Error!")    
  34.             ComplyFlag = 0
  35.         end if
  36.  
  37.         if ComplyFlag = 1 then    
  38.             '************************************
  39.             Set connectionToDatabase=Server.CreateObject("ADODB.Connection")
  40.             connectionToDatabase.ConnectionTimeout=60
  41.             connectionToDatabase.Open "DSN=Order"
  42.             Set recordSet = Server.CreateObject("ADODB.Recordset")
  43.             recordSet.ActiveConnection = connectionToDatabase
  44.             recordSet.Source = "SELECT * FROM Record"
  45.             '************************************
  46.             'CursorType
  47.             '0=adOpenForwardOnly - gives you an updateable, non-scrollable recordset. 
  48.             '1=adOpenKeyset - gives you a scrollable keyset recordset 
  49.             '2=adOpenDynamic - gives you a scrollable, fully dynamic recordset 
  50.             '3=adOpenStatic - gives you a read only, scrollable recordset 
  51.             'LockType
  52.             '0=adLockReadOnly - gives a read only recordset, where no updating is allowed 
  53.             '1=adLockPessimistic - gives an updateable recordset, that locks the records as soon as you start editing 
  54.             '2=adLockOptimistic -gives an updateable recordset, where the lock is only placed just before you try and update the record. 
  55.             '3=adLockBatchOptimistic - gives optimistic locking for batch updates 
  56.             'CursorLocation
  57.             '2=adUseServer
  58.             '3=adUseClient
  59.             '************************************
  60.             recordSet.CursorType = 1
  61.             recordSet.CursorLocation = 2
  62.             recordSet.LockType = 2
  63.             recordSet.Open()
  64.             '************************************
  65.             NoSequence = 1
  66.             Do While NOT recordSet.EOF             
  67.                 if recordSet("No") > NoSequence then
  68.                     NoSequence = recordSet("No")
  69.                 end if
  70.                        recordSet.MoveNext
  71.             Loop    
  72.             NoSequence = NoSequence +1
  73.             '************************************
  74.             '###########################################################################################        
  75.             if CInt(Request.QueryString("S_No")) <> NoSequence then
  76.                 connectionToDatabase.Close
  77.                 Set connectionToDatabase=Nothing
  78.                 Response.Status ="301 Moved Permanently"            
  79.                 Response.AddHeader "Location", "Order.asp"
  80.                 Response.Write("</html>")
  81.                 Response.end
  82.             end if            
  83.             '###########################################################################################
  84.             recordSet.Addnew
  85.             '----------------------------------------------------------------
  86.             recordSet("No") = IDSequence ' recordSet.RecordCount 'Request.QueryString("No")
  87.             recordSet("MobileNo") = "????"
  88.             recordSet("ServiceType") = "????"
  89.             recordSet("State") = "????"
  90.             recordSet("Location") =" ????"
  91.             recordSet("TopupDate") = "????"
  92.             recordSet("TopupAmount") = 0        
  93.             recordSet("ExpiredDate") = "????"
  94.             recordSet("owner") = "????"
  95.             recordSet("remark") = "????"
  96.  
  97.             '----------------------------------------------------------------            
  98.  
  99.             if Request.QueryString("S_MobileNo") <> EMPTY then
  100.                 recordSet("MobileNo") = Request.QueryString("S_MobileNo")
  101.             end if
  102.             if Request.QueryString("S_ServiceType") <> EMPTY then
  103.                 recordSet("ServiceType") = Request.QueryString("S_SeviceType")
  104.  
  105.             end if
  106.             if Request.QueryString("S_State") <> EMPTY then
  107.                 recordSet("State") = Request.QueryString("S_State")
  108.             end if
  109.             if Request.QueryString("S_Location") <> EMPTY then
  110.                 recordSet("Description") = Request.QueryString("S_Location")
  111.             end if
  112.             if Request.QueryString("S_TopupDate") <> EMPTY then
  113.                 if IsDate(Request.QueryString("S_TopupDate")) then            
  114.                     DateStr = Day(Request.QueryString("S_TopupDate")) & "/" & Month(Request.QueryString("S_TopupDate")) & "/" & Year(Request.QueryString("S_TopupDate"))
  115.                     recordSet("TopupDate") = DateStr
  116.                 end if
  117.             end if    
  118.             if Request.QueryString("S_TopupAmount") <> EMPTY then
  119.                 if isnumeric(Request.QueryString("S_TopupAmount")) then
  120.                      recordSet("TopAmount") = Request.QueryString("S_TopAmount")
  121.                 end if
  122.             end if
  123.             if Request.QueryString("S_ExpiredDate") <> EMPTY then
  124.                 if IsDate(Request.QueryString("S_ExpiredDate")) then            
  125.                     DateStr = Day(Request.QueryString("S_ExpiredDate")) & "/" & Month(Request.QueryString("S_ExpiredDate")) & "/" & Year(Request.QueryString("S_ExpiredDate"))
  126.                     recordSet("ExpiredDate") = DateStr
  127.                 end if
  128.             end if
  129.             if Request.QueryString("S_owner") <> EMPTY then
  130.                 recordSet("owner") = Request.QueryString("S_owner")
  131.             end if
  132.             if Request.QueryString("S_remark") <> EMPTY then
  133.                 recordSet("remark") = Request.QueryString("S_remark")
  134.             end if
  135.  
  136.  
  137.  
  138.             recordSet.Update
  139.             connectionToDatabase.Close
  140.             Set connectionToDatabase=Nothing
  141.             '##########################################################################################
  142.             Response.Status ="301 Moved Permanently"
  143.             Response.AddHeader "Location", "Order.asp"
  144.             Response.Write("</html>")
  145.             Response.end
  146.             '##########################################################################################
  147.         end if
  148.     end if    
  149.  
  150.     '------------------------------------------------------------------------------------------
  151.     if Request.QueryString("S_No") <> EMPTY AND Request.QueryString("Submit") = "Update" then        
  152.         'Response.Write "Button:" & Request.QueryString("Submit") 
  153.         'Response.Write "No:" & Request.QueryString("S_No")
  154.         Set connectionToDatabase=Server.CreateObject("ADODB.Connection")
  155.         connectionToDatabase.ConnectionTimeout=60
  156.         connectionToDatabase.Open "DSN=Order"
  157.         Set recordSet = Server.CreateObject("ADODB.Recordset")
  158.         recordSet.Open "SELECT * FROM Record WHERE No = " & Request.QueryString("S_No"),connectionToDatabase,1,2        
  159.         if recordset.EOF then
  160.             Response.Write("no such ID!")            
  161.         else            
  162.             recordSet("No") = Request.QueryString("S_No")
  163.             if Request.QueryString("S_MobileNo") <> EMPTY then
  164.                 recordSet("MobileNo") = Request.QueryString("S_MobileNo")
  165.             end if
  166.             if Request.QueryString("S_ServiceType") <> EMPTY then
  167.                 recordSet("ServiceType") = Request.QueryString("S_ServiceType")
  168.             end if
  169.             if Request.QueryString("S_State") <> EMPTY then
  170.                 recordSet("State") = Request.QueryString("S_State")
  171.             end if
  172.             if Request.QueryString("S_Location") <> EMPTY then
  173.                 recordSet("Location") = Request.QueryString("S_Location")
  174.             end if
  175.             if Request.QueryString("S_TopupDate") <> EMPTY then
  176.                 if IsDate(Request.QueryString("S_TopupDate")) then            
  177.                     DateStr = Day(Request.QueryString("S_TopupDate")) & "/" & Month(Request.QueryString("S_TopupDate")) & "/" & Year(Request.QueryString("S_TopupDate"))
  178.                     recordSet("TopupDate") = DateStr
  179.                 end if
  180.             end if    
  181.             if Request.QueryString("S_TopupAmount") <> EMPTY then
  182.                 if isnumeric(Request.QueryString("S_TopupAmount")) then
  183.                     recordSet("TopupAmount") = Request.QueryString("S_TopupAmount")
  184.                 end if
  185.             end if
  186.             if Request.QueryString("S_ExpiredDate") <> EMPTY then
  187.                 if IsDate(Request.QueryString("S_ExpiredDate")) then            
  188.                     DateStr = Day(Request.QueryString("S_ExpiredDate")) & "/" & Month(Request.QueryString("S_ExpiredDate")) & "/" & Year(Request.QueryString("S_ExpiredDate"))
  189.                     recordSet("ExpiredDate") = DateStr
  190.                 end if
  191.             end if
  192.             if Request.QueryString("S_owner") <> EMPTY then
  193.                 recordSet("owner") = Request.QueryString("S_owner")
  194.                 end if
  195.  
  196.  
  197.             if Request.QueryString("S_remark") <> EMPTY then
  198.                 recordSet("remark") = Request.QueryString("S_remark")
  199.             end if
  200.  
  201.  
  202.             recordSet.Update
  203.         end if        
  204.         connectionToDatabase.Close
  205.         Set connectionToDatabase=Nothing
  206.         '##########################################################################################
  207.         Response.Status ="301 Moved Permanently"
  208.         Response.AddHeader "Location", "Order.asp"
  209.         Response.Write("</html>")
  210.         Response.end
  211.         '##########################################################################################
  212.     end if    
  213.     '------------------------------------------------------------------------------------------
  214. %>        
  215. Last modified: August 9, 2007</font></b><!--mstheme--></font></h5>
  216. </BODY>
  217. </HTML>
Aug 20 '07 #1
Share this Question
Share on Google+
1 Reply


jhardman
Expert 2.5K+
P: 3,405
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

Sign in to post your reply or Sign up for a free account.