4 Replies - 7903 Views - Last Post: 28 November 2008 - 06:25 AM Rate Topic: -----

#1 detlion1643   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 04-November 08

displaying specific row of data

Posted 27 November 2008 - 07:36 AM

I have created a project that when I click a button, it inserts 3 textbox's data into a table in Access. This works flawlessy with testing. Now, I have put a datagridview on the form that links to the table and those same 3 fields. I can write queries that will populate the data, but my question is:
Is there anyway to populate the data grid view with only the last row in the table (no specifics to query, ie date, etc.).
When i click the button to fill in the data in Access, i want the data grid view to populate and display that last row only.

Here is the code that sends the textbox data into Access:
		'create a new connection to the database and insert the 3 textbox
		'data into the 3 corresponding fields in the Date_Time_Test_TT table
		Dim mycn As OleDb.OleDbConnection
		Dim Command As OleDb.OleDbCommand
		Dim SQLstr As String
		mycn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\pyeaney\My Documents\Timetrack.mdb;")
		Try
			SQLstr = "insert into Date_Time_Test_TT ([date], time_in, time_out) values('" & TextBox3.Text & "', '" & TextBox4.Text & "', '" & TextBox5.Text & "')"
			mycn.Open()
			Command = New OleDb.OleDbCommand(SQLstr, mycn)
			Command.ExecuteNonQuery()
			mycn.Close()
		Catch ex As Exception
			MessageBox.Show(ex.Message & " - " & ex.Source & ex.StackTrace)
			mycn.Close()
		End Try



Is This A Good Question/Topic? 0
  • +

Replies To: displaying specific row of data

#2 oldSwede   User is offline

  • D.I.C Regular
  • member icon

Reputation: 4
  • View blog
  • Posts: 464
  • Joined: 08-January 16

Re: displaying specific row of data

Posted 27 November 2008 - 08:19 AM

An idea...

If you are alone on the database and know that there are no concurrent writes or writes between your insert and checking the last line you could do somthing alog the lines of:
select top 1 from tableName order by keyColumn desc

But this is really not good. If you want a more robust solution look at this thread.

/Jens

This post has been edited by jens: 27 November 2008 - 08:29 AM

Was This Post Helpful? 0
  • +
  • -

#3 detlion1643   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 04-November 08

Re: displaying specific row of data

Posted 27 November 2008 - 08:58 AM

View Postjens, on 27 Nov, 2008 - 07:19 AM, said:

But this is really not good. If you want a more robust solution look at this thread.

/Jens


Thanks for the link jens.

On a side note, i am assuming that more than one person will use this in the future (but the inserts will be broken down by usernames) (i.e. john inserting times in a table for john's_times).

I am just trying to work out some kinks and think i was asking for a little too much. I have semi-solved the problem and am currently displaying the whole table, but selected the last row and resized the grid to show the last 3 without scrollbars.

DataGridView1.FirstDisplayedScrollingRowIndex = DataGridView1.RowCount - 1
		DataGridView1.Rows(DataGridView1.RowCount - 1).Selected = True



This is working for what i wanted to accomplish at the time being, but as the table grows bigger, there is no need to pull all of the information. Is there anyway to display the last 3 records (i know there is TOP() in SQL, but without an ordering field, it would need to be the last 3 inserted rows.

Hope this kind of makes sense!
Was This Post Helpful? 0
  • +
  • -

#4 oldSwede   User is offline

  • D.I.C Regular
  • member icon

Reputation: 4
  • View blog
  • Posts: 464
  • Joined: 08-January 16

Re: displaying specific row of data

Posted 27 November 2008 - 09:26 AM

I suppose you have an identity column in your data base table, a key column? If so it is probably an integer and probably auto increment.

Supposing the above is true you can use the above statement but modified to select top 3 from tableName order by identity desc . Be aware thought that "top" might not give the results you expect if there are concurrent writes - and you say there may be.

To be sure you'd have to use SCOPE_IDENTITY as described in the linked thread. I think somthing along the line of
select top 3 from tableName where identity <= IdentityYouGetWithScopeIdentityInOtherStatement order by identity desc 



BTW: Rather than making one table for Johns inserts and another one for Joes inserts you should add one column to your table where you write the identity of the user who inserted the data.

/Jens

This post has been edited by jens: 27 November 2008 - 09:30 AM

Was This Post Helpful? 0
  • +
  • -

#5 detlion1643   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 04-November 08

Re: displaying specific row of data

Posted 28 November 2008 - 06:25 AM

Thanks for all the info jens...

I decided to scrap the idea of the top 3 desc until i get a further understanding for the scope_identity (i haven't seen this before your thread).

I know this is a little off topic of my original decision, but if i add a column that will be used for naming identity, is there any way to pull the name from Windows or AD. Our login names are first initial + last name, but the description name at the top when you click the start button is our full fist name + last name. I would like to be able to use the description name, so when you add your times, it automatically fills in the naming column based on the description name of who is logged into Windows.

Sorry for the odd question in this topic, but i felt it not nessecary to open a new topic dealing with the same application.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1