Reading DateTime table from mdb file with C#

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jr1024 - 09 Jul 2007 10:48 GMT

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.

Reply to this Message

Jon Skeet [C# MVP] - 09 Jul 2007 11:11 GMT

> Hi everybody!
>
[quoted text clipped - 11 lines]
> 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

Reply to this Message

Ignacio Machin ( .NET/ C# MVP ) - 09 Jul 2007 14:59 GMT

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.

Reply to this Message

jr1024 - 10 Jul 2007 08:08 GMT

On Jul 9, 4:59 pm, "Ignacio Machin \( .NET/ C# MVP \)" <machin TA
laceupsolutions.com> wrote:

> Hi,
>
[quoted text clipped - 18 lines]
>
> 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.

Reply to this Message

Ignacio Machin ( .NET/ C# MVP ) - 10 Jul 2007 15:21 GMT

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:
[quoted text clipped - 10 lines]
> I will take a look at OleDbParameter later today.
> Thank you for your time.

What error you are getting?

Reply to this Message

jr1024 - 11 Jul 2007 07:48 GMT

On Jul 10, 5:21 pm, "Ignacio Machin \( .NET/ C# MVP \)" <machin TA
laceupsolutions.com> wrote:

> > But I also want to update the Date fields in the mdb file.
> > And when I do so with:
[quoted text clipped - 12 lines]
>
> 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?

Reply to this Message

Ignacio Machin ( .NET/ C# MVP ) - 11 Jul 2007 15:27 GMT

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

> On Jul 10, 5:21 pm, "Ignacio Machin \( .NET/ C# MVP \)" <machin TA
> laceupsolutions.com> wrote:
[quoted text clipped - 66 lines]
>
> What am I doing wrong?

Reply to this Message

Reading DateTime table from mdb file with C#

Free Magazines