数据不保存在mysql数据库中 [英] Data not save in mysql database

问题描述

先生我在mysql数据库中有三个表，当我们插入一个记录然后只有一个表（slayear）保存记录而另一个表没有任何记录。

我的asp代码和数据库表给出了下面...........



asp.net ....

Sir i have three table in mysql database when we insert a record then only one table (slayear) save record and another table does not have any record.
my asp code and database table is given below...........

asp.net....

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表.... .........

Mysql table.............

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....

推荐答案

您没有将外键idYear插入到表slamonth中。将记录插入slaYear后，需要检索ID字段以传递给slaMonth表。

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.

这篇关于数据不保存在mysql数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！