Mr Rubix Mr Rubix - 4 months ago 39
SQL Question

c# MySqlCommand.Parameters.AddWithValue, parameters to express a table, resulted command have a single quote added

my problem is that two undesired single quote appear in my command when i use AddWithValue.
This is how i call my function:

string sqlTableName = "the_table_i_want";
DataTable myDataTable = SQLcom.SelectLastValueInTab(sqlTableName);


My Function;

public static DataTable SelectLastValueInTab(string TableName)
{
using (MySqlConnection con = new MySqlConnection(ConnStr))
{
using (MySqlCommand myCommand = new MySqlCommand("SELECT * FROM @TabName ORDER BY id DESC LIMIT 1", con))
{
myCommand.Parameters.AddWithValue("@TabName", TableName);
try
{
con.Open();
MySqlDataReader testreader = myCommand.ExecuteReader();


The Resulted command is :

"SELECT * FROM 'the_table_i_want' ORDER BY id DESC LIMIT 1"


And is suppose to be :

"SELECT * FROM the_table_i_want ORDER BY id DESC LIMIT 1"


It cause the program to crash beacause i have:

a syntax error near ''the_table_i_want' ORDER BY id DESC LIMIT 1'


PS : if i don't use AddWithValuechange and change

@TabName for the_table_i_want


in the MySqlCommand it works perfectly !

Thanks a lot !

Daniel G.B

Answer

As the comments suggest, You cannot use parameters to express a table or field name. Alternative approach would be appending the table name to the query.

    public static DataTable SelectLastValueInTab(string TableName)
        {
            using (MySqlConnection con = new MySqlConnection(ConnStr))
            {
                string qry = "SELECT * FROM" + TableName + "ORDER BY id DESC LIMIT 1";
                using (MySqlCommand myCommand = new MySqlCommand(qry, con))
                {
                    try
                    {
                        con.Open();
                        MySqlDataReader testreader = myCommand.ExecuteReader();