MysqlDateAdapter not fetching `Date` from mysql

Question

0.00/5 (No votes)

See more:

Hi all.....
My query not fetching Date column from Mysql Databse.and not showing 'date column' on gridview. Mysql command is like this:

SQL

select p.sno as RecieptNo,p.feesamount,p.totalpaid,p.dueamount,p.`paymentdate`,p.paymode,p.bank,p.checkno,p.status  from paymentdetails p,studentdetails s where p.sid=s.sid and s.class='" + DropDownList2.SelectedValue + "' and s.division='" + DropDownList3.SelectedValue + "' and s.rollno='" + DropDownList4.SelectedValue + "'  order by p.sno

Can any one please help me.
Thanks in advance.

Posted 6-Mar-11 19:51pm

tulasiram3975

Updated 6-Mar-11 19:56pm

v3

Add a Solution

Comments

Sunasara Imdadhusen 7-Mar-11 1:59am

why are you enclosed payment date to quotation e.g. p.`paymentdate`

tulasiram3975 7-Mar-11 2:01am

i tried in many ways like this way also. in order to escape from predefined keywords

2 solutions

Solution 1

Firstly: remove the quotation marks from the field names list: it won't help.

Secondly: Check you have a column called paymentdate in your paymentdetails table, and that it is a date format column.

Thirdly: Check you have data in the column.

If all the above are OK, we need to see the code fragment where you use the SELECT statement.
We also need any error messages, etc., that you get.

"i removed quotation, had column in table and is a Date DataType third one is i had more than 30 columns but it won't work my code is:

MySqlDataAdapter da = new MySqlDataAdapter("select p.sno as RecieptNo,p.feesamount,p.totalpaid,p.dueamount,p.paymentdate,p.paymode,p.bank,p.checkno,p.status from paymentdetails p,studentdetails s where p.sid=s.sid and s.class='" + DropDownList2.SelectedValue + "' and s.division='" + DropDownList3.SelectedValue + "' and s.rollno='" + DropDownList4.SelectedValue + "' order by p.sno", con); DataSet ds = new DataSet(); da.Fill(ds); GridView1.DataSource = ds.Tables[0]; GridView1.DataBind();
remaining all columns coming into gridview except paymentdate
no error message while showing output
thank you"

Odd. Try cutting the select statement down to the minimum:

SQL

select p.sno, p.paymentdate from paymentdetails p

and see what happens (this is just trying to work out where the problem is, not fix it yet - so comment out you existing SELECT, you will need it again!)

If this works, try re-inserting elements a few at a time until it goes wrong.

"if i use this query(select p.sno, p.paymentdate from paymentdetails p) then i get only sno date column not coming"

Take out "p.sno":

SQL

select paymentdate from paymentdetails

Get any columns?

Posted 6-Mar-11 20:33pm

OriginalGriff

Updated 6-Mar-11 21:45pm

v3

Comments

tulasiram3975 7-Mar-11 2:44am

i removed quotation, had column in table and is a Date DataType third one is i had more than 30 columns but it won't work my code is:
MySqlDataAdapter da = new MySqlDataAdapter("select p.sno as RecieptNo,p.feesamount,p.totalpaid,p.dueamount,p.paymentdate,p.paymode,p.bank,p.checkno,p.status from paymentdetails p,studentdetails s where p.sid=s.sid and s.class='" + DropDownList2.SelectedValue + "' and s.division='" + DropDownList3.SelectedValue + "' and s.rollno='" + DropDownList4.SelectedValue + "' order by p.sno", con);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
remaining all columns coming into gridview except paymentdate
no error message while showing output
thank you

OriginalGriff 7-Mar-11 3:34am

Answer updated

tulasiram3975 7-Mar-11 5:24am

am really sorry,
i got an error from this query(select paymentdate from paymentdetails)
error is:
The data source for GridView with id 'GridView1' did not have any properties or attributes from which to generate columns. Ensure that your data source has content.

OriginalGriff 7-Mar-11 5:38am

Yes, I'm still thinking. All this implies that the column is non-existant, but then why isn't the database / DataAdapter giving an error...?

tulasiram3975 7-Mar-11 5:47am

But sir, Remaining All Columns coming into gridview e.g:select sid from paymentdetails works fine sid coming. the only problem is date please help me sir ...
thank you

OriginalGriff 7-Mar-11 5:53am

Exactly. So the date column should be returned as well. If it is the only column listed in the select statement, then it should return either a row for each and every row in the database, or an error to say the column doesn't exist. I can't see how it returns an empty dataset from a select without a where condition if the database is not empty!
I'm confused, and still thinking...

OriginalGriff 7-Mar-11 5:54am

I'm clutching at straws a bit, but can you go into your MySql database manager and export the table definitions (not the data) as SQL statements? I'd like to see what you have...

tulasiram3975 7-Mar-11 12:47pm

ok am very sorry for late replay
this is my table :
sno int(11) NO PRI NULL auto_increment
sid int(11) NO NULL
fistfeetype varchar(50) NO NULL
feestypes varchar(50) NO NULL
feesamount varchar(10) NO NULL
totalpaid varchar(15) NO NULL
dueamount varchar(15) NO NULL
paymentdate date NO NULL
paymode varchar(10) NO NULL
bank varchar(20) NO NULL
checkno varchar(25) NO NULL
status varchar(15) NO NULL
thank you for answering sir

tulasiram3975 7-Mar-11 3:43am

if i use this query(select p.sno, p.paymentdate from paymentdetails p) then i get only sno . date column not coming i think this is a problem of date format in mysql and gridview thank you

tulasiram3975 7-Mar-11 3:48am

No sir,
if i use this(select paymentdate from paymentdetails) gridview not appearing sir...

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)

tulasiram3975 · Accepted Answer · 2011-03-07T08:17:00

I found Solution sir....
special thanks to you 'OriginalGriff', you are tried a lot
Solution is :

XML

<Columns>
     <asp:BoundField HeaderText="RecieptNo" DataField="sno" />
    <asp:BoundField HeaderText="Amount" DataField="feesamount"  />
   <asp:BoundField HeaderText="Paid" DataField="totalpaid" />
   <asp:BoundField HeaderText="Due Amount" DataField="dueamount"  />
  <asp:BoundField HeaderText="Date(M/D/YY)" DataField="paymentdate" />
    <asp:BoundField HeaderText="PaymentMode" DataField="paymode" />
    <asp:BoundField HeaderText="Bank" DataField="bank" />
    <asp:BoundField HeaderText="CheckNo" DataField="checkno"/>
   <asp:BoundField HeaderText="Status" DataField="status" />
</Columns>

in this way i am getting date column also.