CODEWITHSUNDEEP
c#sql-server
ruthoverboard
ruthoverboard

Reputation: 41

Invalid column name 'master', while trying to execute a query in C#

I'm trying to get all the backups that I have from all the databases in my Local Server. I found this SQL code:

select a.backup_set_id, a.server_name, a.database_name, a.name, a.user_name, a.position, a.software_major_version, a.backup_start_date, backup_finish_date, a.backup_size, a.recovery_model, b.physical_device_name 
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b on a.media_set_id = b.media_set_id 
where a.database_name = 'AdventureWorksLT2008' order by a.backup_finish_date desc

In SQL Server it returns all the backups that I have, but when I try to use it in my app in Visual Studio it gives me this error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Invalid column name 'master'. (Master in one of the databases).

This is my code:

 private void listBackUps()
        {
            foreach (string str in listaDB)
            {
                string query1 = "select a.backup_set_id, a.server_name, a.database_name, a.name, a.user_name, a.position, a.software_major_version, a.backup_start_date, backup_finish_date, a.backup_size, a.recovery_model, b.physical_device_name ";
                string query2 = query1 + "from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b on a.media_set_id = b.media_set_id ";
                string query3 = query2 + "where a.database_name = " + str + " order by a.backup_finish_date desc";
                SqlCommand cmd = new SqlCommand(query3, connection);
                cmd.ExecuteNonQuery();
                cbBackUps.Items.Add(new ComboBoxItem { Content = str + " BackUp" });

            }

        }

cbBackUps is a ComboBox where I want to stored all the backups.

Thank you!

Upvotes: 1

Views: 487

Answers (1)

BilalAhmed
BilalAhmed

Reputation: 191

There are few things to notice.

Please try to use parameterized query in c# Kindly review

Second if you are using inline query then you must use String Builder for Constructing Queries Kindly review

Replace

where a.database_name = " + str + " order by

with

where a.database_name = '" + str + "' order by

Because SQL is treating your " + str +" as column name.

Upvotes: 1

Related Questions