CODEWITHSUNDEEP
c#.netado.net
BehnamHesami
BehnamHesami

Reputation: 55

How use WHERE in SqlDataAdapter in C#

How use WHERE in SqlDataAdapter in C#?

I want get name in a textbox and use that at query but it wont work .

SqlConnection sqlconnection = new SqlConnection("Server=Behnam\\Accounting;Initial Catalog=Accounting;Integrated Security=TRUE");

DataTable dt = new DataTable();
string _search_name = txt_search.Text;

SqlDataAdapter SDA = new SqlDataAdapter("SELECT dbo.tbl_user.field1,dbo.tbl_user.field2 FROM tbl_user WHERE dbo.tbl_user.name=_search_name ", sqlconnection);

SDA.Fill(dt);

dataGridView1.DataSource = dt;

Upvotes: 3

Views: 4525

Answers (3)

Steve
Steve

Reputation: 216313

Prepare the command text and use a parameter for the value of your search. Then use that command text to initialize a new SqlCommand. Fill the parameter value with AddWithValue and pass the SqlCommand to the constructor of the SqlDataAdapter.

string cmdText = "SELECT dbo.tbl_user.field1,dbo.tbl_user.field2 " +  
                 "FROM tbl_user WHERE dbo.tbl_user.name=@search_name"
SqlCommand cmd = new SqlCommand(cmdText, sqlconnection);
cmd.Parameters.AddWithValue("@search_name", _search_name);
SqlDataAdapter SDA = new SqlDataAdapter(cmd);

The SqlDataAdapter will store your command as the SelectCommand property and will use the passed in SqlCommand to execute the query to retrieve the records from the database.

Keep in mind that AddWithValue is a shortcut with some drawbacks. For example it pass Always a string as a nvarchar parameter with size equal to the actual lenght of the variable. This effectively reduces the performance of the Sql Server Optimizer.

This is a very enlightening article on the issue

Upvotes: 4

Koushik
Koushik

Reputation: 362

Try this. you were using the string directly in the query which will go undetected.

SqlConnection sqlconnection = new SqlConnection("Server=Behnam\\Accounting;
Initial Catalog=Accounting;Integrated Security=TRUE");
DataTable dt = new DataTable();
SqlDataAdapter SDA = new SqlDataAdapter("SELECT dbo.tbl_user.field1,dbo.tbl_user.field2 FROM tbl_user WHERE dbo.tbl_user.name=@searchName" , sqlconnection);
SDA.SelectCommand.Parameters.AddWithValue("@searchName", txt_search.Text);        
SDA.Fill(dt);
dataGridView1.DataSource = dt;

Upvotes: 2

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

So, you were pretty close, you just needed to define a parameter inside the query and then add that parameter. However, in the following code block I've also conveniently recommended a more appropriate approach to using the classes needed to get the data (pun intended). The using statement here ensures that the objects get disposed of properly after you are done using them (man I just can't stop with the puns!)

using (SqlConnection c = new SqlConnection(connString))
{
    c.Open();
    using (SqlDataAdapter sda = new SqlDataAdapter(
        "SELECT dbo.tbl_user.field1, dbo.tbl_user.field2 FROM tbl_user " +
        "WHERE dbo.tbl_user.name= @name", c))
    {
        sda.SelectCommand.Parameters.AddWithValue("@name", txt_search.Text);
        DataTable dt = new DataTable();
        sda.Fill(dt);
    }
}

Upvotes: 3

Related Questions