CODEWITHSUNDEEP
c#sql-server
Dan Cundy
Dan Cundy

Reputation: 2859

Pass String into SQL Query from Textbox

Background

I've written a function that retrieves data using a SQL query and then outputs that data to a label. At the moment the search string is hard coded to "1002". The function is fired on a button click event.

Question

How do I pass data into my SQL query from a textbox so my search string is the contents of the text box, instead of 1002?

Code

private void getInfoStationID()
{
    String ConnStr = "Data Source=SqlServer; Initial Catalog=Database; User ID=Username; Password=Password";
    String SQL = "SELECT stationname FROM dbo.Stations WHERE StationID = 1002";

    SqlDataAdapter Adpt = new SqlDataAdapter(SQL, ConnStr);
    DataSet question = new DataSet();
    Adpt.Fill(question);

    foreach (DataRow dr in question.Tables[0].Rows)
    {
        nameTtb.Text += question.Tables[0].Rows[0]["stationname"].ToString();
    }
}

Upvotes: 1

Views: 3793

Answers (3)

Mudit Juneja
Mudit Juneja

Reputation: 165

Change the query to:

string constring = @""; // Declare your connection string here.

String SQL = "SELECT stationname FROM dbo.Stations WHERE StationID = @StationId";
SqlConnection con = new SqlConnection(constring);
con.Open();
SqlCommand command = new SqlCommand(SQL ,con);

and then you have to add parameter to the command object like this:

command .Parameters.Add("@StationId",SqlDbType.NVarChar).Value = textbox.Text;

Now you might be wondering why I have used parameters in the query. It is to avoid SQL Injection.

DataSet ds = new DataSet();
SqlDataAdapter adb = new SqlDataAdapter(command);
adb.Fill(ds);
con.Close();

And now you can iterate like this...

foreach (DataRow row in ds.Tables[0].Rows)
{

}

And you have to initialise the connection object and pass your connection string in it.

Upvotes: 4

mmking
mmking

Reputation: 1575

String ConnStr = "Data Source=SqlServer; Initial Catalog=Database; User ID=Username; Password=Password";
string SQL = "SELECT stationname FROM dbo.Stations WHERE StationID = @stationID";

SqlCommand command = new SqlCommand(SQL, ConnStr);

command.Parameters.Add(new SqlParameter("@stationID", SqlDbType.Int));
command.Parameters["@stationID"].Value = textbox.Text;

Upvotes: 0

Hive
Hive

Reputation: 213

You can change your SQL string to use a variable, like @StationID, then add the variable to the query from textbox.text

String SQL = "SELECT stationname FROM dbo.Stations WHERE StationID = @StationID";
SqlCommand cmd = new SqlCommand;
cmd.CommandText = SQL;
cmd.CommandType = CommandType.Text;
cmd.Connection = ConnStr;
cmd.Parameters.AddWithValue("@StationID", Textbox1.Text);
da = new SqlDataAdapter(cmd);
DataSet nds = new DataSet();
da.Fill(nds);

Upvotes: 0

Related Questions