Vb.net error for query and database

 

Dim datetimepicker1 As String = Format(System.DateTime.Now, "yyyy-MM-dd HH:mm:ss") 

    Try
        Dim cmd As MySqlCommand = New MySqlCommand
        With cmd
            .CommandText = "INSERT INTO tbl_product (`prod_name`,`prod_desc`, `cat_id`, `uom_id`,`uom_num`, `dept_id`, `brand_id`, `size_id`, `type_id`, `remarks`, `date`) values (@prod_name,@prod_desc,@cat_id,@uom_id,@uom_num,@dept_id,@brand_id,@size_id,@type_id,@remarks,@date)"
            .Connection = SQLConnection
            .CommandType = CommandType.Text
            .Parameters.AddWithValue("@prod_name", TextBox1.Text)
            .Parameters.AddWithValue("@prod_desc", TextBox2.Text)
            .Parameters.AddWithValue("@cat_id", ComboBox1.Text)
            .Parameters.AddWithValue("@uom_id", ComboBox2.Text)
            .Parameters.AddWithValue("@uom_num", TextBox3.Text)
            .Parameters.AddWithValue("@dept_id", ComboBox3.Text)
            .Parameters.AddWithValue("@brand_id", ComboBox4.Text)
            .Parameters.AddWithValue("@size_id", ComboBox5.Text)
            .Parameters.AddWithValue("@type_id", ComboBox6.Text)
            .Parameters.AddWithValue("@remarks", RichTextBox1.Text)
            .Parameters.AddWithValue("@date", datetimepicker1)
            .ExecuteNonQuery()

        End With

            MsgBox(" SIze Successfully added")
        Catch ex As Exception
            MsgBox(ex.Message.ToString)



        End Try

cannot add or update child row foreign key constraint fails this is my error

this is my table structure* *********** *8

--

-- Table structure for table tbl_brand

CREATE TABLE IF NOT EXISTS tbl_brand ( brand_id int(11) NOT NULL AUTO_INCREMENT, brand_name varchar(200) NOT NULL, brand_desc varchar(200) DEFAULT NULL, PRIMARY KEY ( brand_id ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--

-- Dumping data for table tbl_brand

INSERT INTO tbl_brand ( brand_id , brand_name , brand_desc ) VALUES (1, 'Nike ', 'Nike Air '), (2, 'Crocs ', 'Class A '), (3, 'SafeGuard ', 'SafeGuard ');

--

-- Table structure for table tbl_category

CREATE TABLE IF NOT EXISTS tbl_category ( cat_id int(11) NOT NULL AUTO_INCREMENT, cat_name varchar(200) NOT NULL, cat_desc varchar(200) NOT NULL, PRIMARY KEY ( cat_id ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--

-- Dumping data for table tbl_category

INSERT INTO tbl_category ( cat_id , cat_name , cat_desc ) VALUES (1, 'Bath Soap ', 'Bath Soap '), (2, 'Detergent ', 'Detergent ');

--

-- Table structure for table tbl_dept

CREATE TABLE IF NOT EXISTS tbl_dept ( dept_id int(11) NOT NULL AUTO_INCREMENT, dept_name varchar(200) NOT NULL, dept_desc varchar(200) NOT NULL, PRIMARY KEY ( dept_id ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--

-- Dumping data for table tbl_dept

INSERT INTO tbl_dept ( dept_id , dept_name , dept_desc ) VALUES (1, 'Shoes ', 'Shoes '), (2, 'Soap ', 'Soap ');

--

-- Table structure for table tbl_product

CREATE TABLE IF NOT EXISTS tbl_product ( prod_id int(11) NOT NULL AUTO_INCREMENT, prod_name varchar(200) NOT NULL, prod_desc varchar(200) DEFAULT NULL, cat_id int(11) NOT NULL, dept_id int(11) NOT NULL, brand_id int(11) NOT NULL, type_id int(11) NOT NULL, uom_id int(11) NOT NULL, size_id int(11) NOT NULL, date datetime NOT NULL, remarks varchar(200) DEFAULT NULL, uom_num int(60) DEFAULT NULL, PRIMARY KEY ( prod_id ), KEY tbl_product_ibfk_9 ( type_id ), KEY tbl_product_ibfk_10 ( uom_id ), KEY tbl_product_ibfk_11 ( size_id ), KEY tbl_product_ibfk_12 ( dept_id ), KEY tbl_product_ibfk_13 ( cat_id ), KEY tbl_product_ibfk_14 ( brand_id ) ); ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--

-- Table structure for table tbl_size

CREATE TABLE IF NOT EXISTS tbl_size ( size_id int(11) NOT NULL AUTO_INCREMENT, size_name varchar(100) NOT NULL, PRIMARY KEY ( size_id ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--

-- Dumping data for table tbl_size

INSERT INTO tbl_size ( size_id , size_name ) VALUES (1, 'Small(S) '), (2, 'Medium(M) '), (3, 'Large(L) ');

--

-- Table structure for table tbl_type

CREATE TABLE IF NOT EXISTS tbl_type ( type_id int(11) NOT NULL AUTO_INCREMENT, type_name varchar(200) NOT NULL, PRIMARY KEY ( type_id ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--

-- Dumping data for table tbl_type

INSERT INTO tbl_type ( type_id , type_name ) VALUES (1, 'BaskertBall Shoes '), (2, 'Jersey Shorts '), (3, 'running shoes '), (8, 'Bath Soap ');

--

-- Table structure for table tbl_uom

CREATE TABLE IF NOT EXISTS tbl_uom ( uom_id int(11) NOT NULL AUTO_INCREMENT, uom_name varchar(200) NOT NULL, PRIMARY KEY ( uom_id ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

--

-- Dumping data for table tbl_uom

INSERT INTO tbl_uom ( uom_id , uom_name ) VALUES (1, 'kilogram(kg) '), (2, 'Gram(g) '), (3, 'Milligram(Mg) '), (4, 'Liter(L) '), (5, 'Milliliters(ml) '), (6, 'Pieces(pcs) '), (7, 'foot(ft) ');

--

-- Table structure for table tbl_user

CREATE TABLE IF NOT EXISTS tbl_user ( user_id int(11) NOT NULL AUTO_INCREMENT, user_code varchar(200) DEFAULT NULL, user_password varchar(200) DEFAULT NULL, user_name varchar(200) DEFAULT NULL, user_level int(1) DEFAULT NULL, datetime datetime NOT NULL, com_code varchar(11) NOT NULL, PRIMARY KEY ( user_id ), UNIQUE KEY user_code ( user_code ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--

-- Dumping data for table tbl_user

INSERT INTO tbl_user ( user_id , user_code , user_password , user_name , user_level , datetime , com_code ) VALUES (1, '1024', '1024', 'Vincent Dematera', 3, '2012-01-10 18:18:33', '001'), (7, '14 ', '14', 'Megan Bueno', 1, '2012-10-30 21:56:14', '002'), (8, '13', '13', 'Anonymous', 1, '2012-10-20 21:51:00', '002'), (9, '9', '9', 'boom', 1, '0000-00-00 00:00:00', '003');

--

-- Constraints for dumped tables

--

-- Constraints for table tbl_product

ALTER TABLE tbl_product

ADD CONSTRAINT tbl_product_ibfk_9 FOREIGN KEY ( type_id ) REFERENCES tbl_type ( type_id ), ADD CONSTRAINT tbl_product_ibfk_10 FOREIGN KEY ( uom_id ) REFERENCES tbl_uom ( uom_id ), ADD CONSTRAINT tbl_product_ibfk_11 FOREIGN KEY ( size_id ) REFERENCES tbl_size ( size_id ), ADD CONSTRAINT tbl_product_ibfk_12 FOREIGN KEY ( dept_id ) REFERENCES tbl_dept ( dept_id ), ADD CONSTRAINT tbl_product_ibfk_13 FOREIGN KEY ( cat_id ) REFERENCES tbl_category ( cat_id ), ADD CONSTRAINT tbl_product_ibfk_14 FOREIGN KEY ( brand_id ) REFERENCES tbl_brand ( brand_id ), ADD CONSTRAINT tbl_product_ibfk_2 FOREIGN KEY ( type_id ) REFERENCES tbl_type ( type_id ), ADD CONSTRAINT tbl_product_ibfk_3 FOREIGN KEY ( uom_id ) REFERENCES tbl_uom ( uom_id ), ADD CONSTRAINT tbl_product_ibfk_4 FOREIGN KEY ( size_id ) REFERENCES tbl_size ( size_id ), ADD CONSTRAINT tbl_product_ibfk_5 FOREIGN KEY ( dept_id ) REFERENCES tbl_dept ( dept_id ), ADD CONSTRAINT tbl_product_ibfk_6 FOREIGN KEY ( cat_id ) REFERENCES tbl_category ( cat_id cat_id ), ADD CONSTRAINT tbl_product_ibfk_7 FOREIGN KEY ( brand_id ) REFERENCES tbl_brand ( brand_id ); SET FOREIGN_KEY_CHECKS=1;

You have a significant number of foreign key constraints for your table product. For example, any "size_id" you enter into table products HAS to correspond to a "size_id" in table size.

What your vb.net program is telling you is that one of the id values you are entering into table product does not exist in its corresponding table. My best guess would be that one of your id values (ie. size_id , uom_id , etc) is empty, blank, or zero.

You have a number of options - you can:

  • turn foreign key off for the table if you wish with SET foreign_key_checks = 0;
  • you can remove the foreign keys altogether
  • you can fix your code to ensure you are adhering to the foreign keys before you even attempt to insert a value
    • 链接地址: http://www.djcxy.com/p/59690.html

    上一篇: 将我的MyISAM转换为InnoDB

    下一篇: 查询和数据库的Vb.net错误