CODEWITHSUNDEEP
c#sql-server
sama
sama

Reputation:

Help me Fix this SQL Statement?

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);

Upvotes: 1

Views: 279

Answers (5)

George Stocker
George Stocker

Reputation: 57872

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

Upvotes: 1

Jason Musgrove
Jason Musgrove

Reputation: 3583

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

Upvotes: 4

eKek0
eKek0

Reputation: 23289

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.

Upvotes: -1

Matthew Jones
Matthew Jones

Reputation: 26190

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 + "'"

Upvotes: 0

tekBlues
tekBlues

Reputation: 5793

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

Upvotes: 0

Related Questions