Problem Adding Rows with TIMESTAMP

Problem Adding Rows with TIMESTAMP

Postby John Haldi » Tue 07 Mar 2006 17:18

I'm using VS 2005 (VB), MySQLDirect .NET 3.50 beta, MySQL 5.0 server.

In my code I create a MySQLTableAdapter with this code:

Dim prodPositionTable As MySqlDataTable = New MySqlDataTable("SELECT * FROM mm_options.positions", "User ID=haldi;Password=XXXXXXX;Host=XXXXXXXX;Database=mm_options")

prodPositionTable.Active = True

I then go through another table and try to add rows to the prodPositionTable with this code:

Dim GSECRow As DataRow

For Each GSECRow In GSECPositionUpload.Rows
Dim newProdRow As DataRow = prodPositionTable.NewRow

newProdRow("account") = Mid(GSECRow.Item("ACCOUNT_NUMBER_ENTITY_ID"), 1, 4)
newProdRow("product") = GSECRow.Item("BASIC_PRODUCT")
newProdRow("und_symbol") = GSECRow.Item("UNDERLYING_SYMBOL")
If GSECRow.Item("EXPIRATION_YEAR") <> "" Then
newProdRow("expiry") = GSECRow.Item("EXPIRATION_MONTH") & "/" & GSECRow.Item("EXPIRATION_DAY") & "/" & GSECRow.Item("EXPIRATION_YEAR")
newProdRow("strike") = GSECRow.Item("STRIKE_PRICE")
newProdRow("put_call") = GSECRow.Item("PUT_CALL")
End If
newProdRow("multiplier") = GSECRow.Item("MULTIPLIER")
newProdRow("open_quantity") = GSECRow.Item("T_D_QUANTITY")
newProdRow("curr_quantity") = GSECRow.Item("T_D_QUANTITY")
newProdRow("option_wrap") = GSECRow.Item("TRADING_SYMBOL")
newProdRow("opra_code") = GSECRow.Item("OPRA_CODE")
newProdRow("timestamp") = Today.n

prodPositionTable.Rows.Add(newProdRow)
ProgressBar1.Value = ProgressBar1.Value + 1
Next

When the code gets to the prodPositionTable.Rows.Add(newProdRow), it throws an exception saying that 'timestamp' field can not be set to null.

In the actual table, timestamp is set to allow nulls, and to default to the value of CURRENT_TIMESTAMP.

If I use the DBMonitor tool, I see that the exception isn't being thrown by MySQL - the command never gets passed. Rather it is being thrown by something on the client side. As you can see, I don't set the TIMESTAMP value to anything in my code (I don't want to - I want the DB to do it automatically).

Can anybody please help me sort this out?

Many thanks,

John Haldi
john@haldi.net
John Haldi
 

Postby Serious » Thu 09 Mar 2006 16:29

Check if you created your table correctly. To allow NULL values in timestamp columns use following syntax
Code: Select all
create table table1 (
...
f_timestamp timestamp null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
...
);
If you still encounter problems with timestamp columns please send us your table definition.
Serious
 

TIMESTAMP problems and more

Postby John Haldi » Thu 09 Mar 2006 18:16

I did in fact allow nulls in the table, although the default value is set to insert CURRENT_TIMESTAMP so there shouldn't be an issue. In further testing I've discovered that when I create a MySQLDataTable at runtime in my code, and then attempt to insert rows, its even failing on the primary key column 'ID', which is an auto-increment index.

Is anybody else experiencing this problem?

Thanks,

John Haldi
John Haldi
 


Return to dotConnect for MySQL