DateTime datatype returns Date only
-
How are you placing this value on your form? In a textbox? What is the code you are using to do this? As Guffa says, it is there... Steve
I am using a DataGrid. I don't doubt that the time is there, because it is being returned by the SELECT query, but what am I missing that will display the data as "6/19/2006 12:57:58 PM". If you can't tell, I am new to VB, so I am sure that I am neglecting to do something very simplistic. Maybe it would help to explain what I am trying to accomplish. I support a Time & Attendance application. The table that I am attempting to query contains punch data (time in & out). I am trying to build an interface to delete selected transactions. The 3 elements in the SELECT query are all set as primary keys. The following code is supposed to fill my DataSet, which it does. Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click OleDbDataAdapter1.Fill(DataSet11, "Time_Post_Work") End Sub If I then delete a transaction from the DataGrid and click on my Save button it should update this back to the original database, but instead what I get is a 'System.Data.DBConcurrencyException' error and no records are updated. This is the DELETE query VB generated: 'DELETE FROM TIME_POST_WORK WHERE (Clock_Nbr = ?) AND (Seq_No = ?) AND (Punch = ?)' I maybe wrong, but I think it is not allowing the deletion because the Date that appears in the DataSet does not match the DateTime that appears in the database. If I modify the DELETE query to read as follows: 'DELETE FROM TIME_POST_WORK WHERE (Clock_Nbr = ?) AND (Seq_No = ?)' the transaction is removed from the database. But since this is Date and Time specific data, I need to be able to see that I am removing a transaction at 12:55 PM on 6/19/2006 as opposed to any old transaction on 6/19/2006. Sorry for so many words. Is there some other information that I could provide that might help? Thanks for you help so far.
-
I am using a DataGrid. I don't doubt that the time is there, because it is being returned by the SELECT query, but what am I missing that will display the data as "6/19/2006 12:57:58 PM". If you can't tell, I am new to VB, so I am sure that I am neglecting to do something very simplistic. Maybe it would help to explain what I am trying to accomplish. I support a Time & Attendance application. The table that I am attempting to query contains punch data (time in & out). I am trying to build an interface to delete selected transactions. The 3 elements in the SELECT query are all set as primary keys. The following code is supposed to fill my DataSet, which it does. Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click OleDbDataAdapter1.Fill(DataSet11, "Time_Post_Work") End Sub If I then delete a transaction from the DataGrid and click on my Save button it should update this back to the original database, but instead what I get is a 'System.Data.DBConcurrencyException' error and no records are updated. This is the DELETE query VB generated: 'DELETE FROM TIME_POST_WORK WHERE (Clock_Nbr = ?) AND (Seq_No = ?) AND (Punch = ?)' I maybe wrong, but I think it is not allowing the deletion because the Date that appears in the DataSet does not match the DateTime that appears in the database. If I modify the DELETE query to read as follows: 'DELETE FROM TIME_POST_WORK WHERE (Clock_Nbr = ?) AND (Seq_No = ?)' the transaction is removed from the database. But since this is Date and Time specific data, I need to be able to see that I am removing a transaction at 12:55 PM on 6/19/2006 as opposed to any old transaction on 6/19/2006. Sorry for so many words. Is there some other information that I could provide that might help? Thanks for you help so far.
It seems that you have used the visual tools to create your app so far. Make sure that the column in the DataGrid's underlying table is set to the 'DateTime' data type. Steve
-
It seems that you have used the visual tools to create your app so far. Make sure that the column in the DataGrid's underlying table is set to the 'DateTime' data type. Steve
When I look at the DataSet1.xsd in the Solution Explorer, the datatype for my Clock_Nbr and Seq_No are both 'int'. The datatype for Punch is 'dateTime'.
-
When I look at the DataSet1.xsd in the Solution Explorer, the datatype for my Clock_Nbr and Seq_No are both 'int'. The datatype for Punch is 'dateTime'.
Can you loop through the rows in your table and display them on screen? MessageBox or Console.WriteLine? Just so that we can see what is actually in your dataset table. Steve
-
Can you loop through the rows in your table and display them on screen? MessageBox or Console.WriteLine? Just so that we can see what is actually in your dataset table. Steve
Sorry, I am not familiar with how to generate this data in code, but I was able to copy the output from the DataSet Preview. This is from the DataSet Preview and is what I see when I click the Open button to Fill my dataset: 4011 6/19/2006 1719 4011 6/29/2006 1801 This is from the Query Builder for the same data, and it includes the headers: Clock_Nbr Punch Seq_No 4011 6/19/2006 12:57:58 PM 1719 4011 6/29/2006 2:32:46 PM 1801
-
Sorry, I am not familiar with how to generate this data in code, but I was able to copy the output from the DataSet Preview. This is from the DataSet Preview and is what I see when I click the Open button to Fill my dataset: 4011 6/19/2006 1719 4011 6/29/2006 1801 This is from the Query Builder for the same data, and it includes the headers: Clock_Nbr Punch Seq_No 4011 6/19/2006 12:57:58 PM 1719 4011 6/29/2006 2:32:46 PM 1801
I understand that you are doing all this visually in the designer? You need to create a TableStyle for your grid. Look in the properties window for your grid, click the [...] ellipsis button and create a new DataGridTableStyle. Then, create a DataGridColumnStyle for each column in your grid. When you create these, you can set the 'Format' for each column. In the column that represents your 'punch' value, you will need to enter: "g" in the Format property. This will set the display to 01/01/2006 01:01 AM To get the value correctly into your delete query, pass the value to the punch parameter as: PunchValue.ToString("g") This will make sure the whole value is presented to the parameter. Let me know how it goes! Steve
-
I understand that you are doing all this visually in the designer? You need to create a TableStyle for your grid. Look in the properties window for your grid, click the [...] ellipsis button and create a new DataGridTableStyle. Then, create a DataGridColumnStyle for each column in your grid. When you create these, you can set the 'Format' for each column. In the column that represents your 'punch' value, you will need to enter: "g" in the Format property. This will set the display to 01/01/2006 01:01 AM To get the value correctly into your delete query, pass the value to the punch parameter as: PunchValue.ToString("g") This will make sure the whole value is presented to the parameter. Let me know how it goes! Steve
Great, you are awesome. I just need a little more help with the syntax of where to put the PunchValue.ToString. Me.OleDbDeleteCommand1.CommandText = "DELETE FROM TIME_POST_WORK WHERE (Clock_Nbr = ?) AND (Seq_No = ?) AND (PunchValue.tostring("g")(Punch = ?))" & _ "" I am trying to learn this stuff from a book, and I probably need to find a good instruction course, especially since my employer is willing to pay for it. Thanks for you help again.
-
Here is the Code that I am using. It contains 2 other fields in the query as you can see, but that shouldn't affect how the DateTime (Punch) is displayed. This code was generated by VB: 'OleDbSelectCommand1 ' Me.OleDbSelectCommand1.CommandText = "SELECT Clock_Nbr, Punch, Seq_No FROM TIME_POST_WORK ORDER BY Punch" Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1 This is the code that I used to fill the Dataset and attempt to save back to the database: Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click OleDbDataAdapter1.Fill(DataSet11, "Time_Post_Work") End Sub Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click OleDbDataAdapter1.Update(DataSet11) As for testing my DataSet, in the Properties Window associated with the OleDbDataAdapter, I am clicking on the elipse next to the CommandText for the SELECT statement. This brings up a Query Builder window that contains "SELECT Clock_Nbr, Punch, Seq_No FROM TIME_POST_WORK ORDER BY Punch". I am running this query and it displays 'Punch' as a DateTime. If I use the Data Adapter Preview window the 'Punch' field only displays the Date.
Hi, U can try the following 'CAST' ------------------------------ SELECT Clock_Nbr, CAST(Punch AS VARCHAR(20)), Seq_No FROM TIME_POST_WORK ORDER BY Punch With Regards, Pandian S
-
Great, you are awesome. I just need a little more help with the syntax of where to put the PunchValue.ToString. Me.OleDbDeleteCommand1.CommandText = "DELETE FROM TIME_POST_WORK WHERE (Clock_Nbr = ?) AND (Seq_No = ?) AND (PunchValue.tostring("g")(Punch = ?))" & _ "" I am trying to learn this stuff from a book, and I probably need to find a good instruction course, especially since my employer is willing to pay for it. Thanks for you help again.
OK. The WHERE part of your delete query has three parameters, Clock_Nbr, Seq_No and Punch. Each one has a '?' placeholder. After this line of code you should have some more code that supplies the relevant values for each ? placeholder in your query. What code do you have for assigning these parameters? Actually, each record in your table should have an ID column which is an automatically assigned unique ID value. Typically, you just need to pass the ID of the record to delete it. What type of DB are you using? Steve
-
OK. The WHERE part of your delete query has three parameters, Clock_Nbr, Seq_No and Punch. Each one has a '?' placeholder. After this line of code you should have some more code that supplies the relevant values for each ? placeholder in your query. What code do you have for assigning these parameters? Actually, each record in your table should have an ID column which is an automatically assigned unique ID value. Typically, you just need to pass the ID of the record to delete it. What type of DB are you using? Steve
Here is the code generated by VB for the DELETE statement: Me.OleDbDeleteCommand1.CommandText = "DELETE FROM TIME_POST_WORK WHERE (Clock_Nbr = ?) AND (Seq_No = ?) AND (Punch = ?)" & _ "" Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection1 Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Clock_Nbr", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Clock_Nbr", System.Data.DataRowVersion.Original, Nothing)) Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Seq_No", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Seq_No", System.Data.DataRowVersion.Original, Nothing)) Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Punch", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Punch", System.Data.DataRowVersion.Original, Nothing)) Most of my development involves a SQL database, but for the purposes of this application, I am using an Access database. The table that I am querying actually has 28 cloumns, and all three of the elements that I am SELECTing or DELETEing are set as primary keys.
-
Here is the code generated by VB for the DELETE statement: Me.OleDbDeleteCommand1.CommandText = "DELETE FROM TIME_POST_WORK WHERE (Clock_Nbr = ?) AND (Seq_No = ?) AND (Punch = ?)" & _ "" Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection1 Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Clock_Nbr", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Clock_Nbr", System.Data.DataRowVersion.Original, Nothing)) Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Seq_No", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Seq_No", System.Data.DataRowVersion.Original, Nothing)) Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Punch", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Punch", System.Data.DataRowVersion.Original, Nothing)) Most of my development involves a SQL database, but for the purposes of this application, I am using an Access database. The table that I am querying actually has 28 cloumns, and all three of the elements that I am SELECTing or DELETEing are set as primary keys.
Try setting the OleDbType of Punch to OleDbType.DBTimeStamp. That will pass the date and time parts. However, you should get into the habit of creating an ID column for each of your tables. This should be the Primary Key of the table. This ID will uniquely identify each row in your tables. Then, all you have to do is pass the ID number in the WHERE clause of your UPDATE and DELETE queries. This is not only easier to read and control, but is more efficient since primary keys are always indexed. The index will be used by your DB engine to locate records much quicker and so will perform your commands with the highest efficiency. I don't know if any of the other columns could be used as a primary key? If so, since it is unique, all you have to do is pass that value so the DB knows which row to work on. The data in the other fields is irrelevant since all you need to do is identify the row to delete. Steve
-
Try setting the OleDbType of Punch to OleDbType.DBTimeStamp. That will pass the date and time parts. However, you should get into the habit of creating an ID column for each of your tables. This should be the Primary Key of the table. This ID will uniquely identify each row in your tables. Then, all you have to do is pass the ID number in the WHERE clause of your UPDATE and DELETE queries. This is not only easier to read and control, but is more efficient since primary keys are always indexed. The index will be used by your DB engine to locate records much quicker and so will perform your commands with the highest efficiency. I don't know if any of the other columns could be used as a primary key? If so, since it is unique, all you have to do is pass that value so the DB knows which row to work on. The data in the other fields is irrelevant since all you need to do is identify the row to delete. Steve
That worked like a champ. As for best practices in creating databases, I am certain that there are many issues with the design of the databases that I deal with, but I am not the developer and must therefore work with what I am given. Thanks for your help. I was nervous about posting questions on here, but it was a great experience. Thanks again.
-
That worked like a champ. As for best practices in creating databases, I am certain that there are many issues with the design of the databases that I deal with, but I am not the developer and must therefore work with what I am given. Thanks for your help. I was nervous about posting questions on here, but it was a great experience. Thanks again.
You are welcome! I'm pleased to help. Most of the people that provide answers on forums like this, do so because they like to help people. You shouldn't be nervous. We all started somewhere and I often have need to ask questions myself! Hope you voted for me! (Rate my answer) Steve