Description:
Hi all,
I have tried something rather simple, namely to update a database table using a MySqlDataAdapter and a parametrized INSERT command. Once the MySqlDataAdapter's Update function is called, to update the database with all rows contained in the dataset, a Sql Exception 23000 is thrown, indicating that the first column of the dataset contains NULL values. That this exception is thrown would in principle OK because the corresponding column in the database does not allow null values; but the problem is that the values in the dataset to be inserted into the database are positively not NULL values.
Here is the code for the DataAdapter, Connection, Command and Update Logic:
verbResEinfügen = new MySqlConnection();
verbResEinfügen.ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];
MySqlCommand resEinfügen = new MySqlCommand();
resEinfügen.Connection = verbResEinfügen;
resEinfügen.CommandText = "INSERT INTO Reservationen VALUES (@ID, @Datum, @KW, @Tiername, @Tierart, null)"; // the last null value is a placeholder for a timestamp column in the database
resEinfügen.Parameters.Add(new MySqlParameter("@ID", MySqlDbType.Int32, 4, orderCart.Tables["ReservierteTage"].Columns[0].ColumnName));
resEinfügen.Parameters.Add(new MySqlParameter("@Datum", MySqlDbType.Datetime, 8, orderCart.Tables["ReservierteTage"].Columns[9].ColumnName));
resEinfügen.Parameters.Add(new MySqlParameter("@KW", MySqlDbType.Int32, 4, orderCart.Tables["ReservierteTage"].Columns[5].ColumnName));
resEinfügen.Parameters.Add(new MySqlParameter("@Tiername", MySqlDbType.VarChar, 30, orderCart.Tables["ReservierteTage"].Columns[7].ColumnName));
resEinfügen.Parameters.Add(new MySqlParameter("@Tierart", MySqlDbType.Int32, 4, orderCart.Tables["ReservierteTage"].Columns[6].ColumnName));
MySqlDataAdapter res = new MySqlDataAdapter();
res.InsertCommand = resEinfügen;
res.Update(orderCart.Tables["ReservierteTage"]);
The structure of the table "Reservationen" of the DataSet "orderCart", is as follows:
DataTable dt = new DataTable("ReservierteTage");
dt.Columns.Add(new DataColumn("ID", typeof(int)));
dt.Columns.Add(new DataColumn("ResID", typeof(string)));
dt.Columns.Add(new DataColumn("Jahr", typeof(int)));
dt.Columns.Add(new DataColumn("Monat", typeof(int)));
dt.Columns.Add(new DataColumn("Tag", typeof(int)));
dt.Columns.Add(new DataColumn("KW", typeof(int)));
dt.Columns.Add(new DataColumn("Tierart", typeof(int)));
dt.Columns.Add(new DataColumn("Tiername", typeof(string)));
dt.Columns.Add(new DataColumn("Betrag", typeof(decimal)));
dt.Columns.Add(new DataColumn("Datum", typeof(DateTime)));
orderCart.Tables.Add(dt);
Has anybody already observed a similar phenomenon?
How to repeat:
1) Create a mySql database with two tables as follows:
CREATE TABLE KundenDetails (ID int not null AUTO_INCREMENT PRIMARY KEY, Name nvarchar(50) not null, Vorname nvarchar(50) not null, Adresse nvarchar(70) not null, PLZ int not null, Ort nvarchar(50) not null, Telefon nvarchar(30) not null, Passwort nvarchar(12) not null, Email nvarchar(50) not null);
CREATE TABLE Reservationen(ID int not null REFERENCES KundenDetails(ID) ON UPDATE Cascade ON DELETE Cascade, Datum datetime not null, KW int not null, Tiername nvarchar(50) not null, Tierart int not null, Reservationsdatum timestamp not null, PRIMARY KEY (ID, Datum, Tiername, Tierart));
2) Grant SELECT permission on Table KundenDetails and SELECT/INSERT permission on table Reservationen to the user root
3) populate the table KundenDetails with one dummy customer entry, having ID 1;
4) Create and populate in C# .NET code a dataset as outlined above,
5) run in C# .NET the above outlined Update code against the database. The connection string used is as follows:
"Server=127.0.0.1;Uid=root;Pwd=XLV20AUZ;Database=Reservationen"
Description: Hi all, I have tried something rather simple, namely to update a database table using a MySqlDataAdapter and a parametrized INSERT command. Once the MySqlDataAdapter's Update function is called, to update the database with all rows contained in the dataset, a Sql Exception 23000 is thrown, indicating that the first column of the dataset contains NULL values. That this exception is thrown would in principle OK because the corresponding column in the database does not allow null values; but the problem is that the values in the dataset to be inserted into the database are positively not NULL values. Here is the code for the DataAdapter, Connection, Command and Update Logic: verbResEinfügen = new MySqlConnection(); verbResEinfügen.ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"]; MySqlCommand resEinfügen = new MySqlCommand(); resEinfügen.Connection = verbResEinfügen; resEinfügen.CommandText = "INSERT INTO Reservationen VALUES (@ID, @Datum, @KW, @Tiername, @Tierart, null)"; // the last null value is a placeholder for a timestamp column in the database resEinfügen.Parameters.Add(new MySqlParameter("@ID", MySqlDbType.Int32, 4, orderCart.Tables["ReservierteTage"].Columns[0].ColumnName)); resEinfügen.Parameters.Add(new MySqlParameter("@Datum", MySqlDbType.Datetime, 8, orderCart.Tables["ReservierteTage"].Columns[9].ColumnName)); resEinfügen.Parameters.Add(new MySqlParameter("@KW", MySqlDbType.Int32, 4, orderCart.Tables["ReservierteTage"].Columns[5].ColumnName)); resEinfügen.Parameters.Add(new MySqlParameter("@Tiername", MySqlDbType.VarChar, 30, orderCart.Tables["ReservierteTage"].Columns[7].ColumnName)); resEinfügen.Parameters.Add(new MySqlParameter("@Tierart", MySqlDbType.Int32, 4, orderCart.Tables["ReservierteTage"].Columns[6].ColumnName)); MySqlDataAdapter res = new MySqlDataAdapter(); res.InsertCommand = resEinfügen; res.Update(orderCart.Tables["ReservierteTage"]); The structure of the table "Reservationen" of the DataSet "orderCart", is as follows: DataTable dt = new DataTable("ReservierteTage"); dt.Columns.Add(new DataColumn("ID", typeof(int))); dt.Columns.Add(new DataColumn("ResID", typeof(string))); dt.Columns.Add(new DataColumn("Jahr", typeof(int))); dt.Columns.Add(new DataColumn("Monat", typeof(int))); dt.Columns.Add(new DataColumn("Tag", typeof(int))); dt.Columns.Add(new DataColumn("KW", typeof(int))); dt.Columns.Add(new DataColumn("Tierart", typeof(int))); dt.Columns.Add(new DataColumn("Tiername", typeof(string))); dt.Columns.Add(new DataColumn("Betrag", typeof(decimal))); dt.Columns.Add(new DataColumn("Datum", typeof(DateTime))); orderCart.Tables.Add(dt); Has anybody already observed a similar phenomenon? How to repeat: 1) Create a mySql database with two tables as follows: CREATE TABLE KundenDetails (ID int not null AUTO_INCREMENT PRIMARY KEY, Name nvarchar(50) not null, Vorname nvarchar(50) not null, Adresse nvarchar(70) not null, PLZ int not null, Ort nvarchar(50) not null, Telefon nvarchar(30) not null, Passwort nvarchar(12) not null, Email nvarchar(50) not null); CREATE TABLE Reservationen(ID int not null REFERENCES KundenDetails(ID) ON UPDATE Cascade ON DELETE Cascade, Datum datetime not null, KW int not null, Tiername nvarchar(50) not null, Tierart int not null, Reservationsdatum timestamp not null, PRIMARY KEY (ID, Datum, Tiername, Tierart)); 2) Grant SELECT permission on Table KundenDetails and SELECT/INSERT permission on table Reservationen to the user root 3) populate the table KundenDetails with one dummy customer entry, having ID 1; 4) Create and populate in C# .NET code a dataset as outlined above, 5) run in C# .NET the above outlined Update code against the database. The connection string used is as follows: "Server=127.0.0.1;Uid=root;Pwd=XLV20AUZ;Database=Reservationen"