EL Marshal Boraee Ответов: 3

Код запроса для самой ранней даты

I want to inquire about the nearest date and value with it by id and these are my attempts to find a solution


Что я уже пробовал:

sqlstr = "Select min(date_ex),qtt from table where ID =@id"
     cmd = New OleDbCommand(sqlstr, con)
     cmd.Parameters.Add(New OleDbParameter("@id", ad))
     con.Open()
     Dim dr As OleDb.OleDbDataReader = cmd.ExecuteScalar()
     If dr.Read() Then
         DateTimePicker1.Value = dr.Item(0)
         TextBox5.Text = dr.Item(1)
     End If


cmd = New OleDb.OleDbCommand(String.Format("select min(date_ex),qtt from table where ID = '{0}'", ad), con)
   con.Open()
   Dim dr As OleDb.OleDbDataReader = cmd.ExecuteReader
   If dr.Read() Then
       DateTimePicker1.Value = dr.Item(0)
       TextBox5.Text = dr.Item(1)
   End If

3 Ответов

Рейтинг:
15

EL Marshal Boraee

МОЖЕТЕ ЛИ ВЫ ПОПРОБОВАТЬ В ЭТОМ КОДЕ

For Each r As DataGridViewRow In dgvprodac.Rows
         Dim ad As Integer = Val(r.Cells(0).Value) 'THIS THE ID
         Dim txP As String = Val(r.Cells(1).Value)
         Dim xt As Single = Val(r.Cells(2).Value)
         Dim txC As Integer = Val(r.Cells(3).Value)
         'sqlstr = "SELECT sanf_kem FROM STORE JOIN (SELECT ID_sanf, MIN(date_ex) AS
          MD FROM STORE  GROUP BY ID_sanf) g ON g ID_sanf = @ID"
         'sqlstr = "SELECT g.MD, t.sanf_kem FROM STORE t JOIN (SELECT ID_sanf,
         MIN(date_ex) AS MD  FROM STORE  GROUP BY ID_sanf) g ON g.ID_sanf =
         t.ID_sanf"
         cmd = New OleDbCommand(sqlstr, con)
         cmd.Parameters.Add(New OleDbParameter("@id", ad))
         con.Open()
         Dim dr As OleDb.OleDbDataReader = cmd.ExecuteScalar()
         If dr.Read() Then
             DateTimePicker1.Value = dr.Item(0)
             TextBox5.Text = dr.Item(1)
         End If
     Next

Рейтинг:
0

OriginalGriff

Попробуйте что-нибудь вроде:

SELECT g.MD, t.qtt 
FROM MyTable t
JOIN (SELECT ID, MIN(date_ex) AS MD
      FROM MyTable 
      GROUP BY ID) g
ON g.ID = t.ID

Это вернет каждый идентификатор вместе с ним; самая ранняя дата, добавьте предложение WHERE, чтобы ограничить его только одним.

Рейтинг:
0

EL Marshal Boraee

правильный код

Dim cmd As OleDbCommand = New OleDbCommand("SELECT date_ex, sanf_kem from store WHERE ID_sanf =@pass ORDER BY  date_ex", con)
           cmd.Parameters.Add(New OleDbParameter("@pass", ad))
           If con.State = ConnectionState.Open Then
               con.Close()
           End If
           con.Open()
           Dim dr As OleDbDataReader = cmd.ExecuteReader
           While dr.Read()
               DateTimePicker1.Value = dr(0)
               TextBox5.Text = dr(1)
               con.Close()
               dr.Close()
               Exit While
           End While