ASP.NET insert from bll to mysql

Question

I am trying to make an insert to an MySql database using a three layer solution (or what it might be called).

I have done this may times with an MS-sql database and it has worked very well.

But now when I am trying to make an insert I get the the ID can't be null. I thought the database took care of that. If I write an insert directly in the code and use the MySqlCommand and executeNonQuery it works great.

Is it not possible to use BLL and DAL with MySql?

Error message:

System.Data.NoNullAllowedException: Column 'GiftID' does not allow nulls. at System.Data.DataColumn.CheckNullable(DataRow row) at System.Data.DataColumn.CheckColumnConstraint(DataRow row, DataRowAction action) at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent) at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Int32 position, Boolean fireEvent, Exception& deferredException) at System.Data.DataTable.InsertRow(DataRow row, Int32 proposedID, Int32 pos, Boolean fireEvent) at System.Data.DataRowCollection.Add(DataRow row) at PayEx.payexusersDataTable.AddpayexusersRow(payexusersRow row) in c:\Users\IT\AppData\Local\Temp\Temporary ASP.NET Files\payex\45bd406a\10c84208\App_Code.cyqhjqo7.1.cs:line 444 at PayExBLL.AddPayExUser(String Firstname, String Lastname, String Company, String Address, String Zip, String City, String Phone, String Email, Byte ContactMe, UInt32 Amount, UInt32 TransactionNumber, Byte Anonymous, String Currency) in c:\Users\IT\Documents\Visual Studio 2008\WebSites\payex\App_Code\BLL\PayExBLL.cs:line 66 at _Default.btn_next3_Click(Object sender, EventArgs e) in c:\Users\IT\Documents\Visual Studio 2008\WebSites\payex\Default.aspx.cs:line 191

My code:

[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]
public bool AddPayExUser(string Firstname, string Lastname, string Company, string Address, string Zip, string City, string Phone, string Email, byte ContactMe, uint Amount, uint TransactionNumber, byte Anonymous, string Currency)
{
    PayEx.payexusersDataTable puTable = new PayEx.payexusersDataTable();
    PayEx.payexusersRow puRow = puTable.NewpayexusersRow();

    puRow.Firstname = Firstname;
    puRow.Lastname  = Lastname;
    puRow.Company = Company;
    puRow.Address = Address;
    puRow.Zip = Zip;
    puRow.City = City;
    puRow.Phone = Phone;
    puRow.Email = Email;
    puRow.ContactMe = ContactMe;
    puRow.Amount = Amount;
    puRow.TransactionNumber = TransactionNumber;
    puRow.Anonymous = Anonymous;
    puRow.Currency = Currency;


    puTable.AddpayexusersRow(puRow);
    int rowsAffected = Adapter.Update(puTable);

    return rowsAffected == 1;
}

Answer 1

System.Data.NoNullAllowedException: Column 'GiftID' does not allow nulls. at

From the looks of your code, you're forgetting to pass in a GiftID parameter to your function but it's expected (and can't be null) in your table row.

Hence the exception. Either set it in your code above, or define a default value on it in your MySQL database.

Answer 2

EDIT: This comment assumes you're inserting into a table for which GiftId is the primary key, which seems unlikely. If so, Eoin Campbell's answer makes more sense!

Check if GiftId is an AUTO_INCREMENT column; that's MySQL's equivalent of identity.

You can recreate the column as identity like:

ALTER TABLE Gifts
         DROP COLUMN GiftId;

ALTER TABLE items
         ADD COLUMN GiftId INT NOT NULL AUTO_INCREMENT FIRST;