Passing array as parameter for sql query

Question

I couldn't find an answer for this questions no matter how much I tried. What I have so far is this:

The array of data is gathered and displayed with

readonly string[] values;
protected void btnAddClick(object sender, EventArgs e)
{
    List<string> values = new List<string>
    {
        txtActivitate.Text.ToString()
    };

    for (int i = 0; i < values.Count; i++)
    {

        Message.Text += values[i] + "<br />";

    }
}

and is working properly. Then I try a select with

protected void btnSelectClick(object sender, EventArgs e)
{
    foreach (string val in values)
    {
        using (SqlConnection conn = new SqlConnection(connString))
        {
            string sqlQuery = "SELECT * FROM tblActivitate t1 JOIN tblEvaris t2 ON t1.AID = t2.AID JOIN tblMasura t3 on t2.EID = t3.EID WHERE t1.Activitate = @Activitate ORDER BY Componenta, FactorRisc, FormaManifestare";
            using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@Activitate", val);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                conn.Open();
                DataSet ds = new DataSet();
                da.Fill(ds);
                DataList1.DataSource = ds.Tables[0];
                DataList1.DataBind();
                conn.Close();
            }
        }
    }
}

and I got error

System.NullReferenceException: 'Object reference not set to an instance of an object.' Local0 was null.
How it should be done?

Prasad Raveendran · Answer

Here's an updated version of your code to address the problem: public List values = new List (); protected void Page_Load(Object sender, EventArgs e) { if (!IsPostBack) { if (Session["MyList"] == null) { Session["MyList"] = new List (); } else { values = Session["MyList"] as List ; } } } protected void btnAddClick(object sender, EventArgs e) { if (!string.IsNullOrEmpty(txtActivitate.Text)) { values = Session["MyList"] as List ; // Retrieve the list from the session values.Add(txtActivitate.Text); // Add the new value to the list Session["MyList"] = values; // Update the list in the session foreach (string value in values) { Message.Text += value + " "; } txtActivitate.Text = string.Empty; txtActivitate.Focus(); } } Changes made: In the Page_Load method, it checks if the session variable MyList is null. If it's null, a new list is created and assigned to the session variable. If it's not null, it retrieves the existing list from the session. In the btnAddClick method, it retrieves the list from the session, adds the new value to this list, updates the session variable with the modified list, and then displays all the values in the updated list. This should prevent the duplication of values when the button is clicked repeatedly. Now, the list is properly retrieved from the session, and new values are added to it without doubling the existing values.

Marius Vasile · Answer

Got it work with private bool CheckValues(string checkString) { string[] stringToCheck = checkString.Split(' '); foreach (string word in stringToCheck) { if (values.Contains(word.ToLower())) { return true; } } return false; } protected void btnAddClick(object sender, EventArgs e) { if (!string.IsNullOrEmpty(txtActivitate.Text)) { var text = txtActivitate.Text.ToString(); values = Session["MyList"] as List ; // Retrieve the list from the session if (CheckValues(text)) { values = Session["MyList"] as List ; foreach (string value in values) { Message.Text += value + " "; } txtActivitate.Text = string.Empty; txtActivitate.Focus(); } else { values.Add(text); Session["MyList"] = values; foreach (string value in values) { Message.Text += value + " "; } txtActivitate.Text = string.Empty; txtActivitate.Focus(); } } }

Marius Vasile · Answer

Prasad, I tried your solution but it still double the data in List values and Session["MyList"]. I have to find a way to prevent adding doubled values, tried with if (!string.IsNullOrEmpty(txtActivitate.Text)) { values = Session["MyList"] as List ; // Retrieve the list from the session var text = txtActivitate.Text.ToString(); for (int i = 0; i but is not working, no data is saved

Marius Vasile · Answer

Thank you Prasad, I was trying to declare a public array of strings, populated with first method and use valies in the second. With the Session I tried to preserve values in the list and use it in the second method. Most probably values were not preserved due to post back. I try later today your solution but most likely will work

Prasad Raveendran · Answer

It seems like you've declared two separate variables named values within the same scope, causing confusion between the class-level variable and the local variable declared inside the btnAddClick method. The issue arises because the local variable inside the method is hiding the class-level variable. To resolve the "empty value" problem, you can differentiate between the local variable and the class-level variable by using different names. Here's the corrected code: readonly string[] classLevelValues; protected void btnAddClick(object sender, EventArgs e) { List localValues = new List { txtActivitate.Text.ToString() + "," }; // If you intended to use the class-level values, change the loop to iterate through classLevelValues for (int i = 0; i "; } } By differentiating the names between the class-level variable classLevelValues and the local variable localValues , you can avoid conflicts and ensure that the intended variable is used. Also, ensure that you're populating and iterating through the correct list based on your requirement (either the class-level variable or the local variable).

Marius Vasile · Answer

Well, I manage to have a work arround using a Session but now with every click of a button, the values added previously are doubled public List values = new List (); protected void Page_Load(Object sender, EventArgs e) { if (!IsPostBack) { Session["MyList"] = new List (); } } protected void btnAddClick(object sender, EventArgs e) { values = Session["MyList"] as List ; if (!string.IsNullOrEmpty(txtActivitate.Text)) { values.Add(txtActivitate.Text.ToString()); } for (int i = 0; i "; } txtActivitate.Text = string.Empty; txtActivitate.Focus(); Session["MyList"] = values; } What to do?

Marius Vasile · Answer

I did that already but no values are passed to second method. Full code below public List values = new List (); protected void btnAddClick(object sender, EventArgs e) { if (!string.IsNullOrEmpty(txtActivitate.Text)) { values.Add(txtActivitate.Text.ToString()); } for (int i = 0; i "; } } protected void btnSelectClick(object sender, EventArgs e) { using (SqlConnection conn = new SqlConnection(connString)) { string sqlQuery = "SELECT * FROM tblActivitate t1 JOIN tblEvaris t2 ON t1.AID = t2.AID JOIN tblMasura t3 on t2.EID = t3.EID WHERE t1.Activitate IN ({0}) ORDER BY Activitate"; string[] paramNames = values.Select((s, i) => "@val" + i.ToString()).ToArray(); string inClause = string.Join(",", paramNames); using (var cmd = new SqlCommand(string.Format(sqlQuery, inClause), conn)) { for (int i = 0; i

Jignesh Kumar · Answer

Change string array to list of string. I have updated my answer.

Marius Vasile · Answer

Thank you Jignesh, I implemented your solution and I have the following error

Jignesh Kumar · Answer

public List values = new List ; protected void btnAddClick(object sender, EventArgs e) { if(!string.IsNullOrEmpty(txtActivitate.Text)) { values.Add(txtActivitate.Text.ToString()) } for (int i = 0; i "; } } Please try above one it should work, you are creating new object of string array so existing values will read off.

Marius Vasile · Answer

The problem I have is that string values is not passed to select query. I modified a little the select method to protected void btnSelectClick(object sender, EventArgs e) { List values = new List { "Tehnician", "Inginer" }; using (SqlConnection conn = new SqlConnection(connString)) { string sqlQuery = "SELECT * FROM tblActivitate t1 JOIN tblEvaris t2 ON t1.AID = t2.AID JOIN tblMasura t3 on t2.EID = t3.EID WHERE t1.Activitate IN ({0}) ORDER BY Activitate"; string[] paramNames = values.Select((s, i) => "@val" + i.ToString()).ToArray(); string inClause = string.Join(",", paramNames); using (var cmd = new SqlCommand(string.Format(sqlQuery, inClause), conn)) { for (int i = 0; i and is working. But if I try to get parameters from readonly string[] values; protected void btnAddClick(object sender, EventArgs e) { List values = new List { txtActivitate.Text.ToString() + "," }; for (int i = 0; i "; } } I got empty values. Why?