Date is not getting saved

[Jacksparrow87]

Hi people,

I'm facing this problem where I need to save the date from the date time picker into my sql database. However the date format on the sql database is set as US format where I need it to be UK, the alternative I have found is to pass the date in as a parameter (so that the actual date value is saved) I have got to the following coding:

       Dim conn As New SqlConnection("Data Source= Wk3-dbserver;Initial Catalog= supporttest;Integrated Security=SSPI") 
        Dim cmd As New SqlCommand 
        With cmd 
            .CommandType = CommandType.Text 
            .Connection = conn 
            If _ID = 0 Then 
                .CommandText = "INSERT [Log] (dol) values (@p1)" 
            Else 
                .CommandText = "UPDATE Log SET Dol=@p1 WHERE ID=@p2" 
                .Parameters.Add(New SqlParameter("@p2", _ID)) 
            End If 
            .Parameters.Add(New SqlParameter("@p1", Me.dtp.Value)) 
        End With 
        conn.Open() 
        cmd.ExecuteNonQuery()

Now the problem is that the date that gets saved is blank, there is no date what so ever and the value that is saved in dol is empty.

Any help or ideas please?

 

[gmmastros]

I just tested your code, and it appeared to work for me. I tested both the update and the insert. Of course, to test this I needed to change the server and database. I also created a table to test this with.

I suspect the reason this isn't working for you is because of differences in the way I created the table. Can you post the table structure. The easiest way is...

Open SQL Server Management Studio
Drill down to the LOG table.
Right Click -> Script Table As -> Create To -> New Query Window

Then, copy/paste the table creation script here.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

You may also try changing:

.Parameters.Add(New SqlParameter("@p1", Me.dtp.Value))

To

.Parameters.Add(New SqlParameter("@p1", Format(Me.dtp.Value, "yyyymmdd HH:MM:ss")))

the date format on the sql database is set as [!]US[/!] format where I need it to be [!]UK[/!]

Dealing with dates can be messy business if you don't do it right. You should understand that dates are not stored with any particular format at all. It's really the interpretation of dates that are messing things up. With SQL Server, there is really only 2 date formats that are not ambiguous.

yyyy-mm-dd[!]T[/!]hh:mi:ss.mmm (no spaces)
yyyymmdd hh:mi:ss.mmm

The [!]T[/!] in the first case is a literal value.

You should understand that dates are interpreted based on the language setting for the user logged in to the database. For more on this:

thread183-1240616

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

That's what I was thinking too. When you say blank, I took that to mean empty string (as opposed to null), so is it possible you have a varchar/char column instead of a datetime column? If so, by all means change the column to datetime if you can, otherwise, try casting the VB datetime value to a preferred string format.

 

[Jacksparrow87]

Sorry it was null not blank but Im looking in to it, thanks guys

 

[Jacksparrow87]

Update

Im actually using Sql Server eneterprise manager, when I right click on Log it says the following:

New Table
Design Table
Open Table
>Return All Rows
>Return Top
>Query
Full Text Index Table
>Define Full Text Indexing On Table
All Tasks
>Managing Index
>Managing Triggers
>Managing Permissions,
>Import Data
>Export Data
>Create New Publication
>Generate Sql Script
>Display Dependencies
Cut
Copy
Delete
Rename
Properties
Help

> are the options available when I hover over a menu item for example when I hover on Open Table I can select Return all rows, Return top and Query. Also the field dol was set as smalldatetime.

Hope that helps.

 

[gmmastros]

I don't have Enterprise Manager installed on my computer. But... to script the table...

Right Click -> All Tasks -> Generate Sql Script

From there... there may be more options. I dunno.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jacksparrow87]

Thanks, not sure if it was the following what you was after:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Log]
GO

CREATE TABLE [dbo].[Log] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[LogNumber] [int] NULL ,
	[LogNo] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[NoteNumber] [int] NULL ,
	[Dol] [smalldatetime] NULL ,
	[Dol2] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[CompID] [int] NULL ,
	[Company] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[CallersName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Communication] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Telephone] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Email] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Product] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[ProdCall] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Cuscall] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Summary] [ntext] COLLATE Latin1_General_CI_AS NULL ,
	[Problem] [ntext] COLLATE Latin1_General_CI_AS NULL ,
	[Logged] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Solution] [ntext] COLLATE Latin1_General_CI_AS NULL ,
	[Chacode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[TSpent] [int] NULL ,
	[Status] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I know my table doesn't look correct but I needed to create duplicates of some fields and save them as varchars (for data filtering purposes in my application)

Thanks

 

[gmmastros]

That's exactly what I was looking for.

Specifically, it's helpful to know that ID is an identity column, and it's the only column with 'NOT NULL' on the end. This means that it is the only column in the table that requires a value. Since it's an identity column, that value will be generated automatically for you during the insert.

Since you did not indicate that there was an error anywhere, I'm inclined to think there must be a problem with the VB code.

I don't think this is a problem with UK/US dates because I suspect you are trying to use today's date Sept 10, 2008. In the US, that would be 9/10/2008. In the UK that would be 10/9/2008. Both are valid dates (although different).

For now, my best guess concerns your code.

            If [!]_ID = 0[/!] Then 
                .CommandText = "INSERT [Log] (dol) values (@p1)" 
            Else 
                .CommandText = "UPDATE Log SET Dol=@p1 WHERE ID=@p2" 
                .Parameters.Add(New SqlParameter("@p2", _ID)) 
            End If 
            .Parameters.Add(New SqlParameter("@p1", Me.dtp.Value))

If _Id = 0, you are inserting a new row, otherwise you are updating a row in the table.

Suppose for a moment that you have ID's in the table for 1,2,3. 3 rows. Now, suppose _ID = 25. Since it's not 0, the update code is run, but the update statement would look like....

UPDATE Log SET Dol=@p1 WHERE ID=25

Since there is no row with ID = 25, nothing is updated.

Regarding the table...
Well, from a database perspective, it's best to make 'narrow' tables. By 'narrow', I mean, attempting to minimize the size of the columns. With SQL Server, data is stored on the hard drive in 'pages'. Each page is 8K. When SQL Server reads from (and write to) the hard drive, it is always done a page at a time. So.... fitting more records in to a page will require less hard drive activity. Since hard drives are the slow part of a computer, minimizing this will improve performance.

Specifically, I'm referring to the varchar columns. When you created this table, you apparently left the default size for the varchar columns (which is 50).

[tt][blue][Telephone] [nvarchar] ([!]50[/!]) COLLATE Latin1_General_CI_AS NULL ,[/blue][/tt]

I've never seen a telephone number require 50 characters to store. I'm sure you could use varchar(20). If you do this for all the columns in this table (and other tables in your system), you will likely gain some performance.

This is not the major intent of this post. With small tables, you will probably not notice a difference (by small, I mean less than a million rows). As the number of rows in your table increases, the performance gains will become more noticeable.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jacksparrow87]

Thanks George I understand, you see I have been using Access for a really long time and last week I decided to move from Access to SQL - the main reason being that my Access database was slowing down my vb application and I thought Sql would improve the performance but to be honest I know that I need to scrap my database because that is way too many rows.

I used Enterprise to import my Access database and export it to SQL, that is the reason you're seeing varchar with 50 characters as I haven't had the chance to inpsect each field - at the moment I was just testing my vb application with SQL but as you can see Im experiencing a lot of problems and the main one being not being able to save a date in the UK format.