Column 'timestamp' does not allow nulls

[[email protected]]

I'm trying to do a simple insert into a MySQLDataTable, but my code keeps crashing and complaining that the column 'timestamp' does not allow nulls.

Here's my code snippet:

Dim dr As DataRow = dtAllPositions.NewRow
dr.Item("account") = cbAccount.Text
dr.Item("product") = "OPTION"
dr.Item("und_symbol") = UCase(tbSymbol.Text)
dr.Item("opra_code") = GridView1.GetFocusedRowCellValue("opra_code")
dr.Item("expiry") = GridView1.GetFocusedRowCellValue("expiry")
dr.Item("strike") = GridView1.GetFocusedRowCellValue("strike")
dr.Item("put_call") = GridView1.GetFocusedRowCellValue("put_call")
dr.Item("option_wrap") = GridView1.GetFocusedRowCellValue("option_wrap")
dr.Item("multiplier") = tbMultiplier.Text
dr.Item("open_quantity") = 0
dr.Item("curr_quantity") = tbQuantity.Text
dr.Item("status") = 0
dtAllPositions.Rows.Add(dr)
dtAllPositions.Update()

The program is throwing the exception "Column 'timestamp' does not allow nulls" when it tries to execute the command "dtAllPositions.Rows.Add(dr)". Using the dbMonitor doesn't help because it isn't actually trying to send an INSERT command to the db - it is failing prior to the dtAllPositions.Update command.

Here is my table definition:

CREATE TABLE `positions` (
`pos_id` int(11) NOT NULL auto_increment,
`account` varchar(4) default NULL,
`product` varchar(6) default NULL,
`und_symbol` varchar(12) default NULL,
`opra_code` varchar(5) default NULL,
`expiry` date default NULL,
`strike` decimal(17,7) default NULL,
`put_call` char(1) default NULL,
`option_wrap` char(3) default NULL,
`multiplier` decimal(15,7) default NULL,
`open_quantity` decimal(18,5) default NULL,
`curr_quantity` decimal(18,5) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`status` int(11) NOT NULL default '0',
PRIMARY KEY (`pos_id`),
KEY `positions_idx1` (`timestamp`),
KEY `account` (`account`),
KEY `product` (`product`),
KEY `und_symbol` (`und_symbol`),
KEY `opra_code` (`opra_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I'm using mySQL 5.0, VB 2005, MySQL .NET 3.50.10.0.

dtAllPositions is configured (at design time) as follows:

select * from positions

INSERT command is:

INSERT INTO mm_options.positions (pos_id, account, product, und_symbol, opra_code, expiry, strike, put_call, option_wrap, multiplier, open_quantity, curr_quantity, timestamp, status) VALUES (:pos_id, :account, :product, :und_symbol, :opra_code, :expiry, :strike, :put_call, :option_wrap, :multiplier, :open_quantity, :curr_quantity, :_timestamp, :status)

UPDATE command is:

UPDATE mm_options.positions SET pos_id = :pos_id, account = :account, product = :product, und_symbol = :und_symbol, opra_code = :opra_code, expiry = :expiry, strike = :strike, put_call = :put_call, option_wrap = :option_wrap, multiplier = :multiplier, open_quantity = :open_quantity, curr_quantity = :curr_quantity, timestamp = :_timestamp, status = :status WHERE ((pos_id = :Original_pos_id))

Any suggestions on what I might be doing wrong? This has me completely stumped.

Many thanks,

John

[Alexey]

This exception is raised by dataset, because timestamp does not allow null. To overcome this change your Select command to this one:

Code: Select all

select `pos_id`, `account`, `product`, `und_symbol`, `opra_code`, `expiry`, `strike`, `put_call`, `option_wrap`, `multiplier`, `open_quantity`, `curr_quantity`, `status` from positions