How to use input from a textbox and use it in a mysql query in C#

Question

Code:

string connString = "SERVER=;UID=;PASSWORD=;DATABASE=;";
MySqlConnection connect = new MySqlConnection(connString);
MySqlCommand myCommand = connect.CreateCommand();

string input = textBox4.Text;

myCommand.CommandText = "SELECT * FROM project WHERE Id = @input";
connect.Open();

MySqlDataReader reader = myCommand.ExecuteReader();

if (reader.Read())
   textBox1.Text = reader["Name"].ToString();

connect.Close();

I am trying to use data from a form textbox (textBox4) and pass that input into a mysql query.

The program works by the user inputting their id number and the form outputs their name into another textbox (textBox1).

When I use either the string 'input' or textBox4.Text itself I get an error message which is: "Fatal error encountered during command execution."

However if I manually type in a correct id number in the code itself it returns the correct vaule

New to C# and mysql sorry for any big errors.

It looks like are you trying to do this with a parameter?

Brian
– Brian

2013-06-21 17:56:08 +00:00
Commented Jun 21, 2013 at 17:56 — Brian
– Brian, Commented Jun 21, 2013 at 17:56
stackoverflow.com/q/652978/62576

Ken White
– Ken White

2013-06-21 17:57:29 +00:00
Commented Jun 21, 2013 at 17:57 — Ken White
– Ken White, Commented Jun 21, 2013 at 17:57

Brian · Accepted Answer · 2013-06-21 18:02:45Z

3

Here is what I would do in your case:

string connString = "SERVER=;UID=;PASSWORD=;DATABASE=;";
MySqlConnection connect = new MySqlConnection(connString);
MySqlCommand myCommand = connect.CreateCommand();
string input = textBox4.Text;

myCommand.CommandText = "SELECT * FROM project WHERE Id = @input";
myCommand.Parameters.Add("@input", SqlDbType.Int).Value = Convert.ToInt32(textBox4.Text);
connect.Open();

MySqlDataReader reader = myCommand.ExecuteReader();

if (reader.Read())
   textBox1.Text = reader["Name"].ToString();

connect.Close();

As you can see in the example above I am doing two things:

Sanitizing the input by using a SqlParameter and,
Converting the value of "@input" to a number; which is the assumption based on your question.

Best of luck to you, and please continue learning!

answered Jun 21, 2013 at 18:02

Brian

5,1397 gold badges41 silver badges52 bronze badges

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

Comments

Andre Calil · Accepted Answer · 2013-06-21 18:01:46Z

2

You're almost there, actually. You're using a parameter (@input) and not concatenating strings ("select .. " + text + " from ..."), which is wrong for many reasons.

Just tell your command how to replace that parameter. I guess it's something like this (not sure for MySQL):

myCommand.Parameters.Add("@input", SqlDbType.Int).Value = Convert.ToInt32(textBox4.Text);

Note that I'm assuming that Id is of type SqlDbType.Int. You may change it accordingly.

You must do this before calling myCommand.ExecuteReader(). Most likely, you'll put this line after myCommand.CommandText = "...";.

edited Jun 21, 2013 at 18:01

answered Jun 21, 2013 at 17:55

Andre Calil

7,69236 silver badges41 bronze badges

5 Comments

Ed Gibbs Over a year ago

Andre - you should also tell the user where this line goes; basically after the myCommand.CommandText = ... and before the ExecuteReader. They're new to all this so they may not make the connection.

Brian Over a year ago

Plus one since you got your answer in while I was typing mine :P

Andre Calil Over a year ago

@Brian Thanks! I'm usually the slower one =)

legohead Over a year ago

Thanks very much Andre + Ed it now works! after a few hours of trying -.- I was looking around and saw parts of your solution dotted about, tried them all individually and got even more errors so I thought I was going in the wrong direction.

Andre Calil Over a year ago

@user2509919 You're welcome, I'm glad to help. Let us know if you have any further doubt regarding this question and don't forget to mark the answer as correct.

Collectives™ on Stack Overflow

How to use input from a textbox and use it in a mysql query in C#

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related