Backend database: Access (date format: yyyy-mm-dd).
It returns error when I run below code.
Thanks.
public static bool isDateInDatabaseTableAppointment(DateTime dt)
{
OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString);
Cn.Open();
string strSQL = "select count(*) from TableAppointment WHERE AppointmentDate = '" + dt.ToString("yyyy-MM-dd") + "'";
using (OdbcCommand cmd = new OdbcCommand(strSQL, Cn))
{
object obj = cmd.ExecuteScalar();
int count = Convert.ToInt32(obj);
//int count = Convert.ToInt32(cmd.ExecuteScalar());
if (count > 0)
{
return true;
}
else
{
return false;
}
}
}
using (OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString))
{
for (DateTime dt = dateTimePickerBegin.Value; dt <= dateTimePickerEnd.Value; dt = dt.AddDays(1))
{
bool isFound = GlobalVariables.isDateInDatabaseTableAppointment(dt);
if (isFound) // Check if it is in database TableAppointment
{
MessageBox.Show(dt.ToString() + " : you have an appointment today.");
}
} //end of for loop
}// end of using
System.Runtime.InteropServices.SEHException
HResult=0x80004005
Message=External component has thrown an exception.
Source=System.Data
StackTrace:
at System.Data.Common.UnsafeNativeMethods.SQLDriverConnectW(OdbcConnectionHandle hdbc, IntPtr hwnd, String connectionstring, Int16 cbConnectionstring, IntPtr connectionstringout, Int16 cbConnectionstringoutMax, Int16& cbConnectionstringout, Int16 fDriverCompletion)
at System.Data.Odbc.OdbcConnectionHandle.Connect(String connectionString)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
Edit: Above Using/FOR loop are inside ELSE statement, and there is another Cn in ELSE IF statement. Is that an issue?
private void butonSubmit_Click(object sender, EventArgs e)
{
if (......)
{
MessageBox.Show(".....");
}
else if(.....)
{
using (OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString))
{
.....
}
}
else
{
using (OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString))
{
for (DateTime dt = dateTimePickerBegin.Value; dt <= dateTimePickerEnd.Value; dt = dt.AddDays(1))
{
bool isFound = GlobalVariables.isDateInDatabaseTableAppointment(dt);
if (isFound) // Check if it is in database TableAppointment
{
MessageBox.Show(dt.ToString() + " : you have an appointment today.");
}
}
}
}
}