Object reference not set to an instance of an object - MySqlCommand

Question

I am making a application which should work with MySql database. I am getting this error, and I dont know why:

Object reference not set to an instance of an object

Here is my code:

public void rtMean()
    {
        MySqlConnection conect = con.ConnectToMySQL();
        MySqlDataReader reader;
        MySqlDataReader readerMean;

        // OVERENIE, CI UZ STLPEC PRIEMER EXISTUJE

        string query1 = "SELECT * FROM feture";
        MySqlCommand cmd1 = new MySqlCommand(query1, conect);
        reader = cmd1.ExecuteReader();

        for (int j=0; j < reader.FieldCount; j++)
        {
            if (reader.GetName(j) == "priemer")
            {
                break;
            }
            else
            {
                reader.Close();
                cmd1.Cancel();
                string queryAlterTable = "ALTER TABLE feture ADD COLUMN priemer FLOAT NOT NULL";
                MySqlCommand cmd = new MySqlCommand(queryAlterTable, conect); //here I am getting the error
                cmd.ExecuteScalar();
            }
        }

This should chceck if there is column "priemer" in one of my tables. If yes, it should end / break, and if no, my table should be altered with new column, named "priemer".

Thank you for advices.

EDIT: STACK - TRACE:

  at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()
  at WindowsFormsApplication1.ClusteringReady.rtMean() in    c:\Users\Martin\Desktop\CLUSTER\WindowsFormsApplication1\ClusteringReady.cs:line 43
  at WindowsFormsApplication1.Form1.clusterize_Click(Object sender, EventArgs e) in c:\Users\Martin\Desktop\CLUSTER\WindowsFormsApplication1\Form1.cs:line 130
  at System.Windows.Forms.Control.OnClick(EventArgs e)
  at System.Windows.Forms.Button.OnClick(EventArgs e)
  at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
  at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
  at System.Windows.Forms.Control.WndProc(Message& m)
  at System.Windows.Forms.ButtonBase.WndProc(Message& m)
  at System.Windows.Forms.Button.WndProc(Message& m)
  at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
  at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
  at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
  at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
  at    System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
  at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
  at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
  at System.Windows.Forms.Application.Run(Form mainForm)
  at WindowsFormsApplication1.Program.Main() in c:\Users\Martin\Desktop\CLUSTER\WindowsFormsApplication1\Program.cs:line 19
  at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
  at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
  at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
  at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
  at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
  at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
  at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
  at System.Threading.ThreadHelper.ThreadStart()

What is the stack trace of the exception?

adrianbanks
– adrianbanks

2014-04-13 21:14:59 +00:00
Commented Apr 13, 2014 at 21:14 — adrianbanks
– adrianbanks, Commented Apr 13, 2014 at 21:14

NeverHopeless · Accepted Answer · 2014-04-14 06:55:44Z

1

Ok based on the discussion below, you can run this query:

SELECT COUNT(*) as cnt FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'feture'
AND COLUMN_NAME = 'premier'

and if you get CNT = 0 (in first cell of the result set) then you have to run your ALTER TABLE query ONCE other wise it exists already. Still no need to run a for loop.

edited Apr 14, 2014 at 6:55

answered Apr 13, 2014 at 21:49

NeverHopeless

11.2k4 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Nemeth Over a year ago

I am getting this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT priemer FROM feture) THEN ALTER TABLE feture ADD COLUMN pr' at line 1

Steve · Accepted Answer · 2014-04-13 21:36:15Z

1

So if I understand correctly, if the first field is not named priemer you create a new column trying to close the reader. But you don't stop your loop and thus you execute the second loop, with the reader closed.

There is a better way to find out if your table contains or not the column required

bool found = false;
using(MySqlConnection cnn = new MySqlConnection("....................."))
{
    cnn.Open();
    DataTable dt = cnn.GetSchema("COLUMNS", new string[] {null,null,"feture", null});
    foreach(DataRow r in dt.Rows)
    {
        if(r["COLUMN_NAME"].ToString() == "priemer")
        {
            found = true;
            break;
        }
    }
    if(!found)
    {
        .. create your column here
    }
}

edited Apr 13, 2014 at 21:36

answered Apr 13, 2014 at 21:17

Steve

217k22 gold badges242 silver badges296 bronze badges

2 Comments

Martin Nemeth Over a year ago

So if I do not stop the loop with break, how do I do it? I would like to check the table col by col and when it finds column named "priemer" it will stop the loop

Steve Over a year ago

The example above avoids to retrive all records from your table when you need only the schema data. A part from the help of GetSchema method you should avoid to change the table where your reader is looping on. Not sure but it seems a dangerous thing to do. Just set a flag and change the table after the loop ends

Collectives™ on Stack Overflow

Object reference not set to an instance of an object - MySqlCommand

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related