none
How do I use SQL select where condition is equal to a variable? RRS feed

  • Question

  • Total newbie here. I have searched for a couple of hours but have not been able to come up with an answer. All examples I have found are for comparing data to literal values.

    I have an input box where I ask the user for a number. This number is saved in a string variable.

    Now I need to query my table and select the matching rows but the column is an integer.

    I've tried converting the string to integer and then compare but that doesn't work either. Error says the int variable name (pnumb) is not a valid column name.

    This is what I have so far:

                    string value = "";
                    if (Getuserinput.InputBox("Please Enter the Permit Number:", "(Last 6 digits only...)", ref value) == DialogResult.OK)
                    {

                        int pnumb; // convert answer to int??

                        pnumb = System.Convert.ToInt32(value);

                        SqlConnection conn = new SqlConnection();
                        conn.ConnectionString = @"Data Source=WIN10\SQLEXPRESS;";
                        conn.ConnectionString = conn.ConnectionString + "Initial Catalog=PLV;";
                        conn.ConnectionString = conn.ConnectionString + "User id=PLV;";
                        conn.ConnectionString = conn.ConnectionString + "Password=PLV;";
                        conn.Open();                                                      
                        SqlCommand command;
                        SqlDataReader reader;
                        string sql;

    ///////////////////////////////////// This is the problem

                        sql = "SELECT PrmtNumb, PrmtTNum FROM dbo.prmtmain AS prmtmain WHERE PrmtNumb = value";

    ////////////////////////////////////

                       command = new SqlCommand(sql, conn);
                       reader = command.ExecuteReader();

    Could someone please explain how to properly refer to and use a variable as part of a condition in SELECT WHERE syntax?

    Saturday, January 18, 2020 8:07 PM

Answers

  • https://csharp-station.com/Tutorial/AdoDotNet/Lesson06

    It should help you and you should use parametrized T-SQL to prevent SQL Injection attack. 

    https://www.hacksplaining.com/prevention/sql-injection
    • Edited by DA924x Saturday, January 18, 2020 8:53 PM
    • Marked as answer by J Vallee Saturday, January 18, 2020 9:03 PM
    Saturday, January 18, 2020 8:51 PM

All replies

  • https://csharp-station.com/Tutorial/AdoDotNet/Lesson06

    It should help you and you should use parametrized T-SQL to prevent SQL Injection attack. 

    https://www.hacksplaining.com/prevention/sql-injection
    • Edited by DA924x Saturday, January 18, 2020 8:53 PM
    • Marked as answer by J Vallee Saturday, January 18, 2020 9:03 PM
    Saturday, January 18, 2020 8:51 PM
  • Thank you. I would never have found this on my own.
    Saturday, January 18, 2020 9:04 PM