SQL Server & C# : not returning any results [duplicate]

Question

private void searchMulti(string searchType, string searchTerm)
    {
        {
            {
                var query = "";
                cb_Surname.Items.Clear();
                txt_patient_search.Clear();
                if (patient_NHSID.Equals(null) != true)
                {
                    pbar_search.Value = 2;
                    var connectionString = Settings.Default.CMTA_DBConnectionString;
                    using (var con = new SqlConnection(connectionString))
                    {
                        if (searchType != "NHSID")
                        {
                            query = @"SELECT * FROM Patient WHERE @p2 = '@p1' ";
                        }
                        else
                        {
                            query = @"SELECT * FROM Patient WHERE @p2 = @p1";
                        }

                        using (var qry_search = new SqlCommand(query))
                        {
                            qry_search.Connection = con;
                            qry_search.Parameters.Add("@p1", SqlDbType.VarChar).Value = searchTerm;
                            qry_search.Parameters.Add("@p2", SqlDbType.VarChar).Value = searchType;

                            con.Open();
                            qry_search.ExecuteNonQuery();
                            int firstIteration = 0;

                            using (var rdr = qry_search.ExecuteReader())
                            {
                                if (rdr.HasRows)
                                {
                                    //Found Valid Patient Event
                                    pbar_search.Value = 6;
                                    pbox_tick.Show();
                                    foundValidPatient = true;
                                    ////////////////////////////

                                    while (rdr.Read())
                                    {
                                        if (firstIteration == 0)
                                        {
                                            pbar_search.Value = 8;
                                            cb_Surname.Text = rdr.GetInt64(0) + " - " + rdr.GetString(1) + " - " +
                                                              rdr.GetString(2);
                                            firstIteration = 1;
                                        }
                                        cb_Surname.Items.Add(rdr.GetInt64(0) + " - " + rdr.GetString(1) + " - " +
                                                             rdr.GetString(2));
                                    }
                                }
                                else
                                {
                                    //Patient Not Found
                                    pbox_cross.Show();
                                    patientSelected = false;
                                    foundValidPatient = false;
                                }
                                con.Close();
                            }
                        }
                    }
                }
                else
                {
                    MessageBox.Show("Please Enter Valid Text");
                    pbar_search.Value = 0;
                    pbox_cross.Show();
                }
            }
        }
    }

The method above isn't working. It should query the SQL Server database for a user entered term (such as textbox value) and query it for a searchtype (firstname) however when debugging the SQL query is executed but no rows are returned.

If I run the command without the parameters and insert actual values i.e (WHERE FirstName = 'Alan') it works perfectly.

What have I done incorrectly with this SQL query?

query = @"SELECT * FROM Patient WHERE @p2 = '@p1' ";

Many thanks!

Is column name really @p2? Because you did '@p1'. = 'ALan' is and existing record where as '@p1' is a column name if i'm not mistaken. — P. Pat
– P. Pat, Commented Mar 30, 2017 at 2:46
No the column name for example should be "FirstName" so it should convert to WHERE @p2 = @p1 ; (p1 = alan, p2= firstname) — Jon0149
– Jon0149, Commented Mar 30, 2017 at 2:48
@Jon: Prepared statements don't work like that. You can't dynamically choose the column you search against like this. You aren't getting any results because in the result SQL statement, it will look like you're comparing two string values (WHERE 'FirstName' = 'Alan) which is always false. — Cᴏʀʏ
– Cᴏʀʏ, Commented Mar 30, 2017 at 2:53
@Cory So how do i set it to read as FirstName like a columnname not 'FirstName' as a string? — Jon0149
– Jon0149, Commented Mar 30, 2017 at 2:55
Probably what you need is query = @"SELECT * FROM Patient WHERE " + searchType +" = @p1"; or query = String.Format("SELECT * FROM Patient WHERE {0} = @p1", searchType). The table name should be concatenated within query instead of using string comparison by passing table name as parameter value. — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented Mar 30, 2017 at 2:55

Jon0149 · Accepted Answer · 2017-03-30 03:02:58Z

0

Wrong: query = @"SELECT * FROM Patient WHERE @p2 = '@p1' ";

(Answer) Right: query = @"SELECT * FROM Patient WHERE " + searchType +" = @p1";

Answered by : – Tetsuya Yamamoto

answered Mar 30, 2017 at 3:02

Jon0149

131 gold badge1 silver badge5 bronze badges

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

3 Comments

Cᴏʀʏ Over a year ago

Please keep in mind my comment above about SQL injection. If searchType comes from an input that could be spoofed (text box, web request, etc.), you need to sanitize it first.

P. Pat Over a year ago

@Cory does it also cover combobox.selectedvalue?

Jon0149 Over a year ago

@Cory because their is no user text input (they select from a pre-determined combo box value) there is no chance of custom inputs for SQL injection

littlerufe · Accepted Answer · 2017-03-30 03:06:45Z

0

You can't pass a column name in as a parameter, but what you can do is add it to your query string explicitly. You also might consider putting square brackets around the column name in case someone has a column name that is also a SQL keyword.

I have two examples of formatting a string with your column name in [] below:

if (searchType != "NHSID")
{
    query = $@"SELECT * FROM Patient WHERE [{searchType}] = '@p1'";
}
else
{
    query = string.Format(@"SELECT * FROM Patient WHERE [{0}] = @p1", searchType);
}

answered Mar 30, 2017 at 3:06

littlerufe

37.2k5 gold badges33 silver badges48 bronze badges

Collectives™ on Stack Overflow

SQL Server & C# : not returning any results [duplicate]

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related