selecting data from database and showingg it to gridview

Question

0.00/5 (No votes)

See more:

{
OleDbConnection cn = new OleDbConnection();
        cn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\placement cell\project.mdb";
        cn.Open();
        string sql1 = "select name from student where 10thper>='" + DropDownList3.Text + "'AND  12thper>='" + DropDownList4 + "'AND aggregate>='" + DropDownList5 + "'AND backlog<='" + DropDownList6 + "'AND academicgap<='" + DropDownList7 + "'";
        System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand(sql1, cn);
        System.Data.OleDb.OleDbDataAdapter adp = new OleDbDataAdapter();
        DataSet ds = new DataSet();
        adp.SelectCommand = cmd1;
        adp.Fill(ds);
        DataTable dt = ds.Tables[0];
        OleDbDataReader dr = cmd1.ExecuteReader();
        while (dr.Read() == true)
        {
            GridView1.DataSource = dt;
        }
        cn.Close();
        }

this is the code.
the error is

Server Error in '/placement cell' Application.<br />
Syntax error (missing operator) in query expression '10thper>='55'AND  12thper>='System.Web.UI.WebControls.DropDownList'AND aggregate>='System.Web.UI.WebControls.DropDownList'AND backlog<='System.Web.UI.WebControls.DropDownList'AND academicgap<='System.Web.UI.WebControls.DropDownList''.<br />
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.<br />
<br />
Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression '10thper>='55'AND  12thper>='System.Web.UI.WebControls.DropDownList'AND aggregate>='System.Web.UI.WebControls.DropDownList'AND backlog<='System.Web.UI.WebControls.DropDownList'AND academicgap<='System.Web.UI.WebControls.DropDownList''.<br />
<br />
Source Error:<br />
<br />
Line 146:        DataSet ds = new DataSet();<br />
Line 147:        adp.SelectCommand = cmd1;<br />
Line 148:        adp.Fill(ds);<br />
Line 149:        DataTable dt = ds.Tables[0];<br />
Line 150:        OleDbDataReader dr = cmd1.ExecuteReader();<br />
<br />
Source File: e:\placement cell\recsearch.aspx.cs    Line: 148

Posted 3-May-11 0:54am

harsh_deep

Updated 3-May-11 0:56am

Toniyo Jackson

v2

Add a Solution

Comments

aryanbharti 3-May-11 9:20am

Hi It will be better to do it using stored procedure. It will be easy to bind Grid and show data from database.

6 solutions

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)

LakshmiNarayana Nalluri · Answer 1 · 2011-05-03T02:37:00

Solution 6

sqldataadapter da=new sqldataadapter("select statement",connection);
datatable dt=new datatable();
da.fill(dt);
if(dt.rows.count==0)
{
'so norecord message using page.registerstartupscript.....
}
else
{
gridview1.datasource=dt;
gridview1.databind();
}

Posted 3-May-11 2:37am

LakshmiNarayana Nalluri

Mahendra.p25 · Answer 2 · 2011-05-03T01:11:00

Solution 5

try this

//to bind your Gridview 
 Con.Open();
 Cmd = new SqlCommand(sql1 );
 SqlDataAdapter dt = new SqlDataAdapter(Cmd, Con);
 DataSet ds = new DataSet();
 dt.Fill(ds);
 GridView1.DataSource = ds;
 GridView1.DataBind();
 Con.Close();

//your Query is incorrect 

//Refer to the above experts advice  and take values of dropdown  as per DataType of your
//Database Table Column 

//ex: if it is int Convert your drop down value to int 
//else string

Posted 3-May-11 1:11am

Mahendra.p25

Comments

harsh_deep 3-May-11 7:25am

still having the error....>>

Syntax error (missing operator) in query expression '10thper>='55' AND 12thper>='55' AND aggregate>='55'AND backlog<='0' AND academicgap<='0''.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression '10thper>='55' AND 12thper>='55' AND aggregate>='55'AND backlog<='0' AND academicgap<='0''.

Source Error:

Line 146: DataSet ds = new DataSet();
Line 147: adp.SelectCommand = cmd1;
Line 148: adp.Fill(ds);
Line 149: DataTable dt = ds.Tables[0];
Line 150: OleDbDataReader dr = cmd1.ExecuteReader();

plz.......provide me the right query...

OriginalGriff · Answer 3 · 2011-05-03T01:09:00

To add to what Abhinav says, you should also move to parametrized queries:

string sql1 = "select name from student where 10thper>=@P10 AND  12thper>=@P12 AND  aggregate>=@AGG AND backlog<=@BLG AND academicgap<=@ACG";
System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand(sql1, cn);
cmd1.Parameters.AddWithValue("@P10",DropDownList3.Text);
cmd1.Parameters.AddWithValue("@P12",DropDownList4.Text);
cmd1.Parameters.AddWithValue("@AGG",DropDownList5.Text);
cmd1.Parameters.AddWithValue("@BLG",DropDownList6.Text);
cmd1.Parameters.AddWithValue("@ACG",DropDownList7.Text);

It makes it a LOT more obvious and protects against accidental or deliberate SQL Injection attacks.

BTW: While you are changing this, please change your variable names: You may be able to remember what DropDownList5 contains today, but I bet you won't next month! If you call it "ddlAggregate" or similar, it becomes a lot easier to read and check for mistakes.

Shahriar Iqbal Chowdhury/Galib · Answer 4 · 2011-05-03T01:03:00

Hi,
Mostly syntax error.Please rewrite the query as follows,

{
 OleDbConnection cn = new OleDbConnection();
 cn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\placement cell\project.mdb";
 cn.Open();
 string sql1 = "select name from student where 10thper>='" + DropDownList3.SelectedValue + "' AND  12thper>='" + DropDownList4.SelectedValue + "' AND aggregate>='" + DropDownList5.SelectedValue + "'AND  backlog<='" + DropDownList6.SelectedValue + "' AND academicgap<='" + DropDownList7.SelectedValue + "'";
 System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand(sql1, cn);
 System.Data.OleDb.OleDbDataAdapter adp = new OleDbDataAdapter();
 DataSet ds = new DataSet();
 adp.SelectCommand = cmd1;
 adp.Fill(ds);
 DataTable dt = ds.Tables[0];
 OleDbDataReader dr = cmd1.ExecuteReader();
 while (dr.Read() == true)
 {
  GridView1.DataSource = dt;
 }
 cn.Close();
}

Hope this will help.

Abhinav S · Answer 5 · 2011-05-03T01:00:00

Solution 2

You need to give a space between ' and AND ("'AND aggregate>" should be "' AND aggregate")

A note, in your query, you are doing comparisons directly to the dropdownlist e.g. 2thper>='System.Web.UI.WebControls.DropDownList' which to me appears to be wrong. You should compare to the content inside the drop down list.

Posted 3-May-11 1:00am

Abhinav S

Comments

harsh_deep 3-May-11 7:33am

done..bt still having error can you plz provide me the code..

Toniyo Jackson · Answer 6 · 2011-05-03T00:59:00

Solution 1

Give single space before all AND operator.

string sql1 = "select name from student where 10thper>='" + DropDownList3.Text + "' AND  12thper>='" + DropDownList4 + "' AND aggregate>='" + DropDownList5 + "' AND backlog<='" + DropDownList6 + "' AND academicgap<='" + DropDownList7 + "'";

Posted 3-May-11 0:59am

Toniyo Jackson

v2

Comments

harsh_deep 3-May-11 7:07am

now after editing the code like....>>>>
{
OleDbConnection cn = new OleDbConnection();
cn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\placement cell\project.mdb";
cn.Open();
string sql1 = "select name from student where 10thper>='" + DropDownList3.Text + "' AND 12thper>='" + DropDownList4.Text + "' AND aggregate>='" + DropDownList5.Text + "' AND backlog<='" + DropDownList6.Text + "' AND academicgap<='" + DropDownList7.Text + "'";
System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand(sql1, cn);
System.Data.OleDb.OleDbDataAdapter adp = new OleDbDataAdapter();
DataSet ds = new DataSet();
adp.SelectCommand = cmd1;
adp.Fill(ds);
DataTable dt = ds.Tables[0];
OleDbDataReader dr = cmd1.ExecuteReader();
while (dr.Read() == true)
{
GridView1.DataSource = dt;
}
cn.Close();
}
i got this type of error....>>
Syntax error (missing operator) in query expression '10thper>='55' AND 12thper>='55' AND aggregate>='55' AND backlog<='0' AND academicgap<='0''.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression '10thper>='55' AND 12thper>='55' AND aggregate>='55' AND backlog<='0' AND academicgap<='0''.

Source Error:

Line 146: DataSet ds = new DataSet();
Line 147: adp.SelectCommand = cmd1;
Line 148: adp.Fill(ds);
Line 149: DataTable dt = ds.Tables[0];
Line 150: OleDbDataReader dr = cmd1.ExecuteReader();

selecting data from database and showingg it to gridview

6 solutions

Solution 6

Solution 5

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0