Click here to Skip to main content
15,671,597 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
In my database there are few many tables, i want to join two of them.
one is Customer_Master and other is Collection

in Customer_Master table there are these following fields are available:

in my Collection table

Now i have created an asp page form(here is the link[^]) where i get an result using the above tables.

i am writing this sql query and get the above error:

if (rdbPaid.Checked)
                da = new OleDbDataAdapter("select Customer_Master.Cust_ID, Customer_Master.Cust_Name, Customer_Master.Area_Code, Collection.Amount, Collection.ForMonth, Collection.ForYear From Customer_Master Inner Join Collection on Customer_Master.Cust_ID=Collection.Cust_ID where formonth='" + cmbMonth.Text + "' and area_name='" + cmbArea.Text + "'and foryear='"+cmbYear.Text+"'", con);
                dt = new DataTable();
                if (dt.Rows.Count > 0)
                    lblMessage.Visible = true;
                    lblCustomerCount.Visible = true;
                    lblMessage.Text = "Total Number Of Customer is";
                    for (int i = 0; i <= (dt.Rows.Count - 1)+1; i++)
                        lblCustomerCount.Text = i.ToString();
                    grdMCPD.DataSource = dt;
                    grdMCPD.Columns[0].HeaderText = "Customer ID";
                    grdMCPD.Columns[1].HeaderText = "Customer Name";
                    grdMCPD.Columns[2].HeaderText = "Area Code";
                    grdMCPD.Columns[3].HeaderText = "Ammount Paid";
                    grdMCPD.Columns[4].HeaderText = "Month";
                    grdMCPD.Columns[5].HeaderText = "Year";
                    grdMCPD.Columns[0].Width = 150;
                    grdMCPD.Columns[2].Width = 100;
                    grdMCPD.Columns[1].Width = 100;
                    grdMCPD.Columns[3].Width = 100;
                    grdMCPD.Columns[4].Width = 75;
                    grdMCPD.Columns[5].Width = 40;

can anyone help to get out of this problem.
Thanks in advance.
Updated 16-Aug-11 3:08am
senthil sennu 16-Aug-11 9:07am    
Is your Query is running correctly? In which line you are getting a error?
sahabiswarup 16-Aug-11 9:09am    
Data type mismatch in criteria expression
sahabiswarup 16-Aug-11 9:16am    
in this line i am getting an error that OledbException was unhandled.
Data type mismatch in criteria expression
senthil sennu 16-Aug-11 9:13am    
put try catch block and get the line number and stack trace.

Is your ForMonth column an integer or a varchar? If it's an integer, take the single quotes out of your query string for that particular parameter.
Share this answer
Herman<T>.Instance 16-Aug-11 9:21am    
and what about ForYear ?
sahabiswarup 16-Aug-11 9:22am    
I create this database on Access. And that ForMonth is char type and ForYear is Integer type.
This error is due to Error in the query

Try to get the query and run the query in Query Anlyser. You wil get exact error. As per my understanding your query formation is wrong.
Share this answer
sahabiswarup 16-Aug-11 9:28am    
can you please rewrite my query? Actually i never write an inner join query so it will be a lesson for me.
Simon Bang Terkildsen 16-Aug-11 9:35am    
how would it be a lesson for you if he does your work for you? Unless it's a lesson on how to get people to do your work.
And I beleave John gave you the answer, based on your reply, you should just replace ForMonth with ForYear in his solution.

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900