Solved

Not getting expected error!

Posted on 2004-08-04
8
261 Views
Last Modified: 2008-02-01
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
Comment
Question by:acaraciolo
  • 3
  • 3
  • 2
8 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11718789
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
 
LVL 15

Expert Comment

by:joeposter649
ID: 11718855
Looks like you're always going to redirect.  Do a response.end after writing out the error.
0
 
LVL 15

Expert Comment

by:joeposter649
ID: 11718896
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
 

Author Comment

by:acaraciolo
ID: 11720251
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:acaraciolo
ID: 11720314
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
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 500 total points
ID: 11721092
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
 

Author Comment

by:acaraciolo
ID: 11724348
Thanks Fritz... I guess it had to do with how I was opening the data base....
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11725757
Glad to have helped,

FtB
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now