Why doesn’t this method work?

Isidro Padilla

I am continuing with my SMO learning and again I am blocked.

Something is missing in this method that I am not able to see.

The view I have previously created it without problems, but when I try creating the delete method sends me the following message:

Reference or object Not instantiated to an object.

I hope a charitable soul lends me a hand.

private void BtnBorrarVista_Click(object sender, EventArgs e)
{
try
{
if (MessageBox.Show("Está intentando borrar la Vista " + textBoxBorrarVista.Text + " ¿Está seguro de que quiere eliminarla?", "¡Atención!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
m_Server = new Server(cmbServerName.Text);
Database myNewDatabase = m_Server.Databases[cmbDbName.Text];
Microsoft.SqlServer.Management.Smo.View view = myNewDatabase.Views[textBoxBorrarVista.Text];
view.Drop();
MessageBox.Show("La Vista " + textBoxBorrarVista.Text + " se borró con éxito");
}
else
{
MessageBox.Show("Operación abortada. La Vista " + textBoxBorrarVista.Text + " no se ha borrado");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Isidro Padilla6y ago
Relative to Microsoft link example:

Yes, I have already seen this example and works "myView.Drop ();" if "myView.Create ();" has been previously executed , but what I want is to have it separately and this is what I don't get.

How can myview.Drop () be made to work, in the following example, after removing the line "myview.Create ();" ?
{
//Connect to the local, default instance of SQL Server.
Server srv;
srv = new Server();
//Reference the AdventureWorks2012 database.
Database db;
db = srv.Databases["AdventureWorks2012"];
//Define a View object variable by supplying the parent database, view name and schema in the constructor.
View myview;
myview = new View(db, "Test_View", "Sales");
//Set the TextHeader and TextBody property to define the view.
myview.TextHeader = "CREATE VIEW [Sales].[Test_View] AS";
myview.TextBody = "SELECT h.SalesOrderID, d.OrderQty FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID";
//Create the view on the instance of SQL Server.
myview.Create();
//Remove the view.
myview.Drop(); }
This is my question.

Why do you need to previously run myview.Create () if it already exists in order to ejecute myview.Dop()?

Also this example does not seem very logical, since it creates it and then deletes it. There is something that does not fit my logic.
Isidro Padilla6y ago
OK, here is the new code with the corrections, yours and mine.
Now View contains the name of the Table and the name of the view belonging to this table, as expected:
View = [Sales]. [MyView]
Everything is apparently correct, but now it fails on the line:
view.Drop ();
Send me the message:
Error dropping View 'Sales.myView`
Note:
The truth is that the relative operations Views are giving me a lot of war, the other methods I have solved with greater fortune, but I am spending a lot of time on this and, Googling, I can hardly find examples of how to delete it successfully.
private void BtnBorrarVista_Click(object sender, EventArgs e)
{
      try
      {
            if (MessageBox.Show("Está intentando borrar la Vista " + textBoxBorrarVista.Text + " ¿Está seguro de que quiere eliminarla?",                      "¡Atención!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
               {
                        if (!string.IsNullOrWhiteSpace(cmbServerName.Text))
                        {
                              m_Server = new Server(cmbServerName.Text);
                              if (m_Server != null && !string.IsNullOrWhiteSpace(cmbDbName.Text))
                              {
                                       Database myNewDatabase = m_Server.Databases[cmbDbName.Text];
                                       if (myNewDatabase != null)
                                       {
                                                //Microsoft.SqlServer.Management.Smo.View view = myNewDatabase.Views(textBoxBorrarVista.Text);
                                                Microsoft.SqlServer.Management.Smo.View view;
                                                view = new Microsoft.SqlServer.Management.Smo.View(myNewDatabase, textBoxBorrarVista.Text, "Sales");
                                            if (view != null)
                                                {
                                                         view.Drop(); MessageBox.Show("La Vista " + textBoxBorrarVista.Text + " se borró con éxito");
                                                }
                                        }
                                 }
                           }
                     }
                     else
                     {
                           MessageBox.Show("Operación abortada. La Vista " + textBoxBorrarVista.Text + " no se ha borrado");
                     }
            }
             catch (Exception ex)
            {
                   MessageBox.Show(ex.Message.ToString());
            }
}

Note:
If you have time I can send you the project, complete in a .rar, although it is a novice code it can save you time to see what I am actually doing as a learning experience.
Rajanikant Hawaldar6y ago
Below is about View Class, This is for your learning purpose https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/creating-altering-and-removing-views?view=sql-server-ver15 https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.view?view=sql-smo-140.17283.0
Isidro Padilla6y ago
OK, Im going ro check you new code.

Googling I have found a part of the error, but it still fails. I have changed the line causing the error to this one:
Microsoft.SqlServer.Management.Smo.View view = new Microsoft.SqlServer.Management.Smo.View (myNewDatabase, textBoxBorrarVista.Text, "Sales");
which returns "[Sales]. [myView]", that is, View = [Sales]. [myView]
But now it fails on the line:
view.Drop ();
Send me the message:
Error dropping View 'Sales.myView`
I'm lost.
Rajanikant Hawaldar6y ago
I corrected code check it again. Below code you was using square brackets [ ] this was wrong. Use only round brackets ( ) and check how many parameters Views takes and pass accordingly. Thanks. Microsoft.SqlServer.Management.Smo.View view = myNewDatabase.Views(textBoxBorrarVista.Text);// pass here required parameters.
Isidro Padilla6y ago
Thank you very much for tutorial link. You are very kind.
Isidro Padilla6y ago
Hi Rajanikant Hawaldar thank you very much for the improvements you have brought to my code.
I have tested the code with the improvements that you have incorporated and it continues to fail on the line:
----> Microsoft.SqlServer.Management.Smo.View view = myNewDatabase.Views[textBoxBorrarVista.Text];
Something is wrong with this line providing y "view = null". I hope you can find what is wrong, forgive me I do not see it.
I attach the code of the creation of this View that runs successfully:
private void BtnCrearVista_Click(object sender, EventArgs e)
{
      try
      {
            Database db;
            db = m_Server.Databases[cmbDbName.Text];
            Microsoft.SqlServer.Management.Smo.View myview;
            myview = new Microsoft.SqlServer.Management.Smo.View(db, textBoxCrearVista.Text, "Sales");
            myview.TextHeader = "CREATE VIEW [Sales]." + textBoxCrearVista.Text + " AS";
            myview.TextBody = "SELECT h.SalesOrderID, d.OrderQty FROM Sales.SalesOrderHeader AS h INNER JOIN             Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID";
            //Crear the view on the instance of SQL Server.
            myview.Create();
            MessageBox.Show("La vista de la 'Tabla' 'Sales' de " + cmbDbName.Text + " se creó satisfactoriamente ");
       }
         catch (Exception ex)
      {
            MessageBox.Show(ex.Message.ToString());
      }
}
Rajanikant Hawaldar6y ago
Cool, if you know the object is null then you should review code/ debug and check memory is assigned using new operator or not, even though if it is assigned if object is still null you have to debugg and analyze code, I appreciate self learning interest keep it up. I would like to suggest you to go through below tutorial. c# tutorial for beginners: https://www.youtube.com/playlist?list=PLAC325451207E3105
Isidro Padilla6y ago
Hi Rajanikant Hawaldar thank you very much for replying.
Yes, I know that it is a null object, but I am learning, I am self-taught and I do not reach the level to detect which line of code is wrong or if I am missing some other line to complete the code.

The data that I pass to you is the name of View
"textBoxBorrarVista.Text",
the server name "cmbServerName.Text",
and the name of the database "cmbDbName.Text".
I have reviewed it and cannot find I am able to find any error in the written code. Possibly I'm missing a line of code.
Rajanikant Hawaldar6y ago
Object is null so only your getting this error, while debugging mouse over on objects find which object is null and initialize null object at proper location