Hello,

I have an application that is written in MS Visual Studio 2005 C#. It uses an ODBC
connection to access the DBISam database. I am able to query the database tables and do
simple select statements, but if I try to use ORDER BY, I get an error. I tried turning
off Anti-Virus which you suggested to me in an email. I am still getting the same error. I
have also tried this on another computer and get the same error.

Here is my code.

public int Test()
       {            
           int ExceptionID;
           OdbcCommand Transaction = new OdbcCommand();
           OdbcDataReader Reader;

           OdbcDataReader myReader = null;
           Transaction.Connection = dbisam;
           Transaction.CommandText = "SELECT ExceptionID FROM Exceptions ORDER BY
ExceptionID DESC";
           try
           {
               dbisam.Open();
               myReader = Transaction.ExecuteReader();
               myReader.Read();
               ExceptionID = (int)myReader[0];
               dbisam.Close();
           }

           catch (Exception f)
           {
               ExceptionID = -1;
               dbisam.Close();
           }
           return ExceptionID;
       }

Hi Ian,

Have you set a value for "PrivateDir" in your connection string for the
dbisam OdbcConnection object?

I suspect that without the ORDER BY clause the SELECT returns a live
result set so no temporary table is created, but when you try and ORDER
the results a temporary table is created but you do not have access
rights to create the table.

Also you should have some extra code at the end of your try.. catch
block like this:

try
{
.....
}
catch(Exception f)
{
....
}
finally
{
  if(Transaction != null)
    Transaction.Dispose();
}


Chris Holland
SEC Solutions Ltd.


Ian O'Loughlin wrote:
> Hello,
>
> I have an application that is written in MS Visual Studio 2005 C#. It uses an ODBC
> connection to access the DBISam database. I am able to query the database tables and do
> simple select statements, but if I try to use ORDER BY, I get an error. I tried turning
> off Anti-Virus which you suggested to me in an email. I am still getting the same error. I
> have also tried this on another computer and get the same error.
>
> Here is my code.
>
> public int Test()
>         {            
>             int ExceptionID;
>             OdbcCommand Transaction = new OdbcCommand();
>             OdbcDataReader Reader;
>
>             OdbcDataReader myReader = null;
>             Transaction.Connection = dbisam;
>             Transaction.CommandText = "SELECT ExceptionID FROM Exceptions ORDER BY
> ExceptionID DESC";
>             try
>             {
>                 dbisam.Open();
>                 myReader = Transaction.ExecuteReader();
>                 myReader.Read();
>                 ExceptionID = (int)myReader[0];
>                 dbisam.Close();
>             }
>
>             catch (Exception f)
>             {
>                 ExceptionID = -1;
>                 dbisam.Close();
>             }
>             return ExceptionID;
>         }
>

Ian,

Actually the name to use in the connection string is "PrivateDirectory"

Chris

Chris,

I have the private directory in my connection string and am still getting the error.

Dsn=JobPodDB;
Driver={C:\dbisam\odbc\std\ver4\lib\dbodbc\dbodbc.dll};
connectiontype=Local;
remoteipaddress=127.0.0.1;
remoteport=12005;
remotecompression=0;
remotereadahead=50;
catalogname=C:\Program Files\OEMTransport\Database;
readonly=False;
lockretrycount=15;
lockwaittime=100;
forcebufferflush=True;
strictchangedetection=False;
privatedirectory=C:\DOCUME~1\IOLOUG~1.OEM\LOCALS~1\Temp\

Hi Ian,

Have you tried setting the "PrivateDirectory" to a shorter path?

I have seen some strange thing where the path name is to long.

Chris Holland

Ian O'Loughlin wrote:
> Chris,
>
> I have the private directory in my connection string and am still getting the error.
>
> Dsn=JobPodDB;
> Driver={C:\dbisam\odbc\std\ver4\lib\dbodbc\dbodbc.dll};
> connectiontype=Local;
> remoteipaddress=127.0.0.1;
> remoteport=12005;
> remotecompression=0;
> remotereadahead=50;
> catalogname=C:\Program Files\OEMTransport\Database;
> readonly=False;
> lockretrycount=15;
> lockwaittime=100;
> forcebufferflush=True;
> strictchangedetection=False;
> privatedirectory=C:\DOCUME~1\IOLOUG~1.OEM\LOCALS~1\Temp\
>
>

Chris,
I figured out the problem. I don't know why it works but I changed the connectionstring to
the following:

dbi.DefineConnection("Dsn=JobPodDB;connectiontype=Local;remoteipaddress=127.0.0.1;remoteport=12005;remotecompression=0;remotereadahead=50;readonly=False;lockretrycount=15;lockwaittime=100;forcebufferflush=True;strictchangedetection=False;");

I just took out all references to any paths.

Thanks
Ian

Ian,

<< I just took out all references to any paths. >>

You should be careful when doing this because it can cause temporary tables
to show up in seemingly random locations like the desktop during operation.
It is always recommended that you set the PrivateDirectory connection string
attribute.

Also, please see my email response to you for information on how to fix the
problem with AV software interfering with DBISAM.

--
Tim Young
Elevate Software
www.elevatesoft.com