Solved

Not getting expected error!

Posted on 2004-08-04
8
264 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 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