cannot execute query in Acess Database from Window form

Question

0.00/5 (No votes)

See more:

I had a table jobmaster table with a field shipment date(date/time type in shorttime format) and in a windows form I want to call all data from the table where shipment date = value from a data picker.

I tried a lot with it.
(in access database )

C#

shipmentdate =Date/Time; format=Short Date
///////////////////////
in c# coding
<pre>class jobcodedatabean{
dateTime shipmentdate;
//also have property
}

///////////////////////////
datetime picker
dtpshipmentdate;
format=short;
assigning

C#

jobcodedatabean.shipmentdate=dtpshipmentdate.value;
insert into jobmastertable (shipment date)values('"jobcodedatabean.shipmentdate"')

Now in my function i want to select all data from table
i used

C#

DateTime time = dtpfromDate.Value;
 String query = "Select jobcode,companyName,vehicleno,shipmentdate,totalamount,advance,drivername,fromPlace,destination from jobmastertable  where (shipmentdate=" + @shipmentdate + ")"; 
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
dAdapter.SelectCommand.Parameters.AddWithValue("@shipmentdate", time);
DataSet ds = new DataSet();
 dAdapter.Fill(ds, "tbljobmastertable");
tblpaymentview.DataSource = ds.Tables["tblpaymentview"].DefaultView;

Help me. This returns nothing .

Posted 2-Feb-11 20:23pm

SREENATH GANGA

Updated 19-Feb-13 22:59pm

v4

Add a Solution

2 solutions

Solution 1

DateTime includes hours, minutes, seconds and even milliseconds so it is unlikely that the records you are looking for will match to the millisecond. So you need to find records that match anytime during the selected day. Assuming the datepicker is returning a value which represents midnight on a given day. You will want to find all records greater than the date but less than the date plus one day.

String query = "Select... where (shipmentdate => @startdate AND shipmentdate =< @endDate)";
dAdapter.SelectCommand.Parameters.AddWithValue("@startdate", time);
dAdapter.SelectCommand.Parameters.AddWithValue("@endDate", time.AddDays(1));

Posted 2-Feb-11 21:01pm

RobCroll

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

SREENATH GANGA · Accepted Answer · 2013-02-19T23:00:00

Solution 2

CHANGED THE PARAMETER VALUE TIME FROM

DateTime time = dtpfromDate.Value;

TO

DateTime time = dtpfromDate.Value.DATE;

Posted 19-Feb-13 23:00pm

SREENATH GANGA