1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| Private Sub lirebasedate()
Dim sqlSelect As String
Dim ConnSQL As New SqlConnection()
' Dim ReadSQL As SqlDataReader
Dim datedebut As String
Dim datefin As String
Dim stat As String
Dim leSite As String
ConnSQL.ConnectionString = "Data Source=DESKTOP-KBC80U4\SQLEXPRESS;Initial Catalog=ScadaNetDb1;Integrated Security=SSPI"
ConnSQL.Open()
datedebut = Format$(DateTimePicker1.Value, "yyyy-MM-dd")
datefin = Format$(DateTimePicker2.Value, "yyyy-MM-dd")
MsgBox("Date debut=" & datedebut & Chr(10) & "Date fin=" & datefin & Chr(10) & "N° site=" & valsite & Chr(10) & "N° room=" & valcpt)
leSite = " T1.STA_SiteNumber =" & valsite
stat = " T1.INF_NumberInStation =" & valcpt
'*************lecture water meter***************************
sqlSelect = ("WITH v as(SELECT T1.ID , T1.STA_SiteNumber, T1.INF_NumberInStation," _
& " T1.INF_Date, T1.INF_Value, row_number() over " _
& "(partition by T1.STA_SiteNumber, T1.INF_NumberInStation order by T1.INF_Date) rn_asc," _
& " row_number() over (partition by T1.STA_SiteNumber, T1.INF_NumberInStation " _
& " order by T1.INF_Date desc) rn_desc FROM dbo.View_ArchivedInformations AS T1 " _
& " where (convert(date, T1.INF_Date)>='" & datedebut & " ' ) and (convert(date, T1.INF_Date)<='" & datefin & " ')" _
& "and (" & leSite & " and " & stat & " )) " _
& " select * from v where rn_asc = 1 or rn_desc = 1; ")
'*****************
Dim dt As New DataTable
Dim oSqlDataAdapter As New SqlDataAdapter(sqlSelect, ConnSQL)
oSqlDataAdapter.Fill(dt)
MessageBox.Show(dt.Rows(0).Item(0))
'TextBox5.Text = dt.Rows(0).Item(0)
ConnSQL.Close()
End Sub |
Partager