Fix SDReader throwing exception when given SQL command with parameters in C# [duplicate]

Question

I am trying to create an MVC Razor Webpage on C# an in order to display some content I am trying to get some data from a SQL Table.

I am trying to create a SQL command that I can add parameters to in order to prevent SQL Injection attacks.

string cmdClientAccess = "SELECT * FROM @Table WHERE [User] = '@User'";
int i = 0;

foreach (var client in Clients) {

    using (SqlConnection sConnection = new SqlConnection(SqlConnectionString))
    {

        SqlCommand sUserAccess = new SqlCommand(cmdClientAccess);
        sUserAccess.Parameters.AddWithValue("@Table", ClientUserTables[i]);
        sUserAccess.Parameters.AddWithValue("@User", AccessingUser);

        sConnection.Open();

        using (SqlDataReader SDReader = sUserAccess.ExecuteReader())
        {
            while (SDReader.Read())
            {
                if (SDReader["Requirement2"].ToString() != "")
                {
                    List1.Add(client);
                }

                if (SDReader["Requirement2"].ToString() == "Yes")
                {
                    List2.Add(client);
                }
            }
        }

        sConnection.Close();
    }
    i++;
}

The problem is that as soon as the program reaches this line:

using (SqlDataReader SDReader = sUserAccess.ExecuteReader())

Visual Studio gives the following message:

System.InvalidOperationException: 'ExecuteReader: Connection property has not been initialized.'

'@User' should be @User. Table name alas can't be parameterised the way you are trying to. — mjwills
– mjwills, Commented Oct 14, 2019 at 1:05

MindingData · Accepted Answer · 2019-10-14 01:00:34Z

0

You haven't passed the connection to your command.

Change :

SqlCommand sUserAccess = new SqlCommand(cmdClientAccess);

To :

SqlCommand sUserAccess = new SqlCommand(cmdClientAccess, sConnection );

Further documentation at Microsoft for the SqlCommand object : https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand?view=netframework-4.8

answered Oct 14, 2019 at 1:00

MindingData

12.6k6 gold badges53 silver badges73 bronze badges

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

Collectives™ on Stack Overflow

Fix SDReader throwing exception when given SQL command with parameters in C# [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related