-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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • TSubject is a combobox with some subjects in it like Math, English etc.. –  Dec 28 '20 at 20:20
  • 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. – user2864740 Dec 28 '20 at 20:20
  • 1
    https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements, https://visualstudiomagazine.com/articles/2017/07/01/parameterized-queries.aspx , etc. It will make life *much easier* to follow such patterns going forward. – user2864740 Dec 28 '20 at 20:22
  • 3
    Does this answer your question? [How to give ADO.NET Parameters](https://stackoverflow.com/questions/31420045/how-to-give-ado-net-parameters) – esqew Dec 28 '20 at 20:23
  • 1
    esqew it worked for me, thanks –  Dec 28 '20 at 20:24
  • user2864740 thanks for suggestion, i am checking it now –  Dec 28 '20 at 20:26
  • Also, either consider [`using..`](https://stackoverflow.com/q/23185990/2864740) to help deal with managing DB connections or let the `Fill` [open and close the connection itself](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dataadapter.fill?redirectedfrom=MSDN&view=net-5.0#System_Data_Common_DataAdapter_Fill_System_Data_DataSet_). – user2864740 Dec 28 '20 at 20:33
  • 1
    That is not a MySQL database. – mjwills Dec 28 '20 at 22:19

1 Answers1

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);
SBFrancies
  • 3,987
  • 2
  • 14
  • 37