Default value in SQL not being saved

[peterk]

Hello all,

I have a simple SQL table with a datetime field that has a default value of getdate().
I created a .net application using datagrids that connects to the sql table.

Here is the code in .CellValueChanged
Me.Validate()
Me.TblMemoBindingSource.EndEdit()
Me.TblMemoTableAdapter.Update(Me.GestionMemoDataSet.tblMemo)

All the data that I modified/created gets transfered to my sql table but my default date doesn't appear.

Please point me to the correct tutorial/answer, I'M GOING INSANE

Thanks

 

[cjard]

I thought that NOW() was the function that gave the current time?

Few other points:
Your profile says you use .net 1.1 but tableadapters are definitely 2.0+ Please correct this discrepancy

You save to the DB in the CellValueChanged? That could mean many updates per second. Usually we change all the data then save once

 

[peterk]

The getdate() function that I use is directly in the SQL table not in .net.
CellValueChanged will only run when the user moves from cell to cell. I don't believe it does an update many times per second.
Is there any reason why this is not being saved when every other data is?

 

[cjard]

I dont quite understand how a function can be in a table.. tables hold data. Functions are not data, they are executable code.

So for every row of 20 cells youre going to fire an update 20 times? Why dont you just edit the whole row and then update it once?

I cant really answer your question because I dont understand what you have done, or your thinking. Suffice to say, youre doing something I've never done in 10+ years of programming, so I'd suggest that some aspect of your approach is unusual and this is throwing up problems

 

[JimS]

If the getDate() function you have assigned in SQL for the default value is not being populated, then somewhere in your code a value is being written back to SQL.

I am not an expert on the datagrid control, but I would guess that what's happening is that somehow it is writing NULL back to your table because you haven't specified a value.

Try explicitly writing the date back in using now() instead, or build a SQLClient.SQLCommand insert query.

Another thought, is that if you're updating a row, rather than inserting one, the default value will not be applied, as it is only used if this value is omitted from the initial insert.

I hope this is of some help.

Jim.

 

[cjard]

paraphrasing JimS' post:

You have created a SQL table
You have declared in SQL that the default value for a particular column should be the the resulkt of a function call giving the date
You are not seeing the date get written every time you run an INSERT command

THis is because your INSERT command is inserting a value into the column, so the default is not needed


Example (Oracle syntax):

CREATE TABLE test(col1 VARCHAR2(10), col2 DATE DEFAULT sysdate)

Here are two insert statements:

INSERT INTO table(col1, col2) VALUES ('test', NULL)
INSERT INTO table(col1) VALUES ('test')


The italic statement WILL NOT WRITE A DATE because you have specified a value for the column (the value you specified was: NULL)
The bold statement WILL cause a date to be written, because no value was specified

Have a deeper read up on default values behaviour in your chosen DB

At no time was the function stored in the table itself

 

[peterk]

I looked into the table adapter in .net and saw this generated insert command:

INSERT INTO tblMemo
                      (MemoDescription, DateModified, UserModified, Terminated, UserCreated, Priority, DateCreated)
VALUES     (@MemoDescription,@DateModified,@UserModified,@Terminated,@UserCreated,@Priority,@DateCreated)

I was effectively writing a NULL back to SQL which, in turn, overrides the default statement...DUH. (thanks Jims)

Suffice it to say, I modified it (thanks cjard) and it is now working properly.

Just another quick question to cjard:

At no time was the function stored in the table itself

What do you mean? The getdate() function is what I coded as a default value for my date field.

 

[cjard]

Suffice it to say, I modified it (thanks cjard) and it is now working properly.

If you modified the auto gnereated .Designer.vb file then changes wil lbe lost next time the file is generated. You'd be better off removing the parameter in the INSERT statement entirely, in the designer, but leave it in the select statement

What do you mean? The getdate() function is what I coded as a default value for my date field.

I mean tables hold data. In the same way that a text file holds data. You cant store executable code in a table any more than you can load an EXE into notepad and have it make any sense, or run.

Basically, saying that you put getdate() into the table would be:

INSERT INTO myTable(myCOlumn) VALUES('getdate()')

remember; youre talking to programmers. Try and tell us things that make sense!

 

[peterk]

LOL, Now I understand why you got so worked up with placing functions in tables. I did not do that! I entered getdate() as a default value in the design screen not on the field itself.

You'd be better off removing the parameter in the INSERT statement entirely, in the designer, but leave it in the select statement

That's what I did. Just removed it from the INSERT statement.

I've been programming for a very long time, I go back to the days of COBOL, and now SAP, so all this .net is new to me. Thanks again