After reading in an excel-sheet (to transferTable), I want to add that data to a new table (destinationTable) using SqlBulkCopy, but I'm getting the error:
Cannot access destination table 'test'
I've tried using the default tablename and using square brackets, but that didn't work.
Any suggestions?
private void writeToDBButton_Click(object sender, EventArgs e) {
MakeTable();
destinationTable.TableName = "test";
testDBDataSet.Tables.Add("test");
// Connects to the sql-server using Connection.cs
SqlConnection connection = Connection.GetConnection();
using (connection) {
connection.Open();
// Uses SqlBulkCopy to copy the data from our transferTable to the destinationTable
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) {
bulkCopy.DestinationTableName = destinationTable.TableName;
try {
// Write from the source to the destination.
bulkCopy.WriteToServer(transferTable);
this.dataGridView2.DataSource = destinationTable;
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
connection.Close();
}
}
}
private void saveDBButton_Click(object sender, EventArgs e) {
this.Validate();
this.usersBindingSource.EndEdit();
this.tableAdapterManager.UpdateAll(this.testDBDataSet);
}
private void MakeTable() {
for (int counter = 0; counter < columns; counter++) {
DataColumn dummy = new DataColumn();
dummy.DataType = System.Type.GetType("System.Double");
destinationTable.Columns.Add(dummy);
}
}
My issue was a bit different, turns out my table name was a reserved keyword in SQL so I had to do the following:
bulkCopy.DestinationTableName = $"{schema}.[{tableName}]";
Where schema
is the target schema and tableName
the target table name
From the documentation
DestinationTableName is a three-part name [database].[owningschema].[name]. You can qualify the table name with its database and owning schema if you choose. However, if the table name uses an underscore ("_") or any other special characters, you must escape the name using surrounding brackets as in ([database].[owningschema].[name_01])
Check that user that connects to db has
GRANT ALTER ON [dbo].[TABLE_XXX] TO [appuser]
as suggested in answer by Jhilden on MSDN forum.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With