Using C# and OleDB to connect to an Access database.
I have three almost identical query strings:
"select * from stock_head where sh_id = 19;"
"select * from stock_head where sh_lineno = 32059";
"select * from stock_head where sh_ref='#007705';";
The first and second retrieve the row and the field in each case is an integer, the last one doesn't, it's a char field and the row does exist:
ExecuteQuery - select * from stock_head where sh_lineno = 32059
ID 7705, #007705, 32059, NS, 'NO SALE', 04/02/2017 14:29:00
1 row(s) found
ExecuteQuery - select * from stock_head where sh_ref='#007705';
0 row(s) found
Is there something weird about queries on character fields via C# and OleDB? I've tried using 'like' instead of '=', and single and double quotes to delimit the value, all to no avail.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
namespace OleDbTest
{
class Program
{
static void Main( string[] args )
{
// Create Profile File object
ProcessEJFile EJP = new ProcessEJFile(
"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data Source=E:\Users\sallyw\Documents\Bar.accdb;" +
"Persist Security Info=False;");
//// Get last reference from the Stock Header file
//object retVal = EJP.ExecuteScalar(
// @"SELECT max(stock_head.[sh_ref]) FROM stock_head where sh_ref like ""[#]%"";", null);
//string maxRef = retVal.ToString();
//Console.WriteLine( "maxRef = {0}", maxRef );
// Get the actual row
string query =
// @"select * from stock_head where sh_ref = '{0}';";
//"select * from stock_head where sh_id = 19;";
"select * from stock_head where sh_lineno = 32059";
List<StockHead> shlist = EJP.GetStockHead(query, null );
if ( shlist == null )
{
Console.WriteLine( "shlist is null" );
}
else
{
foreach (StockHead sh in shlist )
{
Console.WriteLine( sh.ToString() );
}
Console.WriteLine( "{0} row(s) found", shlist.Count());
}
query =
// @"select * from stock_head where sh_ref = '{0}';";
"select * from stock_head where sh_ref='#007705';";
List<StockHead> shlist1 = EJP.GetStockHead(query, null );
if ( shlist1 == null )
{
Console.WriteLine( "shlist1 is null" );
}
else
{
foreach ( StockHead sh in shlist1 )
{
Console.WriteLine( sh.ToString() );
}
Console.WriteLine( "{0} row(s) found", shlist1.Count() );
}
Console.ReadLine();
}
}
class ProcessEJFile
{
AccessDatabase Accdb = null;
public ProcessEJFile( string connectionString )
{
Accdb = new AccessDatabase( connectionString );
}
public List<StockHead> GetStockHead( string sql, params object[] args )
{
DataTable t;
Accdb.ExecuteQuery( out t, sql, args );
if ( t != null )
{
List<StockHead> shlist = new List<StockHead>();
foreach ( DataRow r in t.Rows )
{
StockHead sh = new StockHead( r);
shlist.Add( sh );
}
return shlist;
}
else
{
return null;
}
}
// Get a single value - MAX, COUNT etc.
public Object ExecuteScalar( string sql, params object[] args )
{
return Accdb.ExecuteScalar( sql, args );
}
}
class AccessDatabase
{
public OleDbConnection conn = new OleDbConnection();
public AccessDatabase( string connection )
{
conn.ConnectionString = connection;
}
public bool OpenDatabase()
{
try
{
conn.Open();
}
catch ( Exception ex )
{
return false;
}
return true;
}
public void CloseDatabase()
{
if ( conn == null )
return;
conn.Close();
}
public void ExecuteQuery( out DataTable dataTable, string sql, params object[] args )
{
dataTable = new DataTable();
string query;
// Simplified version not validating or cleaning arguments in any way
if ( args == null )
{
query = sql;
}
else
{
query = string.Format( sql, args );
}
Console.WriteLine( "\nExecuteQuery - {0}", query );
if ( OpenDatabase() )
{
OleDbCommand command = new OleDbCommand( query, conn );
OleDbDataAdapter adapter = new OleDbDataAdapter( command );
adapter.Fill( dataTable );
}
}
public object ExecuteScalar( string sql, params object[] args )
{
Object returnValue = null;
string query = sql;
if ( OpenDatabase() )
{
OleDbCommand cmd = new OleDbCommand( query, (OleDbConnection)conn);
returnValue = cmd.ExecuteScalar();
}
return returnValue;
}
}
class StockHead
{
public int sh_id;
public string sh_ref;
public int sh_lineno = 0;
public string sh_type;
public string sh_supplier = "";
public DateTime sh_datetime;
public StockHead( DataRow row )
{
this.sh_id = (int)row[ "sh_id" ];
this.sh_ref = (string)row[ "sh_ref" ];
if ( !string.IsNullOrEmpty( row[ "sh_lineno" ].ToString() ) )
{
this.sh_lineno = (int)row[ "sh_lineno" ];
}
this.sh_type = (string)row[ "sh_type" ];
if ( !string.IsNullOrEmpty( row[ "sh_lineno" ].ToString() ) )
{
this.sh_supplier = (string)row[ "sh_supplier" ];
}
this.sh_datetime = (DateTime)row[ "sh_datetime" ];
}
public override string ToString()
{
return String.Format( "ID {0}, {1}, {2}, {3}, '{4}', {5}",
this.sh_id, this.sh_ref, this.sh_lineno, this.sh_type, this.sh_supplier, this.sh_datetime );
}
}
}`
There's nothing wrong with your query. This is failing because you try to open the connection to the database each time you perform the query by calling OpenDatabase in ExecuteQuery. The second time the OpenDatabase fails as the database is already open and the query is not executed.
You are trapping the error but doing nothing with it. The message given is...
The connection was not closed. The connection's current state is open.
This can easily be found by stepping through your code. Did you not run it in debug?
You can change it to..
public bool OpenDatabase()
{
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
return true;
}
If you trap an error then do something with it other than returning False