Help me Fix this SQL Statement?

Question

Can someone let me know what is wrong with my SQL Statement and how I can improve it?

da = new SqlDataAdapter("SELECT * FROM Guests"+" WHERE Students.name='" + 
   byNametextBox.Text + "'", MyConn);

Answer 1

SqlDataAdapter("SELECT Guests.* FROM Guests,Students WHERE Guest.StudentId = Student.Id and  Students.name='" + byNametextBox.Text + "'", MyConn);`

Answer 2

You need an Inner Join. I think it would be something like this:

SELECT Guests.* FROM Guests INNER JOIN Students ON Students.name = Guests.name WHERE Students.name = '" + byNametextBox.Text + "'"

Answer 3

Try it:

"SELECT g.*
FROM Guests g
INNER JOIN Students s ON g.StudentId = s.StudentId
WHERE Students.Name = '" + byNametextBox.Text + '"'

Assuming that the field wich relates both tables is StudentId.

Beware that SQL is not the same between different Servers. This statement will work on Sql Server, I don't know in others. Also, beware that you aren't protecting yourself on SQL Injection attacks. You should perform your query with parameters, instead of concatenating strings in the way you are doing it.

This is a simple query that you should know by yourself. You can search for tutorials on Google, but here is a generic introduction.

Answer 4

An EXISTS predicate is slightly more efficient than a JOIN if you want only columns from one of the tables. Additionaly - never inject strings into SQL statements like that - you're just begging for SQL Injection attacks, or related crashes errors (Yes, I know it's a Forms application, but the same holds true. If you're searching for a name like "O'Leary", you'll get a crash).

SqlCommand cmd = new SqlCommand("SELECT * FROM Guests WHERE EXISTS (SELECT Id FROM Students WHERE Guests.StudentId = Students.Id And Students.name= @name)", MyConn);
cmd.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = byNametextBox.Text;
SqlDataAdapter adapt = new SqlDataAdapter(cmd);

Note: Some people may argue that "SELECT *" is bad, and that you should consider specifying individual column names

Answer 5

You need to worry about SQL Injection. Put simply, SQL Injection is when a user is able to put arbitrary SQL statements into your query. To get around this, either use a Stored Procedure or a Parametrized SQL Query. An Example of a Parametrized SQL query is below:

SqlConnection conn   = null;
SqlDataReader reader = null;
//Connection string goes here

string studentName = byNametextBox.Text;

SqlCommand cmd = new SqlCommand(
 "SELECT * FROM Guests "+" WHERE Students.name = @name", conn);

SqlParameter param  = new SqlParameter("@name", SqlDbType.NVarChar, 50);

param.Value = studentName;

cmd.Parameters.Add(param);
reader = cmd.ExecuteReader();
//Do stuff with reader here