Hi Folks - sorry to ask such apparently simple question, but here it is:
I have an Access db table with key 'resvID' autonumber and 'resvDate' as 
DateTime.  Using c# and oledb, I try to so a simple SQL call to extract 
records based on a date that's passed in, but no matter what format I try, 
I get a 'DataType mismatch during the .Fill method.  Can anyone shed light?

Thanks so much, jc

code
...
//create adaptor to get resv data for 'date'
	sDate = date.Date.ToShortDateString();
	sql = "select * from reservations where resvDate ='03/18/2003'";
	daResv = new System.Data.OleDb.OleDbDataAdapter( sql, conn);
//add to existing dataset
	daResv.Fill(dsResv, "Reservations");

note: at first i had - 
sDate = date.Date.ToShortDateString();
sql = "select * from reservations where resvDate ='" + sDate + "'";

then I hard coded the sql (and also tried ...='20030318' ...='03182003') 
all with the same date type mismatch problem.

John,

For access, you need to use the # instead of '

Example:
"select * from reservations where resvDate =#03/18/2003#"

Hope that helps!

Sean

-----Original Message-----
From: John A Conte [mailto:jconte8@y...]
Sent: Tuesday, March 18, 2003 8:06 PM
To: ADO.NET
Subject: [ado_dotnet] Trivial SQL ? Data Type mismatch error on DateTime
Field


Hi Folks - sorry to ask such apparently simple question, but here it is:
I have an Access db table with key 'resvID' autonumber and 'resvDate' as
DateTime.  Using c# and oledb, I try to so a simple SQL call to extract
records based on a date that's passed in, but no matter what format I try,
I get a 'DataType mismatch during the .Fill method.  Can anyone shed light?

Thanks so much, jc

code
...
//create adaptor to get resv data for 'date'
	sDate = date.Date.ToShortDateString();
	sql = "select * from reservations where resvDate ='03/18/2003'";
	daResv = new System.Data.OleDb.OleDbDataAdapter( sql, conn);
//add to existing dataset
	daResv.Fill(dsResv, "Reservations");

note: at first i had -
sDate = date.Date.ToShortDateString();
sql = "select * from reservations where resvDate ='" + sDate + "'";

then I hard coded the sql (and also tried ...='20030318' ...='03182003')
all with the same date type mismatch problem.
===
Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using ADO.NET
in your applications. The book covers DataSets and Typed DataSets, accessing
data using DataReaders and DataAdaptors, the close relationship between
ADO.NET and XML, how and where to use ADO.NET in your enterprise
applications, and how to use Web Services and ADO.NET to easily pass data
between applications.
http://www.wrox.com/books/1861007604.htm

Thanks Sean!  But it does shatter my illusion of being able to eventually 
use SQLServer without having to re-write code.

John,

Yes, it's an unfortunate accessism :-)

What I'd recommend is creating a method that will return the appropriate
character, and then have a static variable for your app that declares if
it's Access or Sql Server.

Example:

public class DataUtil{

	public static bool IsAccess = true;

	public static string GetChar(){
		if(DataUtil.IsAccess){
			return "#";
		}else{
			return "'";
		}
	}
}

public class Test{

	public void Test(){
		string sql = "SELECT * FROM Table WHERE dateColumn = " + Test.GetChar() +
"3/13/2002" + Test.GetChar();
		Console.WriteLine(sql);
		// sql should look like: SELECT * FROM Table WHERE dateColumn 
#3/13/2002#

		DataUtil.IsAccess = false;
		string sql2 = "SELECT * FROM Table WHERE dateColumn = " + Test.GetChar() +
"3/13/2002" + Test.GetChar();
		Console.WriteLine(sql2);
		// sql2 should look like: SELECT * FROM Table WHERE dateColumn 
'3/13/2002'

	}
}

Not ideal, but more portable than having to rewrite everything for each
app...

Have a great day!

Sean

-----Original Message-----
From: John A Conte [mailto:jconte8@y...]
Sent: Tuesday, March 18, 2003 9:24 PM
To: ADO.NET
Subject: [ado_dotnet] RE: Trivial SQL ? Data Type mismatch error on
DateTime Field


Thanks Sean!  But it does shatter my illusion of being able to eventually
use SQLServer without having to re-write code.
===
Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using ADO.NET
in your applications. The book covers DataSets and Typed DataSets, accessing
data using DataReaders and DataAdaptors, the close relationship between
ADO.NET and XML, how and where to use ADO.NET in your enterprise
applications, and how to use Web Services and ADO.NET to easily pass data
between applications.
http://www.wrox.com/books/1861007604.htm

FYI, small mistake...In the test class, the call should be to 
DataUtil.GetChar(), not Test.GetChar().  I never promised that it 
worked. ;0)

Sean


> John,

Yes, it's an unfortunate accessism :-)

What I'd recommend is creating a method that will return the appropriate
character, and then have a static variable for your app that declares if
it's Access or Sql Server.

Example:

public class DataUtil{

	public static bool IsAccess = true;

	public static string GetChar(){
		if(DataUtil.IsAccess){
			return "#";
		}else{
			return "'";
		}
	}
}

public class Test{

	public void Test(){
		string sql = "SELECT * FROM Table WHERE dateColumn = " + 
Test.GetChar() +
"3/13/2002" + Test.GetChar();
		Console.WriteLine(sql);
		// sql should look like: SELECT * FROM Table WHERE 
dateColumn 
#3/13/2002#

		DataUtil.IsAccess = false;
		string sql2 = "SELECT * FROM Table WHERE dateColumn = " + 
Test.GetChar() +
"3/13/2002" + Test.GetChar();
		Console.WriteLine(sql2);
		// sql2 should look like: SELECT * FROM Table WHERE 
dateColumn 
'3/13/2002'

	}
}

Not ideal, but more portable than having to rewrite everything for each
app...

Have a great day!

Sean

-----Original Message-----
From: John A Conte [mailto:jconte8@y...]
Sent: Tuesday, March 18, 2003 9:24 PM
To: ADO.NET
Subject: [ado_dotnet] RE: Trivial SQL ? Data Type mismatch error on
DateTime Field


Thanks Sean!  But it does shatter my illusion of being able to eventually
use SQLServer without having to re-write code.
===
Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using 
ADO.NET
in your applications. The book covers DataSets and Typed DataSets, 
accessing
data using DataReaders and DataAdaptors, the close relationship between
ADO.NET and XML, how and where to use ADO.NET in your enterprise
applications, and how to use Web Services and ADO.NET to easily pass data
between applications.
http://www.wrox.com/books/1861007604.htm

Sean - thanks again for all the feedback.  You've gone way beyond the call 
of duty (so to speak) :)