How to use multiple parameters in a SQL queries

Question

I recently came across a statement on how to prevent SQL injection, so I changed my code to this (commented out are the old codes):

nameE = txtName.Text;

//sqlCode = "SELECT * FROM [db].[dbo].[TablePDFTest] WHERE [name] = '" + nameE + "'";
sqlCode = "SELECT * FROM [db].[dbo].[TablePDFTest] WHERE [name] = @name";

using (SqlCommand command = new SqlCommand(sqlCode, Conn))
{
      //command.CommandType = CommandType.Text;
      command.Parameters.AddWithValue("name", nameE);

      using (reader = command.ExecuteReader())
      {
        // some action goes here...
      }
 }

How can I do the same with multiple parameters?

My code is this where I am using as a function padding the two parameters as a variable from another function:

public void writeData(string k, string c)
{
    Conn = new SqlConnection(cString);
    Conn.Open();

    //MessageBox.Show(k);
    //MessageBox.Show(c);

    var pdfPath = Path.Combine(Server.MapPath("~/PDFTemplates/fw9.pdf"));

    // Get the form fields for this PDF and fill them in!
    var formFieldMap = PDFHelper.GetFormFieldNames(pdfPath);

    //if more than multiple entries, verify by name and the last four ssn
    //sqlCode = "SELECT * FROM [db].[dbo].[TablePDFTest] WHERE [name] = '" + k + "' AND [ssn3] = " + c + "";
    sqlCode = "SELECT * FROM [db].[dbo].[TablePDFTest] WHERE [name] = @name2 AND [ssn3] = @ssnnum";
    //MessageBox.Show("" + sqlCode.ToString());

    using (SqlCommand command = new SqlCommand(sqlCode, Conn))
    {
        //command.CommandType = CommandType.Text;
        command.Parameters.AddWithValue("name2", k);
        command.Parameters.AddWithValue("ssnnum", c);

        using (reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                if (reader.Read())
                {
                    MessageBox.Show(reader.GetValue(0).ToString());
                    /*formFieldMap["topmostSubform[0].Page1[0].f1_01_0_[0]"] = reader.GetValue(0).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].f1_02_0_[0]"] = reader.GetValue(1).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].f1_04_0_[0]"] = reader.GetValue(2).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].f1_05_0_[0]"] = reader.GetValue(3).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].f1_07_0_[0]"] = reader.GetValue(4).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].social[0].TextField1[0]"] = reader.GetValue(5).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].social[0].TextField2[0]"] = reader.GetValue(6).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].social[0].TextField2[1]"] = reader.GetValue(7).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].social[0].TextField2[2]"] = reader.GetValue(8).ToString();
                    formFieldMap["topmostSubform[0].Page1[0].social[0].TextField2[3]"] = reader.GetValue(9).ToString();*/
                }
            }
        }
    }

    // Requester's name and address (hard-coded)
    //formFieldMap["topmostSubform[0].Page1[0].f1_06_0_[0]"] = "Medical Group\n27 West Ave\nPurchase, NY 10577";

    //var pdfContents = PDFHelper.GeneratePDF(pdfPath, formFieldMap);

    //PDFHelper.ReturnPDF(pdfContents, "Completed-W9.pdf");
}

enter image description here

Parameters is a collection you can add more than one parameter to it. — Martin Smith
– Martin Smith, Commented May 30, 2014 at 14:27
+ in mysql like that i have never seeen such i always saw and see infact . for that — M.chaudhry
– M.chaudhry, Commented May 30, 2014 at 14:27

Horaciux · Accepted Answer · 2014-05-30 17:45:20Z

5

You can add parammeter as you did before. This is how your code will loke like:

sqlCode = "SELECT * FROM [db].[dbo].[TablePDFTest] WHERE [name] = @name AND [ssn3] =@ssn3";

using (SqlCommand command = new SqlCommand(sqlCode, Conn))
{
      //command.CommandType = CommandType.Text;
      command.Parameters.AddWithValue("@name", nameE);
      command.Parameters.AddWithValue("@ssn3", c);

      using (reader = command.ExecuteReader())
      {
        // some action goes here...
      }
 }

edited May 30, 2014 at 17:45

answered May 30, 2014 at 14:29

Horaciux

6,5152 gold badges26 silver badges45 bronze badges

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

20 Comments

SearchForKnowledge Over a year ago

command.CommandType = CommandType.Text should be commented out?

SearchForKnowledge Over a year ago

My query is coming out empty :/

pseudocoder Over a year ago

This question is related to stackoverflow.com/questions/23956070/…

Liath Over a year ago

@SearchForKnowledge try running the query directly and make sure the values you enter match results

Horaciux Over a year ago

@SearchForKnowledge the only difference I see, is that before you was using variable c as number (no ' ' in string concatenation). look at parameters recived.

|

Collectives™ on Stack Overflow

How to use multiple parameters in a SQL queries

1 Answer 1

20 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

20 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related