get the values of a row in a table with specified id

Question

I have a table which contains student first name, last name, class, etc., and each student has his own "student code". I want to enter a "student code" and get back the values of the row with the specified student code.

I searched a lot and I tried to use datarow like this question.

Here's my code:

MySqlConnection connection = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
DataTable dt = new DataTable();

[WebMethod]
public string Student_Information(string Student_Code)
{
    connection.ConnectionString = "server= ; userid= ; password= ; database= ;";
    connection.Open();

    string code = Student_Code;

    DataRow[] drs = dt.Select("student_code='" + code + "'");

    foreach (DataRow dr in drs)
    {
        return "True";
    }

    return "False";
}

What help you need? Is code giving any error? It's not working? — Dionei Miodutzki, Mar 22 '18 at 20:21
You’re not loading anything from the database. Forget DataTables etc, just find a tutorial on how to use MySQL and go from there. — Sami Kuhmonen, Mar 22 '18 at 20:24
Are you ever hitting the database? You need to have data to populate the `DataTable`. You should start with [this question first](https://stackoverflow.com/questions/14020038/filling-a-datatable-in-c-sharp-using-mysql) — Mark C., Mar 22 '18 at 20:25

score 1 · Accepted Answer · answered Mar 22 '18 at 21:11

As Sami Kuhmonen proposed, you don't need a DataTable for 1 row. Use a DataTable for multiple rows that you need to access after closing the connection to the database.

Use MySqlCommand and the ExecuteReader() method. Then you can read the columns from the recordset.

[WebMethod]
public string Student_Information(string Student_Code)
{
    using( var connection = new MySqlConnection(ConnectionString))
    {
        connection.Open();
        using(var cmd = connection.CreateCommand())
        {
            cmd.CommandText = "select FirstName, LastName, Class from student where student_code=@code";
            cmd.Parameters.AddWithValue("@code", Student_Code);
            var rs = cmd.ExecuteReader();

            if( rs.Read() )
            {
                // read the data using something like
                var FirstName = rs["FirstName"].ToString();

                // or like this
                var LastName = rs.GetFieldValue<string>(rs.GetOrdinal("LastName"));
                return "True"; // as per your example
            }
            else
            {
                // student not found
            }
            rs.Close();
        }
    }
    return "False";
}

score 0 · Answer 2 · answered Mar 22 '18 at 21:00

Okay, so you've opened a connection to the database - that's the first step. But then, after you've got that done, you're probably going to want to work with SqlCommand and SqlDataReader. I notice that you've already got a MySqlCommand variable - but you're just setting it to a blank command.

MySqlCommand cmd = new MySqlCommand();

... that's not what you want. You want to set that 'cmd' variable to the command you want to run.

Then, once you've got those things done, you're going to need to do something like:

using (SqlDataReader reader = cmd.ExecuteReader())
{
    data = new DataTable();
    data.Load(reader);
}
conn.Close();

Hopefully that helps out. Best of luck :-)

get the values of a row in a table with specified id

2 Answers2