Query returns empty data set or exception

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).
In a window form I want to call all data from the table where shipment date = value from a data picker.

I tried a lot with it.

The code is:

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;

Posted 1-Feb-11 19:33pm

SREENATH GANGA

Updated 2-Feb-11 22:29pm

Dalek Dave

v6

Add a Solution

Comments

Vigneshb6 2-Feb-11 1:49am

Where in database it saves in mm//dd//yyyy but from the textbox you will get dd//mm/yyyy i think so convert the datetime in dd//mm//yy like-SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy

SREENATH GANGA 2-Feb-11 2:42am

DateTime time = dtpfromDate.Value;
String timedsate = time.ToShortDateString();
MessageBox.Show(time.ToString());
OleDbConnection oledbconnection=new OleDbConnection(connString);
oledbconnection.Open();
String query = "Select jobcode,companyName,vehicleno,shipmentdate,totalamount,advance,drivername,fromPlace,destination from jobmastertable where (shipmentdate=@shipmentdate)";
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query,oledbconnection);
dAdapter.SelectCommand.Parameters.AddWithValue("@shipmentdate", time);

DataSet ds = new DataSet();
dAdapter.Fill(ds,"tbljobmastertable");
tblpaymentview.DataSource = ds.Tables["tbljobmastertable"].DefaultView;

this worked without exception but no output

OriginalGriff 2-Feb-11 3:10am

What exception are you getting?
Any inner exception detail?

Sandeep Mewara 2-Feb-11 4:50am

What exception?

SREENATH GANGA 2-Feb-11 6:22am

type mismatch criteria exception

SREENATH GANGA 2-Feb-11 6:23am

at da adapter.fill()

Malli_S 2-Feb-11 6:33am

Then surely you must cross check the data type of the 'shipmentdate' from the table.

Malli_S 2-Feb-11 6:26am

What exception you get ? Did you try to put single quote around the date in where condition in second code snippet ?

SREENATH GANGA 2-Feb-11 6:53am

i had tried all and the above code shows no error but dataset is null the datatype of shipmentdate is date/time in shortdateformat in acess

Dalek Dave 3-Feb-11 4:29am

Edited for Readability.

3 solutions

Solution 1

The only thing you are missing in both the snippet is the single quote(') before and after the variable.
Below are the updated code snippets:
Snippet 1:

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;

Snippet 2:

public void showjobcodebySearchDate(DateTime fromdate, DateTime todate)
{
  try
  {
    String query = "Select jobcode,companyName,vehicleno,shipmentdate,totalamount,advance,drivername, vehicletype from jobmastertable where shipmentdate >='" + dtpfromDate.Value.ToShortDateString() + "'";
    OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
    DataSet ds = new DataSet();
    dAdapter.Fill(ds, "tbluserdataview");
    tbljobcodeview.DataSource = ds.Tables["tbluserdataview"].DefaultView;
  }
  catch (Exception exp)
  {
    MessageBox.Show("The application had met with some errors please restart  the application :\n error:close MSAccess files");
  }
}

Hope this answer your question

Posted 2-Feb-11 1:18am

Umair Feroze

Comments

SREENATH GANGA 3-Feb-11 1:33am

the first snippet compiles without exception but give empty dataset
the second one show error that the Shipmentdate parameter doesnot exist in cureent contrast when i removed the quates it accept it but returns empty dataset Anyway Thanks for help

Solution 2

C#

DateTime time = dtpfromDate.Value; 
String query = "Select jobcode,companyName,vehicleno,shipmentdate,totalamount,advance,drivername,fromPlace,destination from jobmastertable  where (datepart(dd,shipmentdate)=" + @shipmentdateday + ") and datepart(mm,shipmentdatemonth)="+@shipmentdatemonth+" and datepart(yyyy,shipmentdateyear)="+@shipmentdateyear+"" ; 
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
dAdapter.SelectCommand.Parameters.AddWithValue("@shipmentdatemday", time.Day);
dAdapter.SelectCommand.Parameters.AddWithValue("@shipmentdatemmonth", time.Month);
dAdapter.SelectCommand.Parameters.AddWithValue("@shipmentdatemYear", time.Year);
DataSet ds = new DataSet(); 
dAdapter.Fill(ds, "tbljobmastertable");
tblpaymentview.DataSource = ds.Tables["tblpaymentview"].DefaultView;

you just change your sql query and that works 100% try this

Posted 2-Feb-11 21:09pm

adsonadson

Updated 2-Feb-11 22:30pm

v4

Comments

RaviRanjanKr 3-Feb-11 3:14am

Code is Wrapped in "pre" tags.

SREENATH GANGA 3-Feb-11 7:29am

ya it also works tanku

Add a Solution

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

SREENATH GANGA · Accepted Answer · 2011-02-02T21:29:00

thank you friends i find it out it was because time is also included in dtpdate.value i added .date property to it anyway thank you alot

C#

try
            {
                DateTime time = dtpfromDate.Value.Date;
                MessageBox.Show("" + time);
                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");
                tbljobcodeview.DataSource = ds.Tables["tbljobmastertable"].DefaultView;
            }
            catch (Exception eps)
            {
                MessageBox.Show("" + eps);
            }

Query returns empty data set or exception

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Query returns empty data set or exception

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us