i am trying to search between two dates startdate and end date there two problems problem given below
1. i have set the dtpSearchStockFrom and dtpSrchStockTo to dd/mm/yy when i enter into table it is inserted successfully but when i reterive data it show the format in datagrid as mm/dd//yyyy what whould i do this i mean why the data in dd/mm/yyyy format can't be retrieved even i enter in dd/mm/yyyy format ??
2. this problem has boother me alot and i am going to be mad with this problem is that there are 10 record with this format 1/5/2011 now when i use search criteria as dateFrom 1/1/2010 to 1/5/2011 it searchs all record but when i am trying to search record like 1/1/2010 to current date it doesn't retrieve all the records whose date is less the 12 i don't know why i guess it this type search create confusion to determine the month and date when date is less then 12 plzzzz help me about this iam trying to search for 4 days but still could get success but daniweb fourm always helped me in my learing THNKS IN ADVANCE TO ALL EXPERTS
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Try
DgvStock.DataSource = Nothing
Dim op As New Operations()
Dim dtStock As New DataTable()
Dim _strClause As New StringBuilder()
Dim _selectedCriteria As Integer = 0
If txtSrchInvoiceNo.Text.Trim.Length = 0 And dtpSearchStockFrom.Checked = False And dtpSrchStockTo.Checked = False And combSrchCompany.Text = "" And combSrchProd.Text = "" And combSrchModel.Text = "" Then
MsgBox("PLEASE ENTER VALUE TO SEARCH RECORD")
Return
End If
If txtSrchInvoiceNo.Text <> "" Then
_selectedCriteria += 1
_strClause.Append("InvoiceNo ='" & txtSrchInvoiceNo.Text & "'")
ElseIf _selectedCriteria > 0 Then
_strClause.Append("and InvoiceNo ='" & txtSrchInvoiceNo.Text & "'")
End If
If dtpSearchStockFrom.Checked = True Or dtpSrchStockTo.Checked = True Then
If _selectedCriteria = 0 Then
_selectedCriteria += 1
_strClause.Append("Dat between #" & dtpSearchStockFrom.Text & " 00:00:01 # and #" & dtpSrchStockTo.Text & " 23:59:59 #")
'_strClause.Append("Dat >= #" & dtpSearchStockFrom.Text & " # and Dat <= #" & dtpSrchStockTo.Text & "#")
ElseIf _selectedCriteria > 0 Then
_selectedCriteria += 1
_strClause.Append(" and Dat between #" & dtpSearchStockFrom.Text & " 00:00:01 # and #" & dtpSrchStockTo.Text & " 23:59:59 #")
'_strClause.Append(" and Dat >= #" & dtpSearchStockFrom.Text & " # and Dat <= #" & dtpSrchStockTo.Text & "#")
End If
End If
If combSrchCompany.Text <> "" Then
If _selectedCriteria = 0 Then
_selectedCriteria += 1
_strClause.Append("CompanyName ='" & combSrchCompany.Text & "'")
ElseIf _selectedCriteria > 0 Then
_selectedCriteria += 1
_strClause.Append("And CompanyName ='" & combSrchCompany.Text & "'")
End If
End If
If combSrchProd.Text <> "" Then
If _selectedCriteria = 0 Then
_selectedCriteria += 1
_strClause.Append("ProductName ='" & combSrchProd.Text & "'")
ElseIf _selectedCriteria > 0 Then
_selectedCriteria += 1
_strClause.Append("And ProductName ='" & combSrchProd.Text & "'")
End If
End If
If combSrchModel.Text <> "" Then
If _selectedCriteria = 0 Then
_selectedCriteria += 1
_strClause.Append("ModelName ='" & combSrchModel.Text & "'")
ElseIf _selectedCriteria > 0 Then
_selectedCriteria += 1
_strClause.Append("And ModelName ='" & combSrchModel.Text & "'")
End If
End If
dtStock = op.GetStockDataToGrid(_strClause.ToString())
Dim _RecordNo As Integer = dtStock.Rows.Count
txtTotalRecordNo.Text = _RecordNo.ToString
If dtStock.Rows.Count > 0 Then
DgvStock.DataSource = dtStock
Else
DgvStock.DataSource = dtStock
MsgBox("NO RECORD FOUND")
End If
Public Function GetStockDataToGrid(ByVal StringClause As String) As DataTable
Dim cmd As New OleDbCommand()
Dim dtStock As New DataTable
Try
Dim con As New Connection()
cmd.Connection = con.con
cmd.Connection.Open()
Dim da As New OleDbDataAdapter("SELECT * FROM Stock WHERE " & StringClause & " ", con.con)
da.Fill(dtStock)
Catch ex As Exception
MsgBox(ex.Message)
Logger.logException("Operations ", "GetStockDataToGrid ", ex.Message)
Finally
cmd.Connection.Close()
End Try
Return dtStock
End Function