CODEWITHSUNDEEP
sqlsql-servervb.net
Lem Pablo
Lem Pablo

Reputation: 21

Datediff + Looping vb.net and sqlserver

I have this table containing id and date, with a column diff with different values.

 ID |  Date  | Diff |
----------------------
  1 | 12/1/17|   1
----------------------
  2 | 12/2/17|   4
----------------------
  3 | 12/3/17|   5
----------------------

How to do if I trigger a button, then column DIFF will be updated based on DATEDIFF between date.today and on column Date. All I can do for now is get the date difference between my datetimepicker and datetoday, this is my code.

Dim diff As String = DateDiff(DateInterval.Day, CDate(DateTimePicker1.Value.Date), CDate(Date.Today)).ToString
MsgBox(diff)

and to display the table in a datagrid view

Try 
    Dim query As String = "select * from tblCondition Order By ID DESC"
    Dim adpt As New SqlDataAdapter(query, connection)
    Dim ds As New DataSet()
    adpt.Fill(ds, "tblCondition")
    DataGridView1.DataSource = ds.Tables(0)
Catch ex As Exception

End Try

can you help me out? im kinda new in vb.net :)

Upvotes: 2

Views: 183

Answers (1)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Use the following SQL inside the Button click event

UPDATE YourTable SET Diff = DATEDIFF(DAY,[Date],GETDATE()) WHERE Diff IS NULL

This will update all records having a null value in diff column with the number of Days difference between the Date column and current date.

Change the Parameter 1 to month , year , minutes etc to get the difference in your desired units

Upvotes: 1

Related Questions