Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL2000 VB8.0 VB SQL SQL-Server , +
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
Edited 30-Aug-12 21:21pm
v2
Comments
Santhosh Kumar J at 31-Aug-12 1:54am
   
what is previous row date?? you meant last record in the table? or yest date?
Sharath2790 at 31-Aug-12 1:57am
   
Only Date value from the last record in the table.
Karthik_J, Coimbatore at 31-Aug-12 2:37am
   
whether that table have identity column?
Sharath2790 at 31-Aug-12 2:45am
   
timestamp will be used...
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi Sharath2790!
 
1. If ur table have identity column use

 select max(@@IDENTITY) from Images
 
2. If ur table doesn't have identity u need to create procedure
 
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
EXEC Get_LastRow 'dbo.TableName'
 
Accept the solution if this is helpful!!
 
Thanx
~JK
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

last updated/added ?
 
1. for last updated record,
add timestamp column
and fire query
select * from tbl timestampCol=max(timestampCol)
 

2. for lastly added record,
--Insert statement then
select @@Identity
Happy Coding!
Smile | :)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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#.
 
foreach(DataRow dr in datatable)
{
int fact1=Convert.ToInt32(dr["fact1"].ToString());
}
 
in 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
Select max(Date) from stock_detail
s
 
Then
Dim dt as datetime 
dt=Convert.ToDateTime(cmd.ExecuteS
calar());
  Permalink  
v5
Comments
Sharath2790 at 31-Aug-12 2:25am
   
how can i write in vb.. i need to assign retrieve date value to a vb variable
Santhosh Kumar J at 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 at 31-Aug-12 2:46am
   
what about date part????
Santhosh Kumar J at 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 at 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 J at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

finding last updated value using
@@identity()
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 553
1 George Jonsson 258
2 OriginalGriff 235
3 CPallini 210
4 PIEBALDconsult 155
0 OriginalGriff 5,865
1 Sergey Alexandrovich Kryukov 5,316
2 CPallini 4,760
3 George Jonsson 3,400
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 31 Aug 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100