|
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! 
|
|
|
|
|
You may want to try:
Shell("C:\Documents and Settings\wordpadtest.rtf", AppWinStyle.MaximizedFocus)
The application set to open .rtf files as a default should execute.
Steve
|
|
|
|
|
Put quotes around the filename:
Shell("C:\Program Files\Windows NT\Accessories\wordpad.exe ""C:\Documents and Settings\wordpadtest.rtf""", AppWinStyle.MaximizedFocus)
---
b { font-weight: normal; }
|
|
|
|
|
Actually got it running by doing the following:
Shell(Chr(34) & "C:\Program Files\Windows NT\Accessories\wordpad.exe" & Chr(34) & " " & Chr(34) & "C:\Documents and Settings\wordpadtest.rtf", AppWinSTyle.MaximizedFocus)
Thanks to all that replied to get this line of code punched out. 
|
|
|
|
|
Which gives the same result as:
Shell("""C:\Program Files\Windows NT\Accessories\wordpad.exe"" ""C:\Documents and Settings\wordpadtest.rtf", AppWinSTyle.MaximizedFocus)
However, you should use matching start and end quotes:
Shell("""C:\Program Files\Windows NT\Accessories\wordpad.exe"" ""C:\Documents and Settings\wordpadtest.rtf""", AppWinSTyle.MaximizedFocus)
---
b { font-weight: normal; }
|
|
|
|
|
Hi there, i need some help on how to customize the setup project for example, like changing to font and the style to xp styles and also need to change the form colors to white, i know it can be done coz i have seen it before, its just wen i open the user interface dialog and click on the forms for example welcome there is only three properties to set like a banner and the text and the copyright warning can anyone help me please this is really important and i need help as soon as possible ... Thanks 
MaryAndNaz
|
|
|
|
|
When i try to run an exe it gives error saying \\abc\d\e.mdb is not a valid path
So i made a folder where the exe was called abc in it sub folder d and in it pasted e.mdb still it gave me same error
Also when i tried to open the code it gave error that crviewer9.dll was missing
so i downloaded and registered the dll
but then when i click on any file in project with .dsr extention it can't open it
also when i try to compile the code it gives Unexpected Error 50003
I am using VB 6 and Crystal Reports 8.0
Don't know with what version of reports was the present code written
I tried ver 10 but got error saying not able to load files with dsr extention
Atleast with ver 8.0 it opens without any problem but am not able to complie
Thanks
-- modified at 8:59 Wednesday 16th August, 2006
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
