Parameterized query in C# OleDb. Incomplete query issue

Question

I'm having trouble with table names used as parameters in OleDb. As I looked through forums with similar questions, I got that it's actually impossible to use parameters in that fashion. But there wasn't given any solution to this problem. Of course, I can use actual table names, but there are lots of them, so it would be easier to process necessary tables separately. And I tried to use just string concatenation using dynamic sql which leads to SQL injection problems. So, here's my code

foreach (String Data in tablelist)
        {
            if (Data == "Harvard")
            {
               cmd.Parameters.Add("@tablename", OleDbType.Char);
               cmd.Parameters["@tablename"].Value = Data;
               cmd1.Parameters.AddWithValue("@University", Data);
               cmd.CommandText = "insert into @tablename ([Firstname],[Lastname]) values (?,?)";
            }
        }

Given that I cannot use parameter in INSERT INTO statement which command I should use not having troubled Command. How can this difficulty be overcome. Thanks in advance

Answer 1

You will have to concatenate the SQL and the table name, like:

cmd.CommandText = "insert into " + Data + " ([Firstname],[Lastname]) values (?,?)";