Reading DateTime table from mdb file with C#

[jr1024]

Hi everybody!


I have a C# app that connects to a mdb file and displays all the
fields from a table
in dataGridView. I have trouble searching in a Access Date/Time field
like this.

string searchDate = "12/11/2007";
Result = DateTime.Parse(Result,
CultureInfo.CurrentCulture).ToShortDateString();

searchDate = "'" + searchDate + "'";

strSQL = "SELECT * FROM TableName WHERE Date=" +
searchDate;


OleDbDataAdapter da = new OleDbDataAdapter(strSQL,
this.conn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);

DataSet ds = new DataSet();
da.Fill(ds, "TableName");

I recieve the following error at run-time:
"Data type mismatch in criteria expression"

I guess the problem has something to do with Regional Settengs.

 

[Jon Skeet [C# MVP]]

Hi everybody!

I have a C# app that connects to a mdb file and displays all the
fields from a table
in dataGridView. I have trouble searching in a Access Date/Time field
like this.

string searchDate = "12/11/2007";
Result = DateTime.Parse(Result,
CultureInfo.CurrentCulture).ToShortDateString();

searchDate = "'" + searchDate + "'";

strSQL = "SELECT * FROM TableName WHERE Date=" +
searchDate;

You should use a parameterised query instead of inserting the date
directly into the SQL.
See the docs for OleDbParameter for an example.

Jon

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Hi everybody!


I have a C# app that connects to a mdb file and displays all the
fields from a table
in dataGridView. I have trouble searching in a Access Date/Time field
like this.

Do this, put a breakpoint right after this line, and see what it contains:

strSQL = "SELECT * FROM TableName WHERE Date=" +
searchDate;

will be like
SELECT * FROM TableName WHERE Date= 4/2/2007

So basically you are making a division

You have to use a delimiter, IIRC Access use # to delimite dates.

But the CORRECT solution is using a parameter as Skeet suggest.

 

[jr1024]

Hi,





Do this, put a breakpoint right after this line, and see what it contains:

strSQL = "SELECT * FROM TableName WHERE Date=" +
searchDate;

will be like
SELECT * FROM TableName WHERE Date= 4/2/2007

So basically you are making a division

You have to use a delimiter, IIRC Access use # to delimite dates.

But the CORRECT solution is using a parameter as Skeet suggest.

Thank you all for replying.
I was able to get the select query to work like that:

strDateSearch = "#" + dt.Month.ToString() + "/"
+ dt.Day.ToString() + "/" +
dt.Year.ToString() + "#";


But I also want to update the Date fields in the mdb file.
And when I do so with:

OleDbDataAdapter da = ...;
DataSet ds = new DataSet();
da.Fill(ds, "TableName");
//...
da.Update(ds, "TableName");


I get Update syntax error. The same code updates string and number
fields no problem.

I will take a look at OleDbParameter later today.
Thank you for your time.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Thank you all for replying.
I was able to get the select query to work like that:

strDateSearch = "#" + dt.Month.ToString() + "/"
+ dt.Day.ToString() + "/" +
dt.Year.ToString() + "#";

That might solve your problem, but it's not the best solution, use a
Parameterized query instead

But I also want to update the Date fields in the mdb file.
And when I do so with:

OleDbDataAdapter da = ...;
DataSet ds = new DataSet();
da.Fill(ds, "TableName");
//...
da.Update(ds, "TableName");


I get Update syntax error. The same code updates string and number
fields no problem.

I will take a look at OleDbParameter later today.
Thank you for your time.

What error you are getting?

 

[jr1024]

What error you are getting?

I get unhandled exception: "Syntax error in UPDATE statement"

And I get same error even when I try do it like that:



private void dataGrid_CellEndEdit(object sender,
DataGridViewCellEventArgs e)
{
DataGridViewCell theCell = dataGrid[e.ColumnIndex,
e.RowIndex];
DataGridViewCell ID_Cell = dataGrid[e.ColumnIndex-1,
e.RowIndex];

if (theCell.ValueType == typeof(DateTime))
{
if (conn != null)
conn.Close();
conn = new OleDbConnection(strConn);

DateTime dt = (DateTime)theCell.Value;


string updateQ = "UPDATE TableName SET Date=@p1 WHERE
ID=@p2";

conn.Open();
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = updateQ;


OleDbParameter p1 = new OleDbParameter("@p1",
OleDbType.DBDate);
p1.Value = dt; //dt.ToOADate();
cmd.Parameters.Add(p1);


OleDbParameter p2 = new OleDbParameter("@p2",
OleDbType.Integer);
p2.Value = ID_Cell.Value;
cmd.Parameters.Add(p2);


try
{
cmd.ExecuteNonQuery();
}
catch(System.InvalidOperationException ex)
{
MessageBox.Show(ex.Message, "zzz Invalid Operation
Exception");
}

}



What am I doing wrong?

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,


What if you write the code like "UPDATE TableName SET [Date]=@p1 WHERE
ID=@p2";

I do not remember if DATE is a reserved word

What error you are getting?

I get unhandled exception: "Syntax error in UPDATE statement"

And I get same error even when I try do it like that:



private void dataGrid_CellEndEdit(object sender,
DataGridViewCellEventArgs e)
{
DataGridViewCell theCell = dataGrid[e.ColumnIndex,
e.RowIndex];
DataGridViewCell ID_Cell = dataGrid[e.ColumnIndex-1,
e.RowIndex];

if (theCell.ValueType == typeof(DateTime))
{
if (conn != null)
conn.Close();
conn = new OleDbConnection(strConn);

DateTime dt = (DateTime)theCell.Value;


string updateQ = "UPDATE TableName SET Date=@p1 WHERE
ID=@p2";

conn.Open();
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = updateQ;


OleDbParameter p1 = new OleDbParameter("@p1",
OleDbType.DBDate);
p1.Value = dt; //dt.ToOADate();
cmd.Parameters.Add(p1);


OleDbParameter p2 = new OleDbParameter("@p2",
OleDbType.Integer);
p2.Value = ID_Cell.Value;
cmd.Parameters.Add(p2);


try
{
cmd.ExecuteNonQuery();
}
catch(System.InvalidOperationException ex)
{
MessageBox.Show(ex.Message, "zzz Invalid Operation
Exception");
}

}



What am I doing wrong?