C# App SQL Query

Question

Okay basically I have a SQL Server database that has details in it.

Column names: Student_Id, Student_name, Unit_number, Unit_grade

I would like to query this database using two textboxes where you enter the id and unit_number and it will return the results in a message box when a button is clicked.

Where the question marks in the code are is where I am unsure of how to display a message box with the result. Unless this is completely the wrong way of doing things, I am only starting out with SQL in C#

I shouldn't be prone to SQL Injection using parameters as far as I know?

try
{
    string str = "SELECT * FROM Students WHERE (Student_Id, Unit_number LIKE '%' + @search + '%')";

    SqlCommand command = new SqlCommand(str, connect);
    command.Parameters.Add("@search", SqlDbType.NVarChar).Value = textBox1.Text;
    command.Parameters.Add("@search", SqlDbType.NVarChar).Value = textBox2.Text;

    connect.Open();
    command.ExecuteNonQuery();

    SqlDataAdapter dataAdapt = new SqlDataAdapter();
    dataAdapt.SelectCommand = command;

    DataSet dataSet = new DataSet();

    dataAdapt.Fill(dataSet, "Student_Id, Unit_number");
    //?
    //?

    connect.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Ceisc · Accepted Answer · 2015-03-08 10:18:06Z

1

Your SQL is wrong in that your WHERE clause is syntactically incorrect. You probably want something like:

string str = "SELECT * FROM Students WHERE Student_ID = @id AND " +
    "Unit_number LIKE @search";

This assumes that Student_ID is a text type. The syntax would be slightly different if it was a number.

You are trying to add the same parameter to the query twice, which you won't want. Instead you'd want two parameters to match with the new SQL definition:

    command.Parameters.Add("id", SqlDbType.NVarChar).Value = 
        textBox1.Text;
    command.Parameters.Add("search", SqlDbType.NVarChar).Value = 
        "%" + textBox2.Text + "%";

Running ExecuteNonQuery on the SqlCommand object doesn't do much for you as it is a query and you're not asking for the result back.

If you're only expecting one table back from your query, you'd probably be better off with a DataTable rather than a DataSet (the DataSet can contain many tables which is overkill for what you need).

try
{
    string str = "SELECT * FROM Students WHERE Student_Id = @id AND " +
                 "Unit_number LIKE @search";

    connect.Open();

    SqlCommand command = new SqlCommand(str, connect);
    command.Parameters.Add("id", SqlDbType.NVarChar).Value = 
        textBox1.Text;
    command.Parameters.Add("search", SqlDbType.NVarChar).Value = 
        "%" + textBox2.Text + "%";


    SqlDataAdapter dataAdapt = new SqlDataAdapter();
    dataAdapt.SelectCommand = command;

    DataTable dataTable = new DataTable();

    dataAdapt.Fill(dataTable);

    // At this point you should have a DataTable with some results in it.

    // This is not going to be the best way of displaying data, 
    //  but it should show you _something_
    // It just iterates through the rows showing the columns 
    //  which you've shown as being in your data.

    foreach (DataRow dr in dataTable.Rows)
    {
        MessageBox.Show(String.Format("{0} - {1} - {2} - {3}", 
                        dr["Student_Id"], dr["Student_name"],
                        dr["Unit_number"], dr["Unit_grade"]));
    }

    connect.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

EDITED to change the parameter handling as it didn't quite do what was needed. The % symbols are not part of the parameter rather than the SQL string.

edited Mar 8, 2015 at 10:18

answered Mar 8, 2015 at 9:48

Ceisc

1,31812 silver badges18 bronze badges

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

8 Comments

Topher Over a year ago

I should have mentioned that the ID is set as an Int field in the table, would I have to alter the SELECT statement I assume?

Topher Over a year ago

I gave it a try with the alterations on the single quotes and when I run it it comes back with value cannot be null, Parameter name: dataTable.

Ceisc Over a year ago

If it's an int you just need to change the datatype in the parameter

Topher Over a year ago

I did it now reads SqlDbType.Int but where you have dataAdapt.Fill(dataTable); and in the for each statement it does not exist in the current context

Ceisc Over a year ago

Sorry... Small typo DataTable datatable = new DataTable(); should read DataTable dataTable = new DataTable();

|

Collectives™ on Stack Overflow

C# App SQL Query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related