CODEWITHSUNDEEP
c#sqlstringobjectado.net
user3215839
user3215839

Reputation: 35

Parameter.addwithvalue function error

    public static bool dataMatch(string data, string tableName, string column)
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = new SqlConnection(CnnStr);//connection string has been applied to CnnStr


        cmd.CommandText = "SELECT * FROM [@tableName] WHERE [@column]=[@data]";
        cmd.Parameters.Add("@tableName",tableName);
        cmd.Parameters.AddWithValue("@column", (column as Object).ToString());
        cmd.Parameters.AddWithValue("@data", (data as Object).ToString());

        cmd.Connection.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            dr.Close();
            cmd.Connection.Close();
            return true;
        }
        dr.Close();
        cmd.Connection.Close();
        return false;
    }

HELLO

the error i get is:

SqlException was unhandled by user code invalid object name @tableName

i think the problem is that the args of 2nd parameter of the AddWithValue funcs are strings, and they have to be object type, so i tried to convert the 2nd string parameters of the addwithvalue func to Object or object but get the same errors

Upvotes: 0

Views: 2069

Answers (2)

Soner Gönül
Soner Gönül

Reputation: 98840

You can't parameterized your column names or table names.

You only parameterize your values. That's why you can't use @tableName and @column as a parameter. You can fix them specifying the table name and column name as part of the SQL.

Your code is a valid syntax for C#, but it is not a valid SQL.

It is a poor solution but if you really want to use them, take a look at dynamic SQL.

Also use using statement to dispose your SqlConnection, SqlCommand and SqlDataReader as well.

using(SqlConnection con = new SqlConnection(CnnStr))
using(SqlCommand cmd = con.CreateCommand())
{
    cmd.CommandText = "SELECT * FROM " + tableName + " WHERE " + column.ToString() + "=@data";
    cmd.Parameters.Add("@data", data.ToString());
    using(SqlDataReader dr = cmd.ExecuteReader())
    {
        //
    }
}

Upvotes: 4

Ishtiaq
Ishtiaq

Reputation: 1058

You can acheive this by the following method. 

 public static bool dataMatch(string data, string tableName, string column)
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = new SqlConnection(CnnStr);//connection string has been applied to CnnStr


        cmd.CommandText = "SELECT * FROM '"+tableName+"' WHERE [@column]=[@data]";

        cmd.Parameters.AddWithValue("@column", (column as Object).ToString());
        cmd.Parameters.AddWithValue("@data", (data as Object).ToString());

        cmd.Connection.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            dr.Close();
            cmd.Connection.Close();
            return true;
        }
        dr.Close();
        cmd.Connection.Close();
        return false;
    }

Concatenate table name in the query instead of passing as parmater.

Upvotes: -1

Related Questions