Pass String into SQL Query from Textbox

Question

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

have a look at parameterized queries and SqlCommand.

Daniel A. White
– Daniel A. White

2015-01-24 20:41:54 +00:00
Commented Jan 24, 2015 at 20:41 — Daniel A. White
– Daniel A. White, Commented Jan 24, 2015 at 20:41

Mudit Juneja · Accepted Answer · 2015-01-24 22:44:06Z

4

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.

edited Jan 24, 2015 at 22:44

answered Jan 24, 2015 at 20:57

Mudit Juneja

1659 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Dan Cundy Over a year ago

Great answer with explanations! Explaining what is actually happening really helps when you are learning a new language. Thank you.

Dan Cundy Over a year ago

The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand(string, System.Data.SqlClient.SqlConnection)' has some invalid arguments

Mudit Juneja Over a year ago

Sorry man...i thought that ConnStr is a SqlConnection Object...hold for a sec

Dan Cundy Over a year ago

Nope connStr is my connection string. :) Okay thank you,

Mudit Juneja Over a year ago

And i think that you have also not opened the sqlconnection.

|

Hive · Accepted Answer · 2015-01-24 20:46:32Z

0

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

answered Jan 24, 2015 at 20:46

Hive

2131 gold badge5 silver badges17 bronze badges

1 Comment

BCdotWEB Over a year ago

Don't use AddWithValue.

mmking · Accepted Answer · 2015-01-24 21:16:01Z

0

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;

edited Jan 24, 2015 at 21:16

answered Jan 24, 2015 at 20:45

mmking

1,5854 gold badges26 silver badges36 bronze badges

Collectives™ on Stack Overflow

Pass String into SQL Query from Textbox

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related