sql query between date in vb

Question

Try d1 = dtpfrom.Value.Date() d2 = dtpto.Value.Date() SQL = SELECT Student_ID FROM Ate_entry execComDB(SQL) reader = cmd.ExecuteReader() If reader.Read() Then reader.Close() Try SQL = SELECT Student_regnoStudent_StdStudent_branchAttendance_DateAttendance_StatusEntry_UserEntry_DateEntry_Time FROM Ate_entry where Student_ID=' & txtstuid.Text & ' and (Attendance_Date Between ' & d1 & ' AND ' & d2 & ') 'cmd.Parameters.AddWithValue(@from dtpfrom.Text) (SELECT * from stockitems where stdate Between # & _ dtDate1.ToString(MM/dd/yyyy) & # And # & _dtDate2.ToString(MM/dd/yyyy) & #con) 'cmd.Parameters.AddWithValue(@to dtpto.Text) execComDB(SQL).. .. I am using this code but it not including the year....only taking month and date....

Rajavel G · Answer

Form code: Public Class Student_attendance_Report Dim d1 As Date Dim d2 As Date Private Sub Student_attendance_Report_Load(sender As Object  e As EventArgs ) Handles MyBase .Load dtpfrom.CustomFormat = MM/dd/yyyy dtpto.CustomFormat = MM/dd/yyyy dtpto.MinDate = dtpfrom.Value.Date.AddDays(1) End Sub Private Sub btnsearch_Click(sender As Object  e As EventArgs ) Handles btnattendance.Click Try d1 = dtpfrom.Value.Date() d2 = dtpto.Value.Date() SQL = SELECT Student_ID FROM Ate_entry execComDB(SQL) reader = cmd.ExecuteReader() If reader.Read() Then reader.Close() Try cn.open() cmd.commandtext = SELECT Student_regnoStudent_StdStudent_branchAttendance_DateAttendance_StatusEntry_UserEntry_DateEntry_Time FROM Ate_entry where Student_ID=' & txtstuid.Text & ' and (Attendance_Date Between ' & d1 & ' AND ' & d2 & ') 'cmd.Parameters.AddWithValue(@from dtpfrom.Text) (SELECT * from stockitems where stdate Between # & _ dtDate1.ToString(MM/dd/yyyy) & # And # & _dtDate2.ToString(MM/dd/yyyy) & #con) 'cmd.Parameters.AddWithValue(@to dtpto.Text) cmd.executenonquery() reader = cmd.ExecuteReader() If reader.Read() Then reader.Close() Dim adapter As New SqlDataAdapter adapter.SelectCommand = cmd Dim dt As New DataTable adapter.Fill(dt) dgvattendancereport.DataSource = dt End If Catch ex As Exception MessageBox .Show(ex.Message) End Try reader.Close() ' attendancepercentage() Else MessageBox .Show( Not Exist ) reader.Close() End If reader.Close() Catch ex As Exception MessageBox .Show(ex.Message) End Try End Sub

Nitin Sontakke · Answer

Completely confused.... Can debug your code and paste what is the value of SQL variable? Then it is easy to spot the error. Don't know how you can directly concatenate d1 and d2 (which i think are of datetime data type) to string? Ensure that dates are in the format yyyy-MM-dd format when appended to SQL variable.