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 Jun 21 '13 at 17:56 — Brian, Jun 21 '13 at 17:56
http://stackoverflow.com/q/652978/62576 – Ken White Jun 21 '13 at 17:57 — Ken White, Jun 21 '13 at 17:57

score 3 · Answer 1 · answered Jun 21 '13 at 18:02

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!

Andre Calil · Accepted Answer · 2013-06-21T18:01:46.760

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 '13 at 18:01

answered Jun 21 '13 at 17:55

Andre Calil

7,652
34
41

1

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. – Ed Gibbs Jun 21 '13 at 17:59
Plus one since you got your answer in while I was typing mine :P – Brian Jun 21 '13 at 18:03
1

@Brian Thanks! I'm usually the slower one =) – Andre Calil Jun 21 '13 at 18:06
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. – legohead Jun 21 '13 at 18:06
@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. – Andre Calil Jun 21 '13 at 18:08

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

2 Answers2