14 Replies - 50845 Views - Last Post: 09 July 2009 - 12:17 AM

#1 davkav   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 35
  • Joined: 11-March 08

How to Insert record at top row of table? (MS SQL Server)

Posted 08 April 2008 - 02:46 AM

Hi Guys,

Ive done some searching and strangley I couldnt find an answer. I thought this would have come up often enough :rolleyes:

Basically all I want to do is write a new record to Row No.1 in my table and move the present data in the table down i.e. Row 1 moves to 2 and Row 2 moves to 3 and so on...


	sqlStr = "insert Tank_Level(Date_Time,Tank_Level1,Tank_Level2,Setpoint1,Setpoint2) Values('" & Now() & "','" & MyText(1).Text & "','" & MyText(2).Text & "','" & frmStatus.MyText(5).Text & "','" & frmStatus.MyText(6).Text & "')"




I have the above code in my VB program.

Table name is Tank_Level

Column names are:
Date_Time
Tank_Level1
Tank_Level2
Setpoint1
Setpoint2

Oh one more thing, if i want to read from my database into excel i use this code
	 MyConnObj.Open "PROVIDER=MSDASQL;driver={SQL Server};server=PC125\SQLEXPRESS;uid=;pwd=;database=TEST;"
	 
	 sqlStr = "select * from Tank_Level"
		 
	 myRecSet.Open sqlStr, MyConnObj, adOpenKeyset

	 frmBPage.Print "Date / Time"; Tab; Tab; "Tank Level 1"; Tab; "Tank Level 2"; Tab; "Setpoint 1"; Tab; "Setpoint 2";
	 frmBPage.Print ""
	 
	 While Not myRecSet.EOF
		 
		 frmBPage.Print myRecSet(0); Tab; myRecSet(1); Tab; myRecSet(2); Tab; myRecSet(3); Tab; myRecSet(4); vbNewLine;
		 myRecSet.MoveNext
	
	 Wend




How can i just pull the last 5 entries in the database as in the latest 5?

Thanks again guys

This post has been edited by davkav: 08 April 2008 - 03:45 AM


Is This A Good Question/Topic? 0
  • +

Replies To: How to Insert record at top row of table? (MS SQL Server)

#2 davkav   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 35
  • Joined: 11-March 08

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 15 April 2008 - 02:47 AM

Bump?
Was This Post Helpful? 0
  • +
  • -

#3 1lacca   User is offline

  • code.rascal
  • member icon

Reputation: 44
  • View blog
  • Posts: 3,822
  • Joined: 11-August 05

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 15 April 2008 - 04:19 AM

Usually it is the internal matter of the database where your record is stored.
Even two consecutive select query is allowed to return the results in different order (if no sorting is specified)
So if you want to sort your records in a specific order, then specify an order by clause in your select and a column that defines the place of the given record. You can put the rank of that record into your custom column.
When you want to push the whole thing down, simply run an update that increases the rank by one, and set the rank of the new record to one.
Was This Post Helpful? 0
  • +
  • -

#4 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7505
  • View blog
  • Posts: 15,553
  • Joined: 16-October 07

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 15 April 2008 - 07:15 AM

What 1lacca said, database don't care about order. Now you said:

View Postdavkav, on 8 Apr, 2008 - 05:46 AM, said:

Basically all I want to do is write a new record to Row No.1 in my table and move the present data in the table down i.e. Row 1 moves to 2 and Row 2 moves to 3 and so on...


WHY?!? This is a very bad idea. There's a reason no one would tell you how to do it. You should never be concerned with who's first, only if there is valid data that you can use to get the results you need. So, you want to order the data by based on sequence of insertion? Not a problem, SQL Server has an auto number mechanism built in...

In your table, add an IDENTITY column. We'll call it Seq. You usually plan for them in table creation, e.g.
create table Tank_Level(
	Seq int IDENTITY(1,1) NOT NULL,
	Date_Time datetime...



But you can put it in with an "Alter Table" if you need to.

Now, the identity column is sequential, it increments on each insert. You want the last row to be the first row? Simple, just present it that way:
select * from Tank_Level order by Seq desc



You can even grab the current max number, if you want to display Seq in a reverse order:
select (Seq-IDENT_CURRENT('Tank_Level'))+1 as Seq
	from Tank_Level 
	order by Seq desc



Hope this helps.
Was This Post Helpful? 1

#5 2457   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 72
  • Joined: 09-April 08

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 15 April 2008 - 06:41 PM

The beuty of database like this, is You sort the data qhen You query it.
It will allways be sorted by some logic, do not treat data tables like excel tables.

So, if You add an "auto increasement" ID to each row, and query by that You can sort the rows by time of adding them relative each other.
(mm so the highest ID will be the lastone added, the lowest ID will be the firstone added)
Was This Post Helpful? 0
  • +
  • -

#6 davkav   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 35
  • Joined: 11-March 08

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 16 April 2008 - 02:55 AM

Thanks guys. Yeah i think i have it stuck in my head thinking like excel.

Never even dawned on me to use a Index column, im a noob at DB :)

One thing though. I have the Seq column as the last column in my design and it auto increments. but when i try to write to the database now, in VB, its asking for data to be written because i now have 6 columns.

	sqlStr = "insert Tank_Level(Date_Time,Tank_Level1,Tank_Level2,Setpoint1,Setpoint2) Values('" & Now() & "','" & MyText(1).Text & "','" & MyText(2).Text & "','" & frmStatus.MyText(5).Text & "','" & frmStatus.MyText(6).Text & "')"




I suppose i want something like this
	sqlStr = "insert Tank_Level(Date_Time,Tank_Level1,Tank_Level2,Setpoint1,Setpoint2,Seq) Values('" & Now() & "','" & MyText(1).Text & "','" & MyText(2).Text & "','" & frmStatus.MyText(5).Text & "','" & frmStatus.MyText(6).Text & "'#########)"



I inserted Seq at the end of the insert statement but what do i put in the values statement where the hashes are?
Was This Post Helpful? 0
  • +
  • -

#7 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7505
  • View blog
  • Posts: 15,553
  • Joined: 16-October 07

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 16 April 2008 - 05:47 AM

The first syntax looks fine, except I believe "insert INTO Tank_Level" may be required. You never insert an auto increment field, they won't even let you unless you ask real nice.

Also, if all you're doing for Date_Time is passing a now, you should let the database pick the current time. e.g.
sqlStr = "insert into Tank_Level(Date_Time,Tank_Level1,Tank_Level2,Setpoint1,Setpoint2) Values(" _
	  & GetDate()," _
	  & "'" & MyText(1).Text & "','" & MyText(2).Text & "','" & frmStatus.MyText(5).Text & "','" & frmStatus.MyText(6).Text _
	  & "')"



Even better you can make the Date_Time have a default value of GetDate() and never have to insert it again.

Also, please use bind parameters. Friends don't let friends get SQL Injected. :P
Was This Post Helpful? 0
  • +
  • -

#8 davkav   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 35
  • Joined: 11-March 08

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 16 April 2008 - 06:37 AM

Legendary Thanks Guys! :^: :D :^: :D :genius:

This post has been edited by davkav: 16 April 2008 - 06:38 AM

Was This Post Helpful? 0
  • +
  • -

#9 ultimitloozer   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 6
  • Joined: 27-June 08

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 28 June 2008 - 09:11 PM

It looks like this Seq field is not even required. The entire process could have been done using the Date_Time field instead. A simple descending sort on that field would yield the same results.
Was This Post Helpful? 0
  • +
  • -

#10 1lacca   User is offline

  • code.rascal
  • member icon

Reputation: 44
  • View blog
  • Posts: 3,822
  • Joined: 11-August 05

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 29 June 2008 - 04:21 AM

Theoretically (and in practice) two records can have the same date_time value.
Was This Post Helpful? 0
  • +
  • -

#11 ultimitloozer   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 6
  • Joined: 27-June 08

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 29 June 2008 - 05:28 AM

If the date_time field is of the datetime data type, its granularity is approximately 3.33ms. Unless the data is being inserted faster than one every 5 ms, the data in that field should be unique. (And if he is pushing data in that fast, I would hate to be the one ensuring that he has enough drive space for the massive growth of the database files!) This would change if the data type is smalldatetime which has a granularity of about 1 minute and would push the gap between inserts to 90 seconds to guarantee a unique stamp.
Was This Post Helpful? 0
  • +
  • -

#12 1lacca   User is offline

  • code.rascal
  • member icon

Reputation: 44
  • View blog
  • Posts: 3,822
  • Joined: 11-August 05

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 29 June 2008 - 06:06 AM

View Postultimitloozer, on 29 Jun, 2008 - 02:28 PM, said:

If the date_time field is of the datetime data type, its granularity is approximately 3.33ms. Unless the data is being inserted faster than one every 5 ms, the data in that field should be unique. (And if he is pushing data in that fast, I would hate to be the one ensuring that he has enough drive space for the massive growth of the database files!)

It is enough if data is pushed at the same time once, and it CAN happen only if a total of 2 records are inserted (Murphy's law and some nice examples at the Daily WTF). I know that this seems to be a theoretical debate from one side, but such design decisions can lead to systems failing spectacularly and misteriously. Sequences/auto increment fields were invented for a reason, and this is one of them. It is not the only solution, but using any kind of date_time type in such a scenario is a design fault.
Was This Post Helpful? 0
  • +
  • -

#13 Trogdor   User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 30 June 2008 - 06:28 AM

Also, think what happens when the datefield is inserted with a nullvalue, or a '0' (setting the date to 1/1/1900 00:00:00)
Just use an ID field, not doing it is something most people know to avoid by now.
Was This Post Helpful? 0
  • +
  • -

#14 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7505
  • View blog
  • Posts: 15,553
  • Joined: 16-October 07

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 30 June 2008 - 09:09 AM

View Post1lacca, on 29 Jun, 2008 - 09:06 AM, said:

I know that this seems to be a theoretical debate from one side, but such design decisions can lead to systems failing spectacularly and misteriously. Sequences/auto increment fields were invented for a reason, and this is one of them. It is not the only solution, but using any kind of date_time type in such a scenario is a design fault.


I agree here completely. Unless it has a unique constraint, it's simply not a valid unique identifier. If you make a datetime your primary key, you will find a time where a record is rejected because it violates this constraint. Maybe not immediately, but it will happen.

It's also not a question of volume. Databases are multi user systems. With just two users you can find a way to collide. The acid test for stability is not that something shouldn't happen, but that, to the best of your knowledge, it can't.
Was This Post Helpful? 0
  • +
  • -

#15 shakeb   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 18-June 09

Re: How to Insert record at top row of table? (MS SQL Server)

Posted 09 July 2009 - 12:17 AM

THANKS BUDDY
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1