Click here to Skip to main content
14,923,486 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to add a total of 11 records in my SQL. the consist of 4 columns, so a total of 44 blocks.

so what I have is information in the form of

Name,IPAdress,Port,Tag

I am just trying to load in the Name column for a test and I keep getting "connection must be valid and open"

I have tested the MySQL connection string and the Query just by sending one down the pipe and it works, its when I try to use a for loop to send them one after another

below is my "on press" function of the forms

What I have tried:

C#
private void button1_Click(object sender, EventArgs e)
{
   Globals.camera1Name = cameraName1.Text; Globals.camera1IP = cameraIP1.Text;  
   Globals.camera1Port = cameraPort1.Text;
   Globals.camera2Name = cameraName2.Text; Globals.camera2IP = cameraIP2.Text;  
   Globals.camera2Port = cameraPort2.Text;
   Globals.camera3Name = cameraName3.Text; Globals.camera3IP = cameraIP3.Text;  
   Globals.camera3Port = cameraPort3.Text;
   Globals.camera4Name = cameraName4.Text; Globals.camera4IP = cameraIP4.Text; 
   Globals.camera4Port = cameraPort4.Text;

   Globals.userName = newUserName.Text; Globals.userPassword = newUserPassword.Text;

   Globals.wonderWareIP = wonderWareIP.Text; 
   Globals.wonderWarePort = wonderWarePort.Text; 
   Globals.wonderWareTag = wonderWareTag.Text;       

   //string command = "UPDATE `james_hardie`.`config` SET `system_name`='" +  Globals.printer1Name + "', `ip_address`='" + Globals.printer1IP + "', `port`='" + Globals.printer1print + "', `cartridges`='" + Globals.printer1Cart + "' WHERE `idconfig`='1';";

   //MySqlCommand excute = new MySqlCommand(command, Globals.hardie);
   MySqlDataReader myReader;

   try
   {
      Globals.hardie.Open();

      for (int x = 0; x < 11; x++)
      {
         string command = "UPDATE `james_hardie`.`config` SET `system_name`='" + Globals.printerNames[x] + "' WHERE `idconfig`='" + (x + 1) + "';";
         MySqlCommand excute = new MySqlCommand(command, Globals.hardie);
         myReader = excute.ExecuteReader();
         Globals.hardie.Close();
      }
   }
   catch (Exception ex)
   {
      MessageBox.Show(ex.Message);
   }
}
}
Posted
Updated 17-Mar-16 18:51pm
v2

1 solution

You are closing the connection inside loop. It will for first time but next time Database connection is not opened so you are getting exception. Secondly use ExecuteNonQuery() instead of ExecuteReader(). Try with below code:
C#
try
{
  // Open connection
  Globals.hardie.Open();

  for (int x = 0; x < 11; x++)
  {
	 string command = "UPDATE `james_hardie`.`config` SET `system_name`='" + Globals.printerNames[x] + "' WHERE `idconfig`='" + (x + 1) + "';";
	 MySqlCommand excute = new MySqlCommand(command, Globals.hardie);
	 excute.ExecuteNonQuery();
  }
  
  // Close connection
  Globals.hardie.Close();
}
catch (Exception ex)
{
  MessageBox.Show(ex.Message);
}

Although above one resolve your problem stills it has SQL Injection problem, so always use parameterised query like below:
C#
MySqlCommand cmd1; 
cmd1 = new MySqlCommand("SELECT * FROM User_table WHERE User_ID = @UserID", Globals.hardie);
cmd1.Parameters.AddWithValue("@UserID", "yourValue"));
excute.ExecuteNonQuery();
cmd1.Parameters.Clear();
   
v4
Comments
Cbrown330 18-Mar-16 0:57am
   
Yea I tried that and it gives me a "myreader is already assigned" I have tried to put system threads in so the code would sleep for a bit to see if I was having timing issues, but that didn't work either.

public static string connection = "datasource=localhost; port=3306; username=root; password=root;";
public static MySqlConnection hardie = new MySqlConnection(connection);


OK sorry just saw the lower part of the response, so in that would session be my array?
[no name] 18-Mar-16 1:20am
   
Updated my answer. Use ExecuteNonQuery() as you are executing DML operation in table.

Secondly for lower part of answer, it need to your userID value(in your case Globals.printerNames[x]).
Cbrown330 18-Mar-16 1:28am
   
The top worked for me, imagine that just using nonQuery would fix my head ache in the last few hours. Thank you much

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900