Answered Sending multiple values to an SQL Query

giasone777

Active member
Joined
Jul 19, 2019
Messages
29
Programming Experience
1-3
Hello,

I would like to send multiple queries to an sql server. It works for one but I am trying to make it work for multiple values stored in an array which came from multiple selections from a listbox.

So right now @RealVms is determining the query, so for instance I would like that query to pull the values from within the array gotselections[num] so it would basically have the following effect

string selectSql = ("select Name, vmHost, Uid from db_owner.vms where UID Like @gotselections[0] and where UID like @gotselections[1] "); or higher depending on the number of selections from the listbox.

Please help - Thank-you in advance. Jason.

These are VMware objects stored in an sql database. At the end the query is sent to an asp grid. The following code does work but for only one query.

C#:
string hosty = vmtofind.Text;
String GetSelection = objectList.SelectedItem.Text;
string RealVms = "%" + GetSelection + "%";

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnection"].ConnectionString))
{
    con.Open();

    string selectSql = ("select Name, vmHost, Uid from db_owner.vms where UID Like @RealVms");
    SqlCommand cmd = new SqlCommand(selectSql, con);

    cmd.Parameters.AddWithValue("@RealVms", RealVms);

    SqlDataReader dr = cmd.ExecuteReader();

    GridView1.DataSource = dr;
    GridView1.DataBind();

    con.Close();
 
Last edited by a moderator:
Here's something I posted elsewhere some time ago:



Myself and others suggest with regularity that values be inserted into SQL statements using parameters rather than string concatenation. Using parameters is more secure, more readable, less error-prone and negates the need to escape single quotes in text values.

Using parameters can be a bit tricky with an IN clause though, e.g.[HIGHLIGHT="SQL"]SELECT * FROM MyTable WHERE ID IN (3, 10, 17)[/HIGHLIGHT]You cannot use a single parameter for the whole list, but if you don't know how many values will be in the list then you can't add the parameters easily.

The way to work around this is to use a combination of string concatenation and parameters. You build a list of parameter place holders using string concatenation, then add the corresponding parameters to the command.

As an example, assume you have a ListBox containing possible field values. If the user can select zero, one or more of the items in the list to filter the results you could do this:
C#:
SqlConnection connection = new SqlConnection("connection string here");
SqlCommand command = new SqlCommand();
StringBuilder query = new StringBuilder("SELECT * FROM MyTable");

switch (this.listBox1.SelectedItems.Count)
{
    case 0:
        break;
    case 1:
        query.Append(" WHERE MyColumn = @MyColumn");
        command.Parameters.AddWithValue("@MyColumn", this.listBox1.SelectedItem);
        break;
    default:
        query.Append(" WHERE MyColumn IN (");

        string paramName;

        for (int index = 0; index < this.listBox1.SelectedItems.Count; index++)
        {
            paramName = "@MyColumn" + index.ToString();

            if (index > 0)
            {
                query.Append(", ");
            }

            query.Append(paramName);
            command.Parameters.AddWithValue(paramName, this.listBox1.SelectedItems[index]);

        }

        query.Append(")");
        break;
}

command.CommandText = query.ToString();
command.Connection = connection;
 
Here's the code updated for today and for your specific case:
C#:
var connection = new SqlConnection("connection string here");
var command = new SqlCommand();
var query = new StringBuilder("SELECT Name, vmHost, UID FROM db_owner.vms");
var selectedIndices = objectList.GetSelectedIndices();

switch (selectedIndices.Length)
{
    case 0:
        break;
    case 1:
        query.Append(" WHERE UID LIKE @UID");
        command.Parameters.Add("@UID", SqlDbType.VarChar, 50).Value = $"%{objectList.SelectedItem.Text}%";
        break;
    default:
        for (var i = 0; index < selectedIndices.Length; index++)
        {
            var index = selectedIndices[i];
            var paramName = $"@UID{index}";

            query.Append($" {(index == 0 ? "WHERE" : "OR")} UID LIKE {paramName}");
            command.Parameters.Add(paramName, SqlDbType.VarChar, 50).Value = $"%{objectList.Items[index].Text}%";
        }

        break;
}

command.CommandText = query.ToString();
command.Connection = connection;
Note that I have used Add rather than AddWithValue, which is good practice, so you will have to change the data type and size of the parameters as required. Note also that I have used OR rather than AND. Surely you want to get records that match any selection rather than all selections.

Also, as a pet peeve of mine, I have to point out that you used both Uid and UID in your code for apparently the same column. It is just sloppy to do things like that. If someone reads your code and sees the same thing referred to in two different ways, they can legitimately ask whether you actually meant to refer to something else. Good code doesn't present such questions.
 
Thank-you so much I will try it on Monday - I haven't done any C# in 4 years so I am really rusty right now and even back then only did it sporadically, I am CSA just trying to help the company find their virtual machines from a database of 25,000 :)
 
Also just one more thing, I don't think I mentioned the listbox is an asp.net listbox so it has different properties than a c# listbox, not sure if you took that into consideration.
 
You never mentioned which project type it was for, nor should that be much of an issue for you.

Please try to amend what you were given and post back with what you've tried since.
 
Also just one more thing, I don't think I mentioned the listbox is an asp.net listbox so it has different properties than a c# listbox, not sure if you took that into consideration.
I originally assumed a WinForms ListBox but I realised after having written the code that you were using a GridView so it was Web Forms, so I took the time to change it. The principles are still the same either way though, so you should have been able to make the requisite changes yourself if you understood the principles.
 
So when I pasted your code into a command button 'index' was being underline in red so I made some changes which I will paste here, but then at run
I originally assumed a WinForms ListBox but I realised after having written the code that you were using a GridView so it was Web Forms, so I took the time to change it. The principles are still the same either way though, so you should have been able to make the requisite changes yourself if you understood the principles.


Sorry for the delay in getting back to you, very busy day yesterday, I was having issues getting your code to run, the word index was being underlined in red, so I had to make some changes to it removing the var from before index within the for loop (a local or parameter index cannot be declared etc....)- also it was telling me the word index was not defined, so I adding the int index, also had to add connection.open - I will post the changes - bottom line is, now it is sending it would seem all of the items in list box to the query rather that the selected boxes (although just choosing one selection does work)- I will ad some code after yours which I was able to successfully grab the selected items and applied it to an array from the list box with when I placed it under a command button as a test, not sure if will help in integrating it into your code -

Any more ideas would be greatly appreciated - Thank-you Jason.
C#:
// I added this because index was underlined in red
int index = objectList.Items.Count;



var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnection"].ConnectionString);

connection.Open();

var command = new SqlCommand();

var query = new StringBuilder("SELECT Name, vmHost, UID FROM db_owner.vms");

var selectedIndices = objectList.GetSelectedIndices();

switch (selectedIndices.Length)

{

case 0:

break;

case 1:

query.Append(" WHERE UID LIKE @UID");

command.Parameters.Add("@UID", SqlDbType.VarChar, 50).Value = $"%{objectList.SelectedItem.Text}%";

break;

default:

for (var i = 0; index < selectedIndices.Length; index++)

{

// had to remove var from index because underlined in red (a local or parameter index cannot be declared etc....)
index = selectedIndices[I];

var paramName = $"@UID{index}";

query.Append($" {(index == 0 ? "WHERE" : "OR")} UID LIKE {paramName}");

command.Parameters.Add(paramName, SqlDbType.VarChar, 50).Value = $"%{objectList.Items[index].Text}%";


}

break;

}

// my code that gets the selected items and puts them in the array listofselections[]
foreach (ListItem item in objectList.Items)
{
if (item.Selected)
{
listofselections[counter] = (item.Value);
counter++;
}
 
Last edited by a moderator:
Sorry, I must have edited index to i in one place and not others. This:
C#:
for (var i = 0; index < selectedIndices.Length; index++)
should have been this:
C#:
for (var i = 0; i < selectedIndices.Length; i++)
You really ought to know how to write a for loop and be able to fix a mistake like that for yourself though. Loops are one of the absolute fundamentals of programming in any language, so you shouldn't really be doing anything else if you don't know how to write a loop.
 
Ok I made that change, much appreciated - It is now telling me there is an error near where it says or

System.Data.SqlClient.SqlException
HResult=0x80131904
Message=Incorrect syntax near the keyword 'OR'.
Source=.Net SqlClient Data Provider
StackTrace:
<Cannot evaluate the exception stack trace>

I will enter the code as it now stands - the error is thrown when I select multiple items in the list box and click the button to execute the code.
You're right I should understand the basics better than I do - please excuse me. Than-you for all your help :)

Jason.



var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnection"].ConnectionString);

connection.Open();

var command = new SqlCommand();

var query = new StringBuilder("SELECT Name, vmHost, UID FROM db_owner.vms");

var selectedIndices = objectList.GetSelectedIndices();

switch (selectedIndices.Length)

{

case 0:

break;

case 1:

query.Append(" WHERE UID LIKE @UID");

command.Parameters.Add("@UID", SqlDbType.VarChar, 50).Value = $"%{objectList.SelectedItem.Text}%";

break;

default:

for (var i = 0; i < selectedIndices.Length; i++)

{

var index = selectedIndices;

var paramName = $"@UID{index}";

query.Append($" {(index == 0 ? "WHERE" : "OR")} UID LIKE {paramName}");

command.Parameters.Add(paramName, SqlDbType.VarChar, 50).Value = $"%{objectList.Items[index].Text}%";







}

break;

}
 
The code you gave is only sending the correct query to the grid when listbox items zero and one are selected see here- this is from debugging -


command.CommandText = "SELECT Name, vmHost, UID FROM db_owner.vms WHERE UID LIKE @UID0 OR UID LIKE @UID1"


When any other listbox items are selected the query becomes this


command.CommandText = "SELECT Name, vmHost, UID FROM db_owner.vms OR UID LIKE @UID20 OR UID LIKE @UID21" (note there is no where statement)

and then it thows an sql near or error

So there is something wrong with the way you are grabbing the index from the listbox I would imagine, let me know what you think.

Jason.
 
More i/index confusion I'm afraid. This:
C#:
    default:
        for (var i = 0; index < selectedIndices.Length; index++)
        {
            var index = selectedIndices[i];
            var paramName = $"@UID{index}";

            query.Append($" {(index == 0 ? "WHERE" : "OR")} UID LIKE {paramName}");
            command.Parameters.Add(paramName, SqlDbType.VarChar, 50).Value = $"%{objectList.Items[index].Text}%";
        }

        break;
should have been this:
C#:
    default:
        for (var i = 0; i < selectedIndices.Length; i++)
        {
            var index = selectedIndices[i];
            var paramName = $"@UID{index}";

            query.Append($" {(i == 0 ? "WHERE" : "OR")} UID LIKE {paramName}");
            command.Parameters.Add(paramName, SqlDbType.VarChar, 50).Value = $"%{objectList.Items[index].Text}%";
        }

        break;
I think I got them all right this time.

For future reference, as you can see, code is more readable when formatted properly. Please do so in future. Be sure to maintain proper indenting and line spacing in the snippets you post too.
 
Back
Top Bottom