SQL Select statement error

What is the data type for the CreateDateTime? You may need to convert it to Date type.

Post the code snippet, it'll be easier for people to help you.


quote:Originally posted by scowley

I am getting a strange error with a select statement to my access database. The statement works fine on my local machine but not online. The error that I am getting is:
[OleDbException (0x80040e07): Data type mismatch in criteria expression.]

The select statement is:
SELECT Anno_Societatis FROM Years_of_the_SCA WHERE Beginning_date<=@DATE AND Ending_date>=@DATE

@DATE is a DateTime that is passed in to the function. It originates from a call to get the CreationDate for a file.

If I remove the where part of the statement everything works fine, I just get the wrong data from the table. Both Beginning_date and Ending_date are DateTime columns in the table.

Is the file creation DateTime a different format somehow?



Steven Cowley
</blockquote id="quote"></font id="quote">

 

Hmmm... i am not a C# guy, i am not sure if this will work, but you can give it a shot

("SELECT Anno_Societatis " +
"FROM Years_of_the_SCA " +
"WHERE Beginning_date<='" + d +
"' AND Ending_date>='" + d + "'",
connection);

quote:Originally posted by scowley

The code snipit for the problem is thus:

private void Page_Load(object sender, System.EventArgs e)
{
String fn = @"\fpdb\Precedence_for_Atenveldt.mdb";
DateTime info = File.GetCreationTime(PathClass.getRealPath(fn));
String s = info.ToLongDateString();
String[] str = s.Split(new Char[] {',',' '});
lblModified.Text = " (as of " + str+ " " + str + ", A.S. " + DBHonors.getScaYear(info) + ", being " + str + " in the common reckoning)";
}


the problem is in the call to DBHonors.getScaYear(info). Here is the code for that:

public static String getScaYear(DateTime d)
{
// Make a connection to the access database
OleDbConnection connection = new OleDbConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

// Create a SQL command to execute in the database
OleDbCommand cmd = new OleDbCommand("SELECT Anno_Societatis " +
"FROM Years_of_the_SCA " +
"WHERE Beginning_date<=@DATE " +
"AND Ending_date>=@DATE",
connection);
cmd.Parameters.Add("@DATE", d);

// Open up the database connection
cmd.Connection.Open();

// Execute our SQL query and store the results
OleDbDataReader results = cmd.ExecuteReader();

Any help would be appreciated



Steven Cowley
</blockquote id="quote"></font id="quote">