Click here to Skip to main content
15,113,991 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Dear _Damian S_ sir you posted me solution that .(You are not inserting the foreign key idYear into table slamonth. After inserting your record into slaYear, you need to retrieve the ID field to pass to the slaMonth table).But where we modifie and how, i am already apply foreigen key in my both table slamonth and slareport .can you correct my problem.Please help me
asp.net....
VB
protected void btn_save(object sender, EventArgs e)
        {
            string path = FileUpload1.PostedFile.FileName;
            string ext = Path.GetExtension(path);
            string contenttype = string.Empty;
            if (!FileUpload1.HasFile)
            {
 
                ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language="'javascript'">alert('Please select a file')</script>");  //if file uploader has no file selected

            }
            else if (FileUpload1.HasFile)
            {
                try
                {
                    switch (ext)
                    {
                        case ".csv":
 
                            contenttype = "application/vnd.ms-excel";
 
                            break;
                    }
                    string YEAR = ddlyear.SelectedItem.ToString();
                    string MONTH = ddlmonth.SelectedItem.ToString();
                    MySqlConnection exclecon = new MySqlConnection("Server=Localhost;DataBase=password1;user=root;password=nectar");
                    exclecon.Open();
                    MySqlCommand cmdYear = new MySqlCommand("INSERT INTO slayear (YEAR) VALUES(@YEAR)", exclecon);
                    cmdYear.Parameters.AddWithValue("@YEAR", YEAR);
                    MySqlCommand cmdMonth = new MySqlCommand("INSERT INTO slamonth (MONTH,contenttype) VALUES(@MONTH,@contenttype)", exclecon);
                    cmdMonth.Parameters.AddWithValue("@MONTH", MONTH);
                    cmdMonth.Parameters.Add("@contenttype", MySqlDbType.VarChar).Value = contenttype;
                    string uploadQry = "LOAD DATA LOCAL INFILE '" + path + "' INTO TABLE slareport FIELDS TERMINATED  BY ','   LINES TERMINATED BY '//n' IGNORE 1 LINES ";
                    MySqlCommand myCUpload = new MySqlCommand(uploadQry, exclecon);
                    cmdYear.ExecuteNonQuery();
                    cmdMonth.ExecuteNonQuery();
                    myCUpload.ExecuteNonQuery();
                    ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language="'javascript'">alert('File uploaded Successfully ')</script>");
                    exclecon.Close();
                  
               }
 
                catch (Exception ex)
                {
                    Label4.Text = "Error: " + ex.Message.ToString();
 
                }
            }
        }
    }
}
Mysql table.............
SQL
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
 
CREATE SCHEMA IF NOT EXISTS `password1` DEFAULT CHARACTER SET latin1 ;
USE `password1` ;
-- -----------------------------------------------------
-- Table `password1`.`slayear`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `password1`.`slayear` (
  `idyear` INT(25) NOT NULL AUTO_INCREMENT ,
  `YEAR` VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY (`idyear`) )
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;
-- -----------------------------------------------------
-- Table `password1`.`slamonth`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `password1`.`slamonth` (
  `idmonth` INT(25) NOT NULL AUTO_INCREMENT ,
  `idyear` INT(25) NOT NULL ,
  `MONTH` VARCHAR(45) NOT NULL ,
  `contenttype` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`idmonth`) ,
  INDEX `F_K_idx` (`idyear` ASC) ,
  CONSTRAINT `F_K`
    FOREIGN KEY (`idyear` )
    REFERENCES `password1`.`slayear` (`idyear` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
-- -----------------------------------------------------
-- Table `password1`.`slareport`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `password1`.`slareport` (
  `FromSLANon_ComplianceReportMonthsParameter` VARCHAR(150) NULL DEFAULT NULL ,
  `Target` VARCHAR(45) NULL DEFAULT NULL ,
  `Unit` VARCHAR(45) NULL DEFAULT NULL ,
  `OM` VARCHAR(45) NULL DEFAULT NULL ,
  `Finance` VARCHAR(45) NULL DEFAULT NULL ,
  `InbndMRO` VARCHAR(45) NULL DEFAULT NULL ,
  `InbndRM` VARCHAR(45) NULL DEFAULT NULL ,
  `Maximo` VARCHAR(45) NULL DEFAULT NULL ,
  `Payroll` VARCHAR(45) NULL DEFAULT NULL ,
  `HILAllied` VARCHAR(45) NULL DEFAULT NULL ,
  `Hardware` VARCHAR(45) NULL DEFAULT NULL ,
  `Network` VARCHAR(45) NULL DEFAULT NULL ,
  `Software` VARCHAR(45) NULL DEFAULT NULL ,
  `DBA` VARCHAR(45) NULL DEFAULT NULL ,
  `OPM` VARCHAR(45) NULL DEFAULT NULL ,
  `idmonth` INT(25) NULL DEFAULT NULL ,
  `id` INT(25) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`id`) ,
  INDEX `F_K!_idx` (`idmonth` ASC) ,
  CONSTRAINT `F_K1`
    FOREIGN KEY (`idmonth` )
    REFERENCES `password1`.`slamonth` (`idmonth` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
 
USE `password1` ;
 
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
please provide solution to me....
Posted
Updated 16-May-13 5:07am
v3
Comments
[no name] 16-May-13 8:34am
   
You might be waiting a long time if you are going to wait for _Damian S_ to just happen by and notice that this posting is meant for them....
Richard C Bishop 16-May-13 10:07am
   
Indeed, why not just reply to his solution he gave you initially?

1 solution

Your trying to save data however your not specifying the @idyear instead you have @year you also have @month which is fine, but you need to specify the unique column where you want the data to go. In the case you have the @yearid as the specific column you should at it as an parameter, so the data knows exactly where its going.
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



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