?
Solved

Not getting expected error!

Posted on 2004-08-04
8
Medium Priority
?
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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
 

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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 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