CODEWITHSUNDEEP
c#sql-serverdatabase
B. Baxter
B. Baxter

Reputation: 165

Visual Studio C# and Databases

I'm working on a small school assignment where we have to create a simple form that connects to a database and we run commands that add/delete/modify data in the said database using buttons on the Windows form.

However, I'm struggling with the delete part.

Essentially I want to delete all of the data in 2 tables Customer and Order - but for some reason I'm getting an error when I click the button:

Incorrect syntax near the keyword 'Database.Order;'

in the below code.

private void deleteAll_Click(object sender, EventArgs e)
{
    string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\NOTIMPORTANT\Desktop\MD3\MD3\Database.mdf;Integrated Security=True";

    SqlConnection SqlCon = new SqlConnection(connectionString);

    const string SqlStrDeleteCustomer = "DELETE FROM Database.Customer;";
    const string SqlStrDeleteOrder = "DELETE FROM Database.Order;";

    SqlCommand SqlCom = new SqlCommand();

    try
    {
        SqlCon.Open();
        SqlCom.Connection = SqlCon;
        SqlCom.CommandText = SqlStrDeleteCustomer;

        SqlCom.ExecuteNonQuery(); //Says the error is line 103 which is this.

        SqlCom.CommandText = SqlStrDeleteOrder;
        SqlCom.ExecuteNonQuery();

        MessageBox.Show("All data has been deleted.");
        SqlCon.Close();
    }
    catch (System.Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

I can't seem to understand where the error is.

Any help would be appreciated.

Thank you.

Upvotes: 0

Views: 282

Answers (4)

STLDev
STLDev

Reputation: 6174

The only way to specify a table using just the database name and the table name is to use a syntax like this: AdventureWorks..Customer, which actually specifies the Customer table from the default schema of the database AdventureWorks.

The queries you're writing in your C# code are in T-SQL format, which is the query syntax that is native to SQL Server.

When referring to database table names in a T-SQL query, it can be done in any of the following ways:

  • If you use just the table name, for example, Customer, this references the table Customer in the default schema of the current database (which was specified in your connection string).
  • If you specify schema name and the table name, for example, dbo.Customer, this references a table within a specific schema within the current database.
  • If you specify the database name, the schema name, and the table name, for example, AdventureWorks.dbo.Customer, this references a table contained within a completely different database.
  • If you specify the linked server name, the database name, the schema name, and the table name, for instance, ProdServer.AdventureWorks.dbo.Customer, this references the Customer table in the dbo schema of the Adventureworks database on the SQL Server specified by the Linked Server reference named "ProdServer"

Upvotes: 1

Nitin S
Nitin S

Reputation: 7601

as you are using reserved word order in your query, Update your commands as follows

const string SqlStrDeleteCustomer = "DELETE FROM [Database].[dbo].[Customer];";
const string SqlStrDeleteOrder = "DELETE FROM [Database].[dbo].[Order];";

or

const string SqlStrDeleteCustomer = "DELETE FROM [Customer];";
const string SqlStrDeleteOrder = "DELETE FROM [Order];";

Upvotes: 1

jinglyhk
jinglyhk

Reputation: 13

Instead of using Delete from Database.order, use DELETE from ORDER and this should work.

The name of the database is already passed in the connection string.

Upvotes: 0

Rion Williams
Rion Williams

Reputation: 76597

Your existing Connection String is already specifying the default database to use (inferred from the Database.mdf), so it's likely that the query upon seeing Database is assuming it to be a schema and causing it to blow up.

You could explicitly specify your database using the Initial Catalog property in your connection string:

string connectionString = "...Initial Catalog=YourDatabaseName...";

You should be able to adjust your statements to use either the table name directly or preface them with the schema (e.g. dbo.TableName) as seen below:

const string SqlStrDeleteCustomer = "DELETE FROM Customer;"; // DELETE FROM dbo.Customer;
const string SqlStrDeleteOrder = "DELETE FROM Order;";       // DELETE FROM dbo.Order

Upvotes: 2

Related Questions