-3

Can someone explain why this is happening? I am trying to retrieve data from mysql database but this error occur but the same code works fine when I retrieve a integer value such as TeacherID. Something is wrong with retraveling non-numeric values.

string connectionString = (@"Data Source=(LocalDB)\v11.0;AttachDbFilename=\myDB.mdf;Integrated Security=True");

string sql = "SELECT * FROM Teacher WHERE Subject = "+ TSubject.Text;

SqlConnection connection = new SqlConnection(connectionString);
SqlDataAdapter dataadapter = new SqlDataAdapter(sql, connection);

DataSet ds = new DataSet();

connection.Open();

dataadapter.Fill(ds, "Teacher");

connection.Close();

DataGridViewStudent.DataSource = ds;
DataGridViewStudent.DataMember = "Teacher";

I have already checked spell mistake or type, all okay. So what is the issue?

Improve this question
8
  • 1
    Use placeholdes and the problem with "fix itself". Using this terribly poor way of building an SQL string which results in a value that is not correctly quoted; this is "accidental" injection. It can also lead to malicious injection. Please do not follow suggestions of manually adding proper string quotes. Commented Dec 28, 2020 at 20:20
  • 1
    stackoverflow.com/questions/7505808/…, visualstudiomagazine.com/articles/2017/07/01/… , etc. It will make life much easier to follow such patterns going forward. Commented Dec 28, 2020 at 20:22
  • 3
    Does this answer your question? How to give ADO.NET Parameters Commented Dec 28, 2020 at 20:23
  • 1
    esqew it worked for me, thanks Commented Dec 28, 2020 at 20:24
  • 1
    That is not a MySQL database. Commented Dec 28, 2020 at 22:19

1 Answer 1

Reset to default
5

Your problem is that your query is coming out as

"SELECT * FROM Teacher WHERE Subject = Math"

Math is not not quoted so assumed to be a column rather than a value.

One way you could - BUT SHOULD NOT - fix this is by adding the quotes manually. Instead parameterise the query.

string sql = "SELECT * FROM Teacher WHERE Subject = @SUBJECT";

Sqlcommand command = new SqlCommand(sql, connection);
command.Parameter.Add(new SqlParameter("@SUBJECT", TSubject.Text));
SqlDataAdapter dataadapter = new SqlDataAdapter(command);
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I believe I've fixed the obvious mistakes now, although it is still just a snippet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.