getting error on sql query in asp.net using c#

Question

I am using this sql query in c#

select t.testname,t.totalque,r.testdate,r.score 
from test t, result r 
where t.testid=r.testid 
  and r.login='vsdep980'

but it shows an error that is

invalid column name 'vsdep980'

now here vsdep980 is the value which is stored in the column login but why it shows this type of error.

now my code is

SqlCommand cmd = new SqlCommand("select t.testname,t.totalque,r.testdate,r.score from test t, result r where t.testid=r.testid and r.login=vsdep980", con);
con.Open();
int c = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
if (c > 0)
{
    Response.Write("<br><br><h1 class='head1'> You have not given any quiz</h1>");
    Response.End();
}
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
DataTable dt = ds.Tables[0];
DataRow dr = null;
Response.Write("<table border='1' align='center'><tr class='style2'><td width='300'>Test Name </td><td> Total<br> Question </td><td> Date </td><td> Score </td></tr>");
foreach (DataRow dr1 in dt.Rows)
{
    dr = dr1;
    Response.Write("<tr class='style8'><td> "+dr[0]+" </td><td align='center'> "+dr[1]+" </td><td align='center'> "+dr[2]+" </td><td align='center'>"+dr[3]+" </td></tr>");
}
Response.Write("</table>");

image of error msg

Please help me.

Thanks.

yes ofcourse this code is running well in sqlserver but in c# it shows an error. — V.Deep
– V.Deep, Commented Nov 15, 2017 at 20:33

Community · Accepted Answer · 2020-07-29 18:25:24Z

5

1- Do not use old joins.

Bad habits to kick : using old-style JOINs

2- Use parameters in your query.

A parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. The most important reason to use parameterized queries is to avoid SQL injection attacks.

Your query should be:

SqlCommand cmd = new SqlCommand("select t.testname,t.totalque,r.testdate,r.score 
from test t, result r where t.testid=r.testid and r.login='vsdep980'", con);

You miss quote '', so the vsdep980 will act as a column instead of a value.

And your query should be like:

SELECT T.testname,
       T.totalque,
       R.testdate,
       R.score 
FROM test T JOIN result R ON T.testid = R.testid -- Use the new style instead
WHERE R.login = 'vsdep980';

edited Jul 29, 2020 at 18:25

CommunityBot

11 silver badge

answered Nov 15, 2017 at 20:39

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

V.Deep Over a year ago

if i use single quote it shows again a new error "input string was not in the correct format"

Mohamed Najiullah Over a year ago

Try using the single quotes with the escape sequence like I've mentioned i.e. \'vsdep980\'

Mohamed Najiullah Over a year ago

I've added more details in the question's comments section

squillman · Accepted Answer · 2017-11-16 13:36:20Z

You're executing a query that returns multiple columns (and, I'm assuming multiple rows) with a call to ExecuteScalar(). This method is primarily used for retrieving a single value from SQL Server (it returns the value from the first column of the first row of your data). I expect that the first value of t.testname is not convertable to an Int32.

From your code, it looks like you're trying to check to see if any rows are returned from your query. If that is the case, you should do something like this instead:

SqlCommand cmd = new SqlCommand("select t.testname,t.totalque,r.testdate,r.score from test t, result r where t.testid=r.testid and r.login='vsdep980'", con);
con.Open();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count == 0)
{
    Response.Write("<br><br><h1 class='head1'> You have not given any quiz</h1>");
    Response.End();
}

Mohamed Najiullah · Accepted Answer · 2017-11-15 21:17:12Z

1

The query has to use single quotes while specifying the filter text i.e vsdep980.

Edit: Looking at the code I saw that the command is executed using ExecuteScalar. ExecuteScalar executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

Since the first column is testname it'll return a string that cannot be converted to an integer. Rethink what you want to do with the return value and change it accordingly

edited Nov 15, 2017 at 21:17

answered Nov 15, 2017 at 20:40

Mohamed Najiullah

4235 silver badges12 bronze badges

Collectives™ on Stack Overflow

getting error on sql query in asp.net using c#

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related