-
C# Search in database
hello guys i'm doing some kind of work with database it's called phone book, where u can add delete search for a friend an I stuck by searching,
i can do search by ID number, it works with it. but when i want to do by entering friend name, it's gives my error. here is the code searching by name. so if someone could help me how to do a search by a friend First name, it would be great :).
Code:
private void button1_Click(object sender, EventArgs e)
{
try
{
SqlConnection con;
con = new System.Data.SqlClient.SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Documents and Settings\\PsychO\\My Documents\\Visual Studio 2010\\Projects\\savarankiskas2\\savarankiskas2\\FriendsContact.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
DataTable dt = new DataTable();
SqlDataAdapter SDA = new SqlDataAdapter("SELECT * FROM Friends where FirstName = " + char.parse(textBox1.Text), con);
con.Open();
SDA.Fill(dt);
dataGridView1.DataSource = dt;
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
-
You don't need char.Parse. You need to put the content of textBox1.Text in quotes in your SQL-string:
Code:
string.Format("SELECT * FROM Friends where FirstName = '{0}' ", textBox1.Text)
Please note that this is very susceptible to sql injection attacks, better use bind parameters once you got it running.
-
hmm. i don't get a little bit. can you correct my code?
-
Code:
SqlDataAdapter SDA = new SqlDataAdapter("SELECT * FROM Friends where FirstName = " + char.parse(textBox1.Text), con);
This line is wrong. The first parameter needs to be a string that looks like this:
Code:
SELECT * FROM FRIENDS WHERE FIRSTNAME = 'Bob'
Obviously, Bob is your input. Build a string that looks like this and pass it to the adapter:
Code:
string sql = "SELECT * FROM FRIENDS WHERE FIRSTNAME = 'Bob' ";
SqlDataAdapter SDA = new SqlDataAdapter(sql, con);
Now you obviously need to get textBox1.Text in there or you will only select Bob forever. Use th line from my first post to format you sql string according to your input.
Databases are advanced stuff. Have a look at string formatting and SQL first.
-
i done it :D just needed to add this
string sql = "SELECT * FROM Friends where FirstName like '" + textBox1.Text+"'";
and it worked ^^. thx for your help
-
Okay, it's working, that's great. Please have a look at the concept of SQL injections and it's remedy, bind variables. Once you got your program working, try to change your formatted SQL strings to SQL commands and use the Parameters collection. It's way easier, too. Try to input " D'Artagnan " in your textbox and see what happens to your program.
-
You may also want to check LINQ which could make your task even simpler.
-
SqlCommand cmd = new SqlCommand("select * from STOCK WHERE Item_ID=" + txtItemID.Text, conn);
What would happen if I put "; DROP DATABASE;" in txtItemID?
-
here's an idea:
Code:
static void Main(string[] args)
{
string con = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\PsychO\My Documents\Visual Studio 2010\Projects\savarankiskas2\savarankiskas2\FriendsContact.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
string cmd = "SELECT * FROM Friends WHERE FirstName LIKE @name + '%'";
DataTable dt = new DataTable();
using (SqlDataAdapter a = new SqlDataAdapter())
{
try
{
a.SelectCommand = new SqlCommand();
a.SelectCommand.Connection = new SqlConnection(con);
a.SelectCommand.CommandType = CommandType.Text;
a.SelectCommand.CommandText = cmd;
a.SelectCommand.Parameters.AddWithValue("name", textBox1.Text);
a.Fill(dt);
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
Console.Read();
}
I'm not sure if the AddWithValue method of the SqlParameterCollection class will help prevent SQL injection attacks. would it? or is it better to use the Add method and specify the SQL type, etc?