Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Not getting expected error!

hello all - Thanks for all the help from 'fritz the blank' and 'golfDoctor' I completed a web project that includes dynamic select boxes and use of ADO to enter and extract data to a web based microsoft access 7.0 database.  Anyway I have  one loose end  involoving a unique filed.  The database is defined as follows:

Fields:(Name     -   Type    -    Size)
--------------------------------------------
           TxtNum      Double        8
           NetId         Text           50
           CktType     Text           15
           Network     Text           15
           Equip         Text           30
           Equip         TypeText    30
           Res            Text           30
           Other         Memo          0
           Date           Date/Time   8
           Time          Date/Time   8

Indexes (Name     -    Fields    -    Unique)
-------------------------------------------------
              TxtNum       +TxtNum     True

The problem is I don't know how to identify an entry as unique, once the user hits submit.  I assumed that if it tried to enter a duplicate entry it would generate an error, and I could trap the error for evaluation.  Then do something like if  err.number = x then alert user.  Instead it acts normal as if everything was fine, except that if you go back and look at the batabase the entry isn't there.  Here is the code that writes the data out:

      <%
            Dim txtNumber, txtNetID, txtCktType, txtNetwork, txtEquipList
            Dim txtEquipType, txtEquipCode, txtOther, cDate, cTime
            Dim conn, sql
            
            
            cDate = Date
            cTime = Time
            
            txtNumber = Request.QueryString("txtNumber")
            txtNetID = Request.QueryString("txtNetID")
            txtCktType = Request.QueryString("txtCktType")
            txtNetwork = Request.QueryString("txtNetwork")
            txtEquipList = Request.QueryString("txtEquipList")
            txtEquipType = Request.QueryString("txtEquipType")
            txtEquipCode = Request.QueryString("txtEquipCode")
            txtOther = Request.QueryString("txtOther")
            
            response.write("<p>&nbsp;</p>")
            response.write("<b><font face=""Arial"" size=""4"">")
            response.write("Please wait while the page updates the<br>")
            response.write("database.....")
            response.write("<p>&nbsp;</p>")
            
            set conn=Server.CreateObject("ADODB.Connection")
            conn.Provider="Microsoft.Jet.OLEDB.4.0"
            Conn.Mode = 3
            conn.Open Server.MapPath("ProActive.mdb")
            
            'set rs=Server.CreateObject("ADODB.recordset")
            'rs.Open "Select * from tblMain", conn
            
            sql = "INSERT INTO tblMain VALUES ('"
            sql = sql & txtNumber & "','"
            sql = sql & txtNetID & "','"
            sql = sql & txtCktType & "','"
            sql = sql & txtNetwork & "','"
            sql = sql & txtEquipList & "','"
            sql = sql & txtEquipType & "','"
            sql = sql & txtEquipCode & "','"
            sql = sql & txtOther & "','"
            sql = sql & cDate & "','"
            sql = sql & cTime & "')"
            
            on error resume next
            conn.Execute sql
            
            if err<>0 then
                    response.write ("An Error has occured, of the type '" & Err.description & "' ,")
                    response.write ("<br> number: " & err.Number)
            else
                    response.write ("Done.")

            end if
            
            conn.close
            
            response.redirect("http://maoc.mcilink.com/pnmc.htm")

      %>

0
acaraciolo
Asked:
acaraciolo
  • 3
  • 3
  • 2
1 Solution
 
fritz_the_blankCommented:
I would do something slightly different:

 set conn=Server.CreateObject("ADODB.Connection")
          conn.Provider="Microsoft.Jet.OLEDB.4.0"
          Conn.Mode = 3
          conn.Open Server.MapPath("ProActive.mdb")
         
          'set rs=Server.CreateObject("ADODB.recordset")
          'rs.Open "Select * from tblMain", conn
         
I would create a sql select here with the parameters passed from the form. If you have rs.eof, then you know that no record exists, so you can just use the rs.addnew() method to populate your database. If you don't have rs.eof, then you know that the record already exists, so you can warn the user. Does that makes sense? If so, and you want to pursue that option, please let me know if you need a hand to implement.

FtB
0
 
joeposter649Commented:
Looks like you're always going to redirect.  Do a response.end after writing out the error.
0
 
joeposter649Commented:
Also, it's good practice to set "on error goto 0" after your error check so further errors in the page aren't ignored.  I suspect you're getting an error on the redirect because you already wrote to the response object.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
acaracioloAuthor Commented:
fritz.. I modifyed the code as follows:

      <%
            Dim txtNumber, txtNetID, txtCktType, txtNetwork, txtEquipList
            Dim txtEquipType, txtEquipCode, txtOther, cDate, cTime
            Dim conn, sql
            
            
            cDate = Date
            cTime = Time
            
            txtNumber = Request.QueryString("txtNumber")
            txtNetID = Request.QueryString("txtNetID")
            txtCktType = Request.QueryString("txtCktType")
            txtNetwork = Request.QueryString("txtNetwork")
            txtEquipList = Request.QueryString("txtEquipList")
            txtEquipType = Request.QueryString("txtEquipType")
            txtEquipCode = Request.QueryString("txtEquipCode")
            txtOther = Request.QueryString("txtOther")
            
            response.write("<p>&nbsp;</p>")
            response.write("<b><font face=""Arial"" size=""4"">")
            response.write("Please wait while the page updates the<br>")
            response.write("database.....")
            response.write("<p>&nbsp;</p>")
            
            set conn=Server.CreateObject("ADODB.Connection")
            conn.Provider="Microsoft.Jet.OLEDB.4.0"
            Conn.Mode = 3
            conn.Open Server.MapPath("ProActive.mdb")
            
            set rs=Server.CreateObject("ADODB.recordset")
            sql = "SELECT * FROM tblMain WHERE TxtNum = " & txtNumber
            rs.Open sql, conn
            
            if rs.EOF = True Then
            
                  rs.AddNew
                  rs.Fields("TxtNum") = txtNumber
                  rs.Fields("NetID") = txtNetID
                  rs.Fields("CktType") = txtCktType
                  rs.Fields("Network") = txtNetwork
                  rs.Fields("Equip") = txtEquipList
                  rs.Fields("EquipType") = txtEquipType
                  rs.Fields("Res") = txtEquipCode
                  rs.Fields("Other") = txtOther
                  rs.Fields("Date") = cDate
                  rs.Fields("Time") = cTime
                  rs.Update

                  rs.close
                  conn.close
            
                  'response.redirect("http://maoc.mcilink.com/test1.htm")
                  
            else
            
                                                rs.close
                  conn.close                  
                                                response.write("<p>&nbsp;</p>")
                  response.write("It appears you tried to enter a duplicate Ticket number...")
                  
            
            end if

      %>


and got the following error.....

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.


0
 
acaracioloAuthor Commented:
joeposter649 - The redirect works fine I put <% response.buffer = true %> on the TOP line above the <HTML> tag.  Also, while I didn't go into detail.... in testing I rem'd out the redirect to see any errors and none occured in the original page, the one I mentioned above occurs after I went from the INSERT INTO method to the rs.AddNew.
0
 
fritz_the_blankCommented:
Any luck if you change it like this?

strDataPath = Server.MapPath("ProActive.mdb")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
            & " Data Source= " & strDataPath & ";"_
            & " Mode=Share Deny None;User Id=admin;PASSWORD=;"


set Conn=Server.CreateObject("ADODB.Connection")
Conn.ConnectionTimeout = 15
Conn.CommandTimeout =  10
Conn.Mode = 3 'adModeReadWrite
if Conn.state = 0 then
      Conn.Open strConnectString
end if
  set rs=Server.CreateObject("ADODB.recordset")
  sql = "SELECT * FROM tblMain WHERE TxtNum = " & txtNumber
  rs.Open sql, conn,3,3
0
 
acaracioloAuthor Commented:
Thanks Fritz... I guess it had to do with how I was opening the data base....
0
 
fritz_the_blankCommented:
Glad to have helped,

FtB
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now