Link to home
Start Free TrialLog in
Avatar of r3nder
r3nderFlag for United States of America

asked on

How do you do a multiple insert statement in MySQL

I am trying to find a way to insert multiple rows into MySQL at one time - I was reading about something like
 INSERT INTO table (a,b) VALUES (1,2), (2,3), (3,4);
but can anyone give me an example of how to write the code, for example I have a memorylog array
(public MemoryLog memorylog;) and I have the number of entries that it will have to return
(for (int i = 0; (i < Program.MainForm.myBus.numberofsurveys); i++))
then I assign values. but I am not sure how to  build the query and fill it

Does anyone know?
Avatar of Sinisa Vuk
Sinisa Vuk
Flag of Croatia image
fill string variable with sequentially added insert command and pass variable for executing:

String sSQL;

sSQL = "";
for (int i = 0; (i < Program.MainForm.myBus.numberofsurveys); i++)
{
   sSQL = sSQL + "INSERT INTO table (a,b) VALUES (" + valueAasStr+ ", " + valueBasStr + ");";
}
//execute sSQL - same as one INSERT

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of r3nder
r3nder
Flag of United States of America image

ASKER

ok I keep getting a fatal error - anyone see it? - I know I don't
  public void BuldUploadQueryAndUpload()
        {
            string globalconnStr = System.Configuration.ConfigurationManager.ConnectionStrings["con1"].ToString();
            string query = "";
            MySqlConnection cn = new MySqlConnection(globalconnStr);
            MySqlCommand cmd = new MySqlCommand(query, cn);
            
            query += "INSERT INTO ControllerMemoryLogData (UploadTime,EraseID,Technician,Location,ControllerSerialNumber,ToolNumber,SurveyTime,PulseSyncTime,Angle,Azimuth,DipAngle,HoursLeft,Voltage,BatteryUsed,Temperature, ";
            query += " TGF,TMF,NumberOfPulses,Frame0,Frame1,Frame2,Frame3,Frame4,Frame5,Frame6,Frame7,Frame8,Frame9,Frame10,PulseEnergy0,PulseEnergy1,PulseEnergy2,PulseEnergy3, ";
            query += " PulseEnergy4,PulseEnergy5,PulseEnergy6,PulseEnergy7,PulseEnergy8,PulseEnergy9,PulseEnergy10,PulseEnergy11,PulseEnergy12,PulsePing0,PulsePing1,PulsePing2, ";
            query += " PulsePing3,PulsePing4,PulsePing5,PulsePing6,PulsePing7,PulsePing8,PulsePing9,PulsePing10,PulsePing11,PulsePing12,PulseJam0,PulseJam1,PulseJam2,PulseJam3, ";
            query += " PulseJam4,PulseJam5,PulseJam6,PulseJam7,PulseJam8,PulseJam9,PulseJam10,PulseJam11,PulseJam12,DefaultLocationIndex) ";
            query += " VALUES ";
            for (int i = 0; (i < Program.MainForm.myBus.numberofsurveys); i++)
            {
                Program.MainForm.myServer.controllermemorylog.technician = Properties.Settings.Default.Username;
                Program.MainForm.myServer.controllermemorylog.location = Properties.Settings.Default.Location;
                Program.MainForm.myServer.controllermemorylog.EraseID = Program.MainForm.myBus.Controller_EEProm[EEPROM.CONTROLLER.EraseID].Data.asLong;
                Program.MainForm.myServer.controllermemorylog.controllerserialnumber = Program.MainForm.myBus.Controller_EEProm[EEPROM.CONTROLLER.SerialNum].Data.asLong;
                Program.MainForm.myServer.controllermemorylog.toolnumber = Program.MainForm.myBus.Controller_EEProm[EEPROM.CONTROLLER.ToolNumber].Data.asLong;
                Program.MainForm.myServer.controllermemorylog.SurveyTime = Program.MainForm.myBus.surveydata[i].SurveyTimeStamp;
                Program.MainForm.myServer.controllermemorylog.PulseSyncTime = Program.MainForm.myBus.surveydata[i].PulseSyncTimeStamp;
                Program.MainForm.myServer.controllermemorylog.angle = Program.MainForm.myBus.surveydata[i].Angle;
                Program.MainForm.myServer.controllermemorylog.azimuth = Program.MainForm.myBus.surveydata[i].Azimuth;
                Program.MainForm.myServer.controllermemorylog.dip = Program.MainForm.myBus.surveydata[i].DipAngle;
                Program.MainForm.myServer.controllermemorylog.hoursleft = Program.MainForm.myBus.surveydata[i].BatteryHoursLeft;
                Program.MainForm.myServer.controllermemorylog.voltage = Program.MainForm.myBus.surveydata[i].BatteryVoltage;
                Program.MainForm.myServer.controllermemorylog.battused = Program.MainForm.myBus.surveydata[i].BatteryCountsUsed;
                Program.MainForm.myServer.controllermemorylog.temperature = Program.MainForm.myBus.surveydata[i].Temperature;
                Program.MainForm.myServer.controllermemorylog.tgf = Program.MainForm.myBus.surveydata[i].TGF;
                Program.MainForm.myServer.controllermemorylog.tmf = Program.MainForm.myBus.surveydata[i].TMF;
                Program.MainForm.myServer.controllermemorylog.numberofpulses = Program.MainForm.myBus.surveydata[i].numberofpulsessent;
                Program.MainForm.myServer.controllermemorylog.defaultLocationIndex = Program.MainForm.currentuser.defaultlocationid;
                for (int j = 0; j < 13; j++)
                {
                    Program.MainForm.myServer.controllermemorylog.PulseEnergy[j] = Program.MainForm.myBus.surveydata[i].pulseenergy[j];
                    Program.MainForm.myServer.controllermemorylog.PulsePing[j] = Program.MainForm.myBus.surveydata[i].pulseping[j];
                    Program.MainForm.myServer.controllermemorylog.PulseJam[j] = Program.MainForm.myBus.surveydata[i].pulsejam[j];

                }
                Debug.WriteLine(i.ToString());
                for (int k = 0; k < 11; k++)
                {
                    Program.MainForm.myServer.controllermemorylog.frame[k] = Program.MainForm.myBus.surveydata[i].frames[k];
                }
                //now that the data is loaded into the array, let's upload it
                int currentindex = i;
                int totalfiles = (int)Program.MainForm.myBus.numberofsurveys;
                if (i == 0)
                {
                    query += " (@UploadTime" + i + ", @EraseID" + i + ",@Technician" + i + ",@Location" + i + ",@ControllerSerialNumber" + i + ",@ToolNumber" + i + ",@SurveyTime" + i + ",@PulseSyncTime" + i + ",@Angle" + i + ",@Azimuth" + i + ",@DipAngle" + i + ",@HoursLeft" + i + ",@Voltage" + i + ",@BattUsed" + i + ",@Temperature" + i + ",";
                    query += " @TGF" + i + ",@TMF" + i + ",@NumberOfPulses" + i + ",@Frame0" + i + ",@Frame1" + i + ",@Frame2" + i + ",@Frame3" + i + ",@Frame4" + i + ",@Frame5" + i + ",@Frame6" + i + ",@Frame7" + i + ",@Frame8" + i + ",@Frame9" + i + ",@Frame10" + i + ",@PulseEnergy0" + i + ",@PulseEnergy1" + i + ",@PulseEnergy2" + i + ",@PulseEnergy3" + i + ", ";
                    query += " @PulseEnergy4" + i + ",@PulseEnergy5" + i + ",@PulseEnergy6" + i + ",@PulseEnergy7" + i + ",@PulseEnergy8" + i + ",@PulseEnergy9" + i + ",@PulseEnergy10" + i + ",@PulseEnergy11" + i + ",@PulseEnergy12" + i + ",@PulsePing0" + i + ",@PulsePing1" + i + ",@PulsePing2" + i + ", ";
                    query += " @PulsePing3" + i + ",@PulsePing4" + i + ",@PulsePing5" + i + ",@PulsePing6" + i + ",@PulsePing7" + i + ",@PulsePing8" + i + ",@PulsePing9" + i + ",@PulsePing10" + i + ",@PulsePing11" + i + ",@PulsePing12" + i + ",@PulseJam0" + i + ",@PulseJam1" + i + ",@PulseJam2" + i + ",@PulseJam3" + i + ", ";
                    query += " @PulseJam4" + i + ",@PulseJam5" + i + ",@PulseJam6" + i + ",@PulseJam7" + i + ",@PulseJam8" + i + ",@PulseJam9" + i + ",@PulseJam10" + i + ",@PulseJam11" + i + ",@PulseJam12" + i + ",@DefaultLocationIndex" + i + ") ";
                }
                if (i >= 1)
                {
                    query += " ,(@UploadTime" + i + ", @EraseID" + i + ",@Technician" + i + ",@Location" + i + ",@ControllerSerialNumber" + i + ",@ToolNumber" + i + ",@SurveyTime" + i + ",@PulseSyncTime" + i + ",@Angle" + i + ",@Azimuth" + i + ",@DipAngle" + i + ",@HoursLeft" + i + ",@Voltage" + i + ",@BattUsed" + i + ",@Temperature" + i + ",";
                    query += " @TGF" + i + ",@TMF" + i + ",@NumberOfPulses" + i + ",@Frame0" + i + ",@Frame1" + i + ",@Frame2" + i + ",@Frame3" + i + ",@Frame4" + i + ",@Frame5" + i + ",@Frame6" + i + ",@Frame7" + i + ",@Frame8" + i + ",@Frame9" + i + ",@Frame10" + i + ",@PulseEnergy0" + i + ",@PulseEnergy1" + i + ",@PulseEnergy2" + i + ",@PulseEnergy3" + i + ", ";
                    query += " @PulseEnergy4" + i + ",@PulseEnergy5" + i + ",@PulseEnergy6" + i + ",@PulseEnergy7" + i + ",@PulseEnergy8" + i + ",@PulseEnergy9" + i + ",@PulseEnergy10" + i + ",@PulseEnergy11" + i + ",@PulseEnergy12" + i + ",@PulsePing0" + i + ",@PulsePing1" + i + ",@PulsePing2" + i + ", ";
                    query += " @PulsePing3" + i + ",@PulsePing4" + i + ",@PulsePing5" + i + ",@PulsePing6" + i + ",@PulsePing7" + i + ",@PulsePing8" + i + ",@PulsePing9" + i + ",@PulsePing10" + i + ",@PulsePing11" + i + ",@PulsePing12" + i + ",@PulseJam0" + i + ",@PulseJam1" + i + ",@PulseJam2" + i + ",@PulseJam3" + i + ", ";
                    query += " @PulseJam4" + i + ",@PulseJam5" + i + ",@PulseJam6" + i + ",@PulseJam7" + i + ",@PulseJam8" + i + ",@PulseJam9" + i + ",@PulseJam10" + i + ",@PulseJam11" + i + ",@PulseJam12" + i + ",@DefaultLocationIndex" + i + ") ";
                }
                try
                {
                    
                    cmd.Parameters.AddWithValue("@UploadTime" + i, DateTime.Now);
                    cmd.Parameters.AddWithValue("@EraseID" + i, Program.MainForm.myServer.controllermemorylog.EraseID);
                    cmd.Parameters.AddWithValue("@Technician" + i, Program.MainForm.myServer.controllermemorylog.technician.Replace("'", "`"));
                    cmd.Parameters.AddWithValue("@Location" + i, Program.MainForm.myServer.controllermemorylog.location.Replace("'", "`"));
                    cmd.Parameters.AddWithValue("@ControllerSerialNumber" + i, Program.MainForm.myServer.controllermemorylog.controllerserialnumber);
                    cmd.Parameters.AddWithValue("@ToolNumber" + i, Program.MainForm.myServer.controllermemorylog.toolnumber);
                    cmd.Parameters.AddWithValue("@SurveyTime" + i, Program.MainForm.myServer.controllermemorylog.SurveyTime.ToString("yyyyMMddHHmmss"));
                    cmd.Parameters.AddWithValue("@PulseSyncTime" + i, Program.MainForm.myServer.controllermemorylog.PulseSyncTime.ToString("yyyyMMddHHmmss"));
                    cmd.Parameters.AddWithValue("@Angle" + i, Program.MainForm.myServer.controllermemorylog.angle.ToString("G"));
                    if (Program.MainForm.myServer.controllermemorylog.azimuth.ToString("G") != "NaN")
                    {
                        cmd.Parameters.AddWithValue("@Azimuth" + i, Program.MainForm.myServer.controllermemorylog.azimuth.ToString("G"));
                    }
                    else
                    {
                        string nullstring = string.Empty;
                        cmd.Parameters.AddWithValue("@Azimuth" + i, null);
                    }
                    if (Program.MainForm.myServer.controllermemorylog.dip.ToString("G") != "NaN")
                    {
                        cmd.Parameters.AddWithValue("@DipAngle" + i, Program.MainForm.myServer.controllermemorylog.dip.ToString("G"));
                    }
                    else
                    {
                        string nullstring = string.Empty;
                        cmd.Parameters.AddWithValue("@DipAngle" + i, null);
                    }
                    cmd.Parameters.AddWithValue("@HoursLeft" + i, Program.MainForm.myServer.controllermemorylog.hoursleft.ToString("G"));
                    cmd.Parameters.AddWithValue("@Voltage" + i, Program.MainForm.myServer.controllermemorylog.voltage.ToString("G"));
                    cmd.Parameters.AddWithValue("@BattUsed" + i, Program.MainForm.myServer.controllermemorylog.battused.ToString("G"));
                    cmd.Parameters.AddWithValue("@Temperature" + i, Program.MainForm.myServer.controllermemorylog.temperature.ToString("G"));
                    cmd.Parameters.AddWithValue("@TGF" + i, Program.MainForm.myServer.controllermemorylog.tgf.ToString("G"));
                    if (Program.MainForm.myServer.controllermemorylog.voltage.ToString("G") != "NaN")
                    {
                        cmd.Parameters.AddWithValue("@TMF" + i, Program.MainForm.myServer.controllermemorylog.voltage.ToString("G"));
                    }
                    else
                    {
                        string nullstring = string.Empty;
                        cmd.Parameters.AddWithValue("@TMF" + i, null);
                    }
                    cmd.Parameters.AddWithValue("@NumberOfPulses" + i, Program.MainForm.myServer.controllermemorylog.numberofpulses.ToString());
                    cmd.Parameters.AddWithValue("@Frame0" + i, Program.MainForm.myServer.controllermemorylog.frame[0].ToString());
                    cmd.Parameters.AddWithValue("@Frame1" + i, Program.MainForm.myServer.controllermemorylog.frame[1].ToString());
                    cmd.Parameters.AddWithValue("@Frame2" + i, Program.MainForm.myServer.controllermemorylog.frame[2].ToString());
                    cmd.Parameters.AddWithValue("@Frame3" + i, Program.MainForm.myServer.controllermemorylog.frame[3].ToString());
                    cmd.Parameters.AddWithValue("@Frame4" + i, Program.MainForm.myServer.controllermemorylog.frame[4].ToString());
                    cmd.Parameters.AddWithValue("@Frame5" + i, Program.MainForm.myServer.controllermemorylog.frame[5].ToString());
                    cmd.Parameters.AddWithValue("@Frame6" + i, Program.MainForm.myServer.controllermemorylog.frame[6].ToString());
                    cmd.Parameters.AddWithValue("@Frame7" + i, Program.MainForm.myServer.controllermemorylog.frame[7].ToString());
                    cmd.Parameters.AddWithValue("@Frame8" + i, Program.MainForm.myServer.controllermemorylog.frame[8].ToString());
                    cmd.Parameters.AddWithValue("@Frame9" + i, Program.MainForm.myServer.controllermemorylog.frame[9].ToString());
                    cmd.Parameters.AddWithValue("@Frame10" + i, Program.MainForm.myServer.controllermemorylog.frame[10].ToString());
                    cmd.Parameters.AddWithValue("@PulseEnergy0" + i, Program.MainForm.myServer.controllermemorylog.PulseEnergy[0].ToString());
                    cmd.Parameters.AddWithValue("@PulseEnergy1" + i, Program.MainForm.myServer.controllermemorylog.PulseEnergy[1].ToString());
                    cmd.Parameters.AddWithValue("@PulseEnergy2" + i, Program.MainForm.myServer.controllermemorylog.PulseEnergy[2].ToString());
                    cmd.Parameters.AddWithValue("@PulseEnergy3" + i, Program.MainForm.myServer.controllermemorylog.PulseEnergy[3].ToString());
                    cmd.Parameters.AddWithValue("@PulseEnergy4" + i, Program.MainForm.myServer.controllermemorylog.PulseEnergy[4].ToString());
                    cmd.Parameters.AddWithValue("@PulseEnergy5" + i, Program.MainForm.myServer.controllermemorylog.PulseEnergy[5].ToString());
                    cmd.Parameters.AddWithValue("@PulseEnergy6" + i, Program.MainForm.myServer.controllermemorylog.PulseEnergy[6].ToString());
                    cmd.Parameters.AddWithValue("@PulseEnergy7" + i, Program.MainForm.myServer.controllermemorylog.PulseEnergy[7].ToString());
                    cmd.Parameters.AddWithValue("@PulseEnergy8" + i, Program.MainForm.myServer.controllermemorylog.PulseEnergy[8].ToString());
                    cmd.Parameters.AddWithValue("@PulseEnergy9" + i, Program.MainForm.myServer.controllermemorylog.PulseEnergy[9].ToString());
                    cmd.Parameters.AddWithValue("@PulseEnergy10" + i, Program.MainForm.myServer.controllermemorylog.PulseEnergy[10].ToString());
                    cmd.Parameters.AddWithValue("@PulseEnergy11" + i, Program.MainForm.myServer.controllermemorylog.PulseEnergy[11].ToString());
                    cmd.Parameters.AddWithValue("@PulseEnergy12" + i, Program.MainForm.myServer.controllermemorylog.PulseEnergy[12].ToString());
                    cmd.Parameters.AddWithValue("@PulsePing0" + i, Program.MainForm.myServer.controllermemorylog.PulsePing[0].ToString());
                    cmd.Parameters.AddWithValue("@PulsePing1" + i, Program.MainForm.myServer.controllermemorylog.PulsePing[1].ToString());
                    cmd.Parameters.AddWithValue("@PulsePing2" + i, Program.MainForm.myServer.controllermemorylog.PulsePing[2].ToString());
                    cmd.Parameters.AddWithValue("@PulsePing3" + i, Program.MainForm.myServer.controllermemorylog.PulsePing[3].ToString());
                    cmd.Parameters.AddWithValue("@PulsePing4" + i, Program.MainForm.myServer.controllermemorylog.PulsePing[4].ToString());
                    cmd.Parameters.AddWithValue("@PulsePing5" + i, Program.MainForm.myServer.controllermemorylog.PulsePing[5].ToString());
                    cmd.Parameters.AddWithValue("@PulsePing6" + i, Program.MainForm.myServer.controllermemorylog.PulsePing[6].ToString());
                    cmd.Parameters.AddWithValue("@PulsePing7" + i, Program.MainForm.myServer.controllermemorylog.PulsePing[7].ToString());
                    cmd.Parameters.AddWithValue("@PulsePing8" + i, Program.MainForm.myServer.controllermemorylog.PulsePing[8].ToString());
                    cmd.Parameters.AddWithValue("@PulsePing9" + i, Program.MainForm.myServer.controllermemorylog.PulsePing[9].ToString());
                    cmd.Parameters.AddWithValue("@PulsePing10" + i, Program.MainForm.myServer.controllermemorylog.PulsePing[10].ToString());
                    cmd.Parameters.AddWithValue("@PulsePing11" + i, Program.MainForm.myServer.controllermemorylog.PulsePing[11].ToString());
                    cmd.Parameters.AddWithValue("@PulsePing12" + i, Program.MainForm.myServer.controllermemorylog.PulsePing[12].ToString());
                    cmd.Parameters.AddWithValue("@PulseJam0" + i, Program.MainForm.myServer.controllermemorylog.PulseJam[0].ToString());
                    cmd.Parameters.AddWithValue("@PulseJam1" + i, Program.MainForm.myServer.controllermemorylog.PulseJam[1].ToString());
                    cmd.Parameters.AddWithValue("@PulseJam2" + i, Program.MainForm.myServer.controllermemorylog.PulseJam[2].ToString());
                    cmd.Parameters.AddWithValue("@PulseJam3" + i, Program.MainForm.myServer.controllermemorylog.PulseJam[3].ToString());
                    cmd.Parameters.AddWithValue("@PulseJam4" + i, Program.MainForm.myServer.controllermemorylog.PulseJam[4].ToString());
                    cmd.Parameters.AddWithValue("@PulseJam5" + i, Program.MainForm.myServer.controllermemorylog.PulseJam[5].ToString());
                    cmd.Parameters.AddWithValue("@PulseJam6" + i, Program.MainForm.myServer.controllermemorylog.PulseJam[6].ToString());
                    cmd.Parameters.AddWithValue("@PulseJam7" + i, Program.MainForm.myServer.controllermemorylog.PulseJam[7].ToString());
                    cmd.Parameters.AddWithValue("@PulseJam8" + i, Program.MainForm.myServer.controllermemorylog.PulseJam[8].ToString());
                    cmd.Parameters.AddWithValue("@PulseJam9" + i, Program.MainForm.myServer.controllermemorylog.PulseJam[9].ToString());
                    cmd.Parameters.AddWithValue("@PulseJam10" + i, Program.MainForm.myServer.controllermemorylog.PulseJam[10].ToString());
                    cmd.Parameters.AddWithValue("@PulseJam11" + i, Program.MainForm.myServer.controllermemorylog.PulseJam[11].ToString());
                    cmd.Parameters.AddWithValue("@PulseJam12" + i, Program.MainForm.myServer.controllermemorylog.PulseJam[12].ToString());
                    cmd.Parameters.AddWithValue("@DefaultLocationIndex" + i, Program.MainForm.myServer.controllermemorylog.defaultLocationIndex);
                    cmd.Parameters.Clear();
                }
                catch (Exception exs)
                {
                    Program.MainForm.myServer.SaveSQLCommands(cmd.CommandText); //useless in this instance - will not save variables only statement
                }
            }
            try
            {
                cn.Open();
                cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            catch (MySqlException ex)
            {
                string e = ex.InnerException.ToString();
                Program.MainForm.myServer.SaveSQLCommands(cmd.CommandText + "//" + e);
            }
        }

Open in new window

Avatar of r3nder
r3nder
Flag of United States of America image

ASKER

the error came from the config file in the con string - I had to add "Allow User Variables=True;"