3 Replies Latest reply on Jan 28, 2008 2:03 PM by burleigh

    Right way to connect, submit some SQL, and disconnect using ASP?

    burleigh
    burleigh Jan 18, 2008 7:42 PM
      I thought I knew how to do this but, with one, low-use web form, we're occasionally losing form submissions and I haven't been able to understand why.

      The form's processor is in a JSCRIPT "class" so I'll pull out the relevant bits below.

      I was just able to learn from a user that he saw the "cannot save" message "thrown" when the test for this.aConnection.State!=1 fails.

      No other place in the application presents that error, so I'd guess that's the problem. Thing is, how can a connect to the database occasionally fail when the database (11g on windows 2003) hasn't been down for weeks? The DB is in "shared" mode with I believe one dispatcher.

      Hints would sure be appreciated.

      this.aConnection=null;

      var sConnect="Provider=OraOLEDB.Oracle;Data Source=<>;User Id=<>;Password=<>";

      this.aConnection=Server.CreateObject( "ADODB.Connection" );
      if( !this.aConnection )
      throw "The application cannot access information.";

      this.aConnection.Open( sConnect );
      if( this.aConnection.State!=1 )
      throw "The application cannot save your submission.";

      this.aConnection.Execute( <DDL to insert>, 0, 1 );
                     
      this.aConnection.Close();
      • 2635 Views
      • Tags:
        • 1. Re: Right way to connect, submit some SQL, and disconnect using ASP?
          burleigh
          burleigh Jan 26, 2008 10:11 PM (in response to burleigh)
          I've concluded that the State property of a Connection object isn't reliable. The moment I added the test:

          if( this.aConnection.State!=1 )
          throw "The application cannot save your submission.";

          we began to have problems with users of a our web app seeing the thrown message even though the database instance shows no evidence of connection trouble. I toyed with tracing OraOLEDB but never got that to work, and an Oracle support person suggested that type of tracing isn't reliable anyway. Finally, buried in some MS support document I saw the claim that a connection isn't actually opened until a recordset is returned.

          That suggests that the real test for "connected" is whether your app can do the work you intend it to do: in other words, "try."
          • 2. Re: Right way to connect, submit some SQL, and disconnect using ASP?
            502182
            502182 Jan 28, 2008 5:19 AM (in response to burleigh)
            Can you please provide the link to the MS support document if possible?
            • 3. Re: Right way to connect, submit some SQL, and disconnect using ASP?
              burleigh
              burleigh Jan 28, 2008 2:03 PM (in response to 502182)
              The State property is a sort of enum so, strictly speaking, the test for !=1 is too simple.

              The MS documentation for the Open method of the Connection object is here:
              http://msdn2.microsoft.com/en-us/library/ms676505(VS.85).aspx

              In a note they say:

              "Remote Data Service Usage When used on a client-side Connection object, the Open method doesn't actually establish a connection to the server until a Recordset is opened on the Connection object."