Control for correct input with SQL in C#

Question

        txtBox2.Enabled = false;
        //made by francisco juliao
        Convert.ToInt32(txtBox1.Text);
        int Ticket = Convert.ToInt32(txtBox1.Text);
        SqlConnection mySC = new SqlConnection();
        SqlCommand com = new SqlCommand();
        mySC.ConnectionString = ("Data Source=localhost;Initial Catalog=RCA;Integrated Security=True");
        mySC.Open();
        com.Connection = mySC;
        com.CommandText = (@"SELECT Ticketid * FROM RCA)
            VALUES ('" + txtBox1.Text + "','" + output+ "');");
        mySC.Close();
       if (Ticket = //SQL ticketID check here.
            txtBox2.Enabled = true;
            MessageBox.Show("Valid ID! Input email now.");
            label1.Text = "Enter your email now.";
        }
        else
        {
            MessageBox.Show("Incorrect!");
        }

So I want my C# app to check if the ticketID(txtBox1.Text) that was typed by the user is in the SQL server. I have no idea how I would go about doing this. If the input is correct the label changes and the textbox2 gets enabled. If the email is not found in the sql database, a messagebox is shown with the text "incorrect!". Any help would be appreciated!

There is no point to use `ExecuteNonQuery` for a `SELECT` statement. You _actually_ try to say: `INSERT INTO RCA` instead? — Soner Gönül, Jan 18 '16 at 09:46
You are also new to SQL, I guess? Because that statement looks like a centaur - the front is `SELECT` and the back is `INSERT`. — CompuChip, Jan 18 '16 at 09:53
This remark is a bit off-topic so please view it only as a nice-to-know-in-the-long-run. Based on your questions, I'm assuming that you're new to programming lacking skills. Still, you seem to have potential, which is great. When it comes to communication with the database, I'd suggest using Entity Framework - you're going end up there soon anyway, hehe. Also, for communication with database the old-style way, you might want to use [*SqlDataAdapter*](http://stackoverflow.com/q/18205560/1525840) class (see accepted answer). It helped me the most. — Konrad Viltersten, Mar 08 '16 at 06:21

score 2 · Accepted Answer · answered Jan 18 '16 at 10:03

You should change the code on the Lost Focus event of the textbox1 i.e. your TicketID textbox.

That would look like this

private void txtBox1_LostFocus(object sender, RoutedEventArgs e)
{
    SqlConnection mySC = new SqlConnection();
    SqlDataReader reader;
    mySC.ConnectionString = ("Data Source=localhost;Initial Catalog=RCA;Integrated Security=True");
    mySC.Open();
    string sql = @"SELECT * FROM RCA Where Ticketid = @tktid;";
    using(var command = new SqlCommand(sql, mySC))
    {
        command.Parameters.Add("@tktid", txtBox1.Text);
        reader = command.ExecuteReader();
    }
    if(reader.HasRows)
    {
        //Here you change the Label with LabelName I have used string to show.
        string Lbllabel = "Change";
        txtEmail.IsEnabled = true;
    }
    mySC.Close();
}

Now to check the email you should use the same thing on TxtEmail_LostFocus

private void txtEmail_LostFocus(object sender, RoutedEventArgs e)
{
    SqlConnection mySC = new SqlConnection();
    SqlDataReader reader;
    mySC.ConnectionString = ("Data Source=localhost;Initial Catalog=RCA;Integrated Security=True");
    mySC.Open();
    string sql = @"SELECT * FROM RCA Where EmailID = @emailid;";
    using(var command = new SqlCommand(sql, mySC))
    {
        command.Parameters.Add("@emailid", txtEmail.Text);
        reader = command.ExecuteReader();
    }
    if(reader.HasRows)
    {
        //Email Exsist in the dataBase.
    }
    else
    {
        MessageBox("Incorrect!!");
    }
    mySC.Close();
}

S. Jerk · Answer 2 · 2016-01-18T10:03:02.370

0

At first you are Converting the text to int

Convert.ToInt32(txtBox1.Text);

But you don't do anything with the result!? like this for example:

int a = Convert.ToInt32(txtBox1.Text);

using Convert.toInt32() doesn't change anything in the textbox. It just takes the string of the text and returns it converted to int.

If you want to validate the entry by checking if it is a number, you could do this:

try{
int a = Convert.ToInt32(txtBox1.Text);
}
catch{
// error, txtBox1.Text could not be converted to int (it contains letters)
}

To ckeck if ticketID is in table make an sql statement like this:

cmd.Commandtext = "SELECT Count(*) FROM RCA WHERE ticketID = '" + txtBox.text + "'";

After that you execute it like a query and safe the result in a reader

SqlDataReader reader = cmd.ExecuteReader();

than you read the results from the reader and see if there was a match

...or you could just do it like Mohit Shrivastava says. Very nice and shiny! :D

edited Jan 18 '16 at 10:03

answered Jan 18 '16 at 09:48

S. Jerk

29
8

Fixed! Thank you I'm new to C#. My answer has still not been answered though. – Jan 18 '16 at 09:50
how do you want to validate the entry? Check if it is a number? – S. Jerk Jan 18 '16 at 09:51
@S.Jerk that line has the advantage of validating the input, since OP is concatenating it into their query as a string - at least the `Convert` line will throw an exception and avoid a potential SQL injection. – CompuChip Jan 18 '16 at 09:53
With sql. If the ticketID is in the sql table then the user will be able to type in txtbox2. – Jan 18 '16 at 09:53
Edited my post. although I still don't have a answer. I want my code to check if the TicketID is in the SQL table. – Jan 18 '16 at 10:01

Control for correct input with SQL in C#

2 Answers2