0

I'm a newbie when it comes to C#, but have some experience with C, Python and MATLAB. I wrote a simple C# program that takes in some user input and converts it into a (parameterized) SQL query. I've successfully converted the datetimepicker into a SQL query; however, I have another parameter (serial numbers) that the user would input into a textbox. They can enter multiple serial numbers, separated by commas. Once the user clicks on 'Submit', the SQL query is sent and the results displayed in a dataGridView.

It works with a single value (i.e. a single serial number), but when I try to put in multiple values, it doesn't work.

I've tried some suggestions like separating the textbox string into an array of values. 

    private DataTable GetResults()
    {

        DataTable dtResults = new DataTable();
        string connString = ConfigurationManager.ConnectionStrings["dbx"].ConnectionString;
        using (SqlConnection con = new SqlConnection(connString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {

                string[] numbers = textBox2.Text.Split(',');
                var parameters = new string[numbers.Length];

                for (int i = 0; i < numbers.Length; i++)
                {
                    parameters[i] = string.Format("@SN{0}", i);
                    cmd.Parameters.AddWithValue(parameters[i], numbers[i]);
                }

                cmd.CommandText = string.Format("SELECT [TestDate],[ParamName],[SerialNumber],[TestDataID],[MeasuredValue]," +
                    "[MaximumLimit],[MinimumLimit],[PassResult] FROM [dbo].[Device.ParametricTestResults] " +
                    "WHERE SerialNumber IN ({0}) " +
                    "AND (TestDate BETWEEN (@start) AND (@end)) " +
                    "AND PassResult = 1", string.Join(", ", parameters));


                cmd.Parameters.AddWithValue("@start", dateTimePicker1.Text);
                cmd.Parameters.AddWithValue("@end", dateTimePicker2.Text);
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                dtResults.Load(reader);
            }
        }
        return dtResults;
    }

And the 'Submit' button has the following code attached to it:

private void button12_Click(object sender, EventArgs e)
    {
        TestResultsdataGridView.DataSource = GetResults();
    }

Again, the datagridview should display entries for multiple serial numbers, but it only works for one.

Daniel Pavlovsky
  • 71
  • 2
  • 13
  • What is the SQL error you get?? – NicoRiff Jan 07 '19 at 17:46
  • 2
    Possible duplicate of [Pass Array Parameter in SqlCommand](https://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand) – Phil M Jan 07 '19 at 17:46
  • What datatype is `SerialNumber` in the DB? Very likely the SQL needs to convert them to text, for starters. Conversely, the `DateTimePicker.Value` need *not* be converted to text. – Ňɏssa Pøngjǣrdenlarp Jan 07 '19 at 17:49
  • It doesn't throw an error. When I input a single value (e.g. 130796) into the textbox and hit 'Submit', it returns the desired results; however, when I input multiple values, separated by commas (e.g. 130796, 131200) then nothing happens, i.e. the datagridview outputs nothing. I'm not exactly sure about the datatype of SerialNumber, but when I just manually code the query (e.g. "WHERE SerialNumber IN ('130796','131200') " +) then it works, i.e. it returns all the information for serial numbers 130796 and 131200. – Daniel Pavlovsky Jan 07 '19 at 18:10
  • 1
    The accepted answer of the linked duplicate shows you how to use IN with a parameterized query and multiple values. You'll have to split the string and use the resulting array as shown. – Phil M Jan 07 '19 at 20:20
  • Thanks Phil. I took your suggestion, and edited my original post with the working code. – Daniel Pavlovsky Jan 07 '19 at 21:16

0 Answers0