|
I am trying to build an interface that returns a datetime data field using an OleDbDataAdapter. The CommandText used by the SelectCommand is "SELECT Punch FROM TIME_POST_WORK". If I run this query from within Query Builder the data returned is "6/19/2006 12:57:58 PM". However, if I try to fill my dataset by using the Preview Data assoctiated with the OleDbDataAdapter, it only returns "6/19/2006". I need to return the value as a DateTime, not just a Date only. I am using Visual Studio .NET 2003 and I get the same results if I am hitting an Access database or a SQL database.
|
|
|
|
|
Can you supply some of your code?
How are you testing what is in your DataSet?
Steve
|
|
|
|
|
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.
|
|
|
|
|
I'm quite confident that the data is present in the data adapter, and that it's the preview window that is showing only the date part.
DateTime values are almost never shown in a format that reveals the full resolution. A DateTime value has a resolution down to 0.0000001 seconds, but you never see seconds displayed with seven decimal places, do you?
---
b { font-weight: normal; }
|
|
|
|
|
That may be true, but for my purposes, I need to display both the Date and Time. I don't necessarily need the seconds, but I do need the hours and minutes. How can I force the display of date and time?
Thanks
|
|
|
|
|
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.
|
|
|
|
|
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'.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
We have an application that stores a list of recipients from our Exchange server (5.5) in an SQLServer database. We use LDAP to get the list. No problem.
We are migrating to a new Exchange server (6.5) and I have not been able to determine how to get the recipients list.
Any help would be appreciated.
Tim
|
|
|
|
|
I need concept and contents of HR Software. I also need code of HR software.
|
|
|
|
|
What do you mean by HR?
High Resolution? Human Resources?
|
|
|
|
|
I can shell to wordpad, no problem.
I can open up a .rtf file from a subdirectory such as "C:\temp\filename.rtf"
I CAN'T open up a .rtf file from a subdirectory such as "C:\Documents and Settings\filename.rtf". I.E.: A subdirectory with a space in it. My line of code is:
--------------------------------------------------------
Shell("C:\Program Files\Windows NT\Accessories\wordpad.exe C:\Documents and Settings\wordpadtest.rtf", AppWinStyle.MaximizedFocus)
--------------------------------------------------------
When I try that I get the error:
"C:\Documents
Cannot find this file. Please verify that the correct path and file name are given."
I can do something similar in Notepad opening up a .txt file with no problems.
Is this a bug?
-- modified at 9:39 Wednesday 16th August, 2006
|
|
|
|
|
Try this:
Shell("""C:\Program Files\Windows NT\Accessories\wordpad.exe"" C:\Documents and Settings\wordpadtest.rtf", AppWinStyle.MaximizedFocus)
Steve
|
|
|
|
|
Steve,
Thanks for the suggestion but same reply:
"c:\Documents
Cannot find this file.
Please verify that the correct path and file name are given.
Will tweak what you suggested though...see if something else comes of it.
Thanks again! 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
