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

hi.. its very easy ..

You have to change some settings in "Region and Language Settings" in Control panel

See the attached thumbnail

Change Short Date Format from MM/dd/yyyy to dd/mm/yyy.
(if u dont find dd/mm/yyy. inthe option then dont worry just write it.) and apppy and see effect

Attachments regionalsettings.png 20.3 KB

is there any other way to do that because from client point of view it would be difficult to make Region and Language Settings and then start doing work on application it may be possible if user doesnt make regional settings and start doing entries in stock and when user will check the entries while using date search criteria then result will not be correct result due to change in dateformat and idea for that thxxxxxxxxxxx

hi sandeep i have changed all the column datatype from date to text now it works fine and i am using msaccess database is there any effect for changing the datatype from date to text i mean any effect of this on execution of code

This article has been dead for over six months. Start a new discussion instead.