SQL Select statement error

[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

[bruce]

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.


[b]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">

[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

[bruce]

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);

[b]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">

[eurovw]

try to rename @DATE.
I had problem when I used field name "Year" in my access database (I think it's a keyword there). It gave me the same problem. Once I changed "Year" to "cYear", everything went fine.