Column 'timestamp' does not allow nulls

Column 'timestamp' does not allow nulls

Postby » Tue 12 Sep 2006 13:27

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

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`)

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

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,

Posts: 38
Joined: Tue 07 Mar 2006 17:13

Postby Alexey » Tue 12 Sep 2006 15:40

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
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Return to dotConnect for MySQL