Retrieve last updated on Database

Question

0.00/5 (No votes)

See more:

SQL-server-2000

, +

Imports System.Data.SqlClient
Public Class ProdA
    Dim com As New SqlConnection
    Dim cmd As SqlCommand
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try

    com.ConnectionString = "Data Source=" + Production.TextBox3.Text + ";Database=Database1;User Id=" + Production.TextBox1.Text + ";Password=" + Production.TextBox2.Text + ";"
            com.Open()

This is the procedure i'm using build the connection with my SQL SERVER. my DB has table called STOCKS_Details whose columns are fact1/fact2/fact3/total/DATE.So to do calculations i need to pick previous row DATE into a variable on a VB application. The Problem is how can I write the Query to retrieve value to the variable..

THANK YOU

Posted 30-Aug-12 19:31pm

Sharath2790

Updated 30-Aug-12 21:21pm

v2

Add a Solution

Comments

Santhosh Kumar Jayaraman 31-Aug-12 1:54am

what is previous row date?? you meant last record in the table? or yest date?

Sharath2790 31-Aug-12 1:57am

Only Date value from the last record in the table.

[no name] 31-Aug-12 2:37am

whether that table have identity column?

Sharath2790 31-Aug-12 2:45am

timestamp will be used...

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Answer 1 · 2012-08-30T20:50:00

Solution 4

last updated/added ?

1. for last updated record,
add timestamp column
and fire query

SQL

select * from tbl timestampCol=max(timestampCol)

2. for lastly added record,

SQL

--Insert statement then
select @@Identity

Happy Coding!
:)

Posted 30-Aug-12 20:50pm

Aarti Meswania

Karthik J, Coimbatore · Answer 2 · 2012-08-30T20:49:00

Hi Sharath2790!

1. If ur table have identity column use

SQL

select max(@@IDENTITY) from Images

2. If ur table doesn't have identity u need to create procedure

SQL

Create Procedure Get_LastRow

(

    @Tname Varchar(50)

)

AS

BEGIN


EXECUTE ('DECLARE GETLAST CURSOR DYNAMIC FOR SELECT * FROM ' + @Tname)
--select date from the above query

OPEN GETLAST

FETCH LAST FROM GETLAST

CLOSE GETLAST

DEALLOCATE GETLAST

END

and execute that procedure to get the result like

SQL

EXEC Get_LastRow 'dbo.TableName'

Accept the solution if this is helpful!!

Thanx
~JK

Kashinath Patil · Answer 3 · 2012-08-30T20:25:00

Solution 2

finding last updated value using
@@identity()

Posted 30-Aug-12 20:25pm

Kashinath Patil

Santhosh Kumar Jayaraman · Answer 4 · 2012-08-30T20:15:00

Solution 1

SQL

Select top 1 fact1,fact2,fact3,total,date from stock_details order by date desc.

Now write this query as sqlcommand and fill the output in datatable.

Then get value for each datatable column and set it to your variable.

i am writing sample code in c#.

C#

foreach(DataRow dr in datatable)
{
int fact1=Convert.ToInt32(dr["fact1"].ToString());
}

in VB

VB

For Each dr As DataRow In datatable
    Dim fact1 As Integer = Convert.ToInt32(dr("fact1").ToString())
Next

Updated solution.

If you need only max date column, then try this

SQL

Select max(Date) from stock_detail

s

Then

VB

Dim dt as datetime 
dt=Convert.ToDateTime(cmd.ExecuteS

calar());

Posted 30-Aug-12 20:15pm

Santhosh Kumar Jayaraman

Updated 30-Aug-12 21:30pm

v5

Comments

Sharath2790 31-Aug-12 2:25am

how can i write in vb.. i need to assign retrieve date value to a vb variable

Santhosh Kumar Jayaraman 31-Aug-12 2:28am

vb code
For Each dr As DataRow In datatable
Dim fact1 As Integer = Convert.ToInt32(dr("fact1").ToString())
Next

Sharath2790 31-Aug-12 2:46am

what about date part????

Santhosh Kumar Jayaraman 31-Aug-12 2:50am

date part? I have took top 1 record from table order by date as desc. So it will give me the row which is of max date in the table

Sharath2790 31-Aug-12 3:25am

ok! fine but my question is i need to assign that max date to a variable in vb..?? So how can i do that??

Santhosh Kumar Jayaraman 31-Aug-12 3:27am

If you want max(date) from the table no need of other columns? If so then its very simple.
Select max(date) from stockdetails will be your query.and use ExecuteScalar.
Datetime dt=cmd.ExecuteScalar() like this.