2 Replies Latest reply on May 13, 2008 5:16 AM by bre358

    SQL statement is not finding my table

    bre358
      I've started implementing the bulk of the database logic in our current software project, and while trying to get the select statement to work in populating the datagrid in one window upon creation, I keep running into this error every time the SQL statement tries to execute. It keeps saying that the table I'm trying to get the data from doesn't exit, when it clearly does in the database the application creates in the event it doesn't already exist. I've checked the spelling, and fixed my select statement, but it still says that the table doesn't exist. Here is the code I'm using:

      //Functions to load the proper window depending on what window is open

      //Imported Stuff
      import MatMgr.DataManager;

      import flash.data.SQLConnection;
      import flash.data.SQLResult;
      import flash.data.SQLStatement;
      import flash.display.NativeWindow;
      import flash.events.SQLErrorEvent;
      import flash.events.SQLEvent;

      //Code to load either the new Repair Order or new Purchase Order window

      //Declared variables used in more than just one function
      private var dataManager:DataManager = new DataManager();
      private var loadRO:Repair_Order = new Repair_Order();
      private var loadPO:PurchaseOrder = new PurchaseOrder();
      private var sqlText:String;
      private var selectStatement:SQLStatement;
      private var conn:SQLConnection;

      //Form Initialization
      private function init():void
      {
      var appWindow:NativeWindow = this.stage.nativeWindow;
      var winTitle:String = appWindow.title;
      //Alert.show(winTitle, "The title of the window is...");
      switch(winTitle)
      {
      case "Repair Orders": setSelectData(winTitle);
      break;
      case "Purchase Orders": setSelectData(winTitle);
      break;
      }
      }
      //Select functions to populate the datagrid
      private function setSelectData(windowTitle:String):void
      {
      var selectedROHeaderColumns:String = "ROID, BillID, RODate, Status";
      var selectedPOHeaderColumns:String = "POID, PODate, TechID, Status";

      switch(windowTitle)
      {
      case "Repair Orders": sqlText = "SELECT " + selectedROHeaderColumns + " FROM Up18ROHeader";
      executeSQL(sqlText);
      break;
      case "Purchase Orders": sqlText = "SELECT " + selectedPOHeaderColumns + " FROM Up18POHeader";
      executeSQL(sqlText);
      break;
      }
      }
      private function executeSQL(selectSQL:String):void
      {
      selectStatement = new SQLStatement();
      conn = new SQLConnection();
      selectStatement.sqlConnection = conn;
      conn.open();
      selectStatement.addEventListener(SQLEvent.RESULT, selectResult);
      selectStatement.addEventListener(SQLErrorEvent.ERROR, dataManager.errorHandler);
      selectStatement.text = selectSQL;
      selectStatement.execute();
      }
      private function selectResult(event:SQLEvent):void
      {
      var result:SQLResult = selectStatement.getResult();
      dgOrders.dataProvider = result.data;
      }
        • 1. Re: SQL statement is not finding my table
          enorton@adobe
          Hi,

          I notice in your executeSQL function that you are creating a new SQLConnection. You are not specifying a database reference in the .open() call. Is this also how you are creating the database?

          -Erica
          • 2. Re: SQL statement is not finding my table
            bre358 Level 1
            Well I had to create a new connection when it kept throwing the error that there was no connection. This is not all of my database logic, the rest of it, such as the creation of the database and tables, as well as all of the related insert statements. How the database was created was this:

            public function openDatabase():void
            {
            conn = new SQLConnection();
            conn.addEventListener(SQLErrorEvent.ERROR, errorHandler);
            var dbFile:File = File.applicationStorageDirectory.resolvePath("MatMgr.db");
            if(dbFile.exists)
            {
            conn.addEventListener(SQLEvent.OPEN, openHandler);
            }
            else
            {
            conn.addEventListener(SQLEvent.OPEN, createHandler);
            }
            conn.openAsync(dbFile);
            }

            Essentially if it doesn't already exist, it creates a new one along with the tables whose logic I left out. While typing this, I experimented and discovered how to fix my problem by doing this:

            private function executeSQL(selectSQL:String):void
            {
            var dbFile:File = File.applicationStorageDirectory.resolvePath("MatMgr.db");
            selectStatement = new SQLStatement();
            conn = new SQLConnection();
            selectStatement.sqlConnection = conn;
            conn.open( dbFile);
            selectStatement.addEventListener(SQLEvent.RESULT, selectResult);
            selectStatement.addEventListener(SQLErrorEvent.ERROR, dataManager.errorHandler);
            selectStatement.text = selectSQL;
            selectStatement.execute();
            }

            Though nothing appeared in the datagrid, no errors were thrown, and the fact that nothing was in the datagrid can be attributed to nothing being in the table in question at the time of this post. Thanks for your help, it's what inspired me to make the needed code change.