Visual Studio Server Explorer show table data is blank

Question

I am working on a Winforms c# program with a service-based database. I have created a table called Books which is empty and currently has no records.

Using some code, I am inserting into the table values. For some reason, when clicking Show Table Data, it still shows as blank despite me having added a record.

I checked to see if the record was there (but hidden) by using a DataGridView with the source of its data being the Books table. I could see the record had been created, but for some reason is not showing in the Server Explorer's Show Table Data view.

Here is my code for inserting a new record into the table:

string query = "INSERT INTO Books (ISBN, Title, Authors, Publishers, Genre, Page_Count, Quantity) VALUES (@isbn, @title, @authors, @publishers, @genre, @page_count, @quantity)";
string connectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\LMSDB.mdf;Integrated Security=True";

// Establish connection with database
using (SqlConnection connection = new SqlConnection(connectionString))
{
    // Avoid SQL injection using parameters. Replace them with their real values
    SqlCommand command = new SqlCommand(query, connection);
    command.Parameters.AddWithValue("@isbn", isbn);
    command.Parameters.AddWithValue("@title", title);
    command.Parameters.AddWithValue("@authors", authors);
    command.Parameters.AddWithValue("@publishers", publishers);
    command.Parameters.AddWithValue("@genre", genre);
    command.Parameters.AddWithValue("@page_count", pageCount);
    command.Parameters.AddWithValue("@quantity", quantity);

    try
    {
        connection.Open();
        if (command.ExecuteNonQuery() == 1)
        {
            // 1 Row affected. Success
            Console.WriteLine("Book added to database successfully");
            ClearControlValues();
            
        }
        
    }
    catch (Exception ex)
    {
        MessageBox.Show("An error occured:\n" + ex.Message);
    }
    finally
    {
        connection.Close();
    }
}

As you can see, the Show Table Data view is appearing blank, despite me knowing that there is a record as I can see it on a DataGridView

Is there something I'm missing as to why the record isn't appearing?

EDIT: Solved thanks to Steve who pointed out that the Server Explorer and my code had different connection strings. Having changed these around, I now have the intended result.

Because you use DataDirectory in code to point to your database and Server Explorer uses another connectionstring pointing to a different database. See https://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails — Steve, Aug 16 '21 at 21:39

score 0 · Answer 1 · answered Aug 17 '21 at 01:33

I made a test with your code, and data can be inserted successfully.

As Steve said, the problem lies in your connection string. And you can find your connectionstring by following steps:

1.Right-click on the connection name and select Properties.

2.There is an item called Connection String in the Properties window. As follows:

Daniel Zhang

Visual Studio Server Explorer show table data is blank

1 Answers1