CODEWITHSUNDEEP
c#
user3107343
user3107343

Reputation: 2299

Must declare the table variable "@".

I tried copy data from DB1 to DB2.But I get error : Must declare the table variable "@TableName".

What is wrong this code ?

Function :

  public void TransferData()


    {
        SqlConnection source = new SqlConnection(strConnectDB1);
        SqlConnection destination = new SqlConnection(strConnectDB2);


        source.Open();
        destination.Open();

        SqlCommand cmd= new SqlCommand("SELECT * FROM @TableName", source);

        cmd.Parameters.AddWithValue("@TableName", listbox1.SelectedItem.ToString());
        SqlDataReader reader = cmd.ExecuteReader();


        SqlBulkCopy bulkData = new SqlBulkCopy(destination);

        bulkData.DestinationTableName = listbox1.SelectedItem.ToString();

        bulkData.WriteToServer(reader);

        .
        .


    }

Button :

    private void button5_Click(object sender, EventArgs e)
    {
        CreateTableName();

        TransferData();

    }

Upvotes: 1

Views: 3703

Answers (4)

Sai Avinash
Sai Avinash

Reputation: 4753

May be you can try this:

public void TransferData()

{

    string strTableName=string.empty;
    SqlConnection source = new SqlConnection(strConnectDB1);
    SqlConnection destination = new SqlConnection(strConnectDB2);


    source.Open();
    destination.Open();

    SqlCommand cmd= new SqlCommand("SELECT * FROM"+strTableName, source);

    strTableName=listbox1.SelectedItem.ToString());
    SqlDataReader reader = cmd.ExecuteReader();


    SqlBulkCopy bulkData = new SqlBulkCopy(destination);

    bulkData.DestinationTableName = listbox1.SelectedItem.ToString();

    bulkData.WriteToServer(reader);

    .
    .


}

Hope this helps..

Upvotes: 0

Christian Hayter
Christian Hayter

Reputation: 31071

The table name in a SQL FROM clause is not an expression, and therefore cannot be sent in a parameter.

You need to construct the SQL string manually, e.g.

SqlCommand cmd = new SqlCommand(string.Format("SELECT * FROM {0}", listbox1.SelectedItem), source);

I strongly recommend that you delimit the table name to prevent SQL injection attacks.

SqlCommand cmd = new SqlCommand(string.Format("SELECT * FROM {0}", "[" + listbox1.SelectedItem.ToString().Replace("]", "]]") + "]"), source);

Upvotes: 7

Ondrej Svejdar
Ondrej Svejdar

Reputation: 22084

You can't use @Parameter syntax for tables; best way how to do that is to close table name into [] and check if table name doesn't contain [] (sql injection precaution)

  if (listbox1.SelectedItem.Contains("[") 
     || listbox1.SelectedItem.Contains("]") {
     throw new Exception("Invalid table name");
  }

  SqlCommand cmd= new SqlCommand(string.Format(
    "SELECT * FROM [{0}]",listbox1.SelectedItem), source);

Upvotes: 4

Mez
Mez

Reputation: 4726

Try the following; Replace your SqlCommand code to this

  SqlCommand cmd= new SqlCommand("SELECT * FROM " + listbox1.SelectedItem.ToString(), source);

And remove the line where you are adding the parameter. It should work like this. You cannot use @Parameter syntax for tables.

Upvotes: 1

Related Questions