i want to search date from MS Access database, using vb.net, ado.net, oledb
my sql query for it is not working,
i m using following query
sql= “select * from table where Date_col = ‘” & txtdate.text & “‘”
yes i set date format to short and dd/mm/yyyy both in control panel of system and MS Access Database table
my coding for database is
PurchaseDa.Dispose()
PurchaseDs.Clear()
PurchaseDt.Clear()
PurchaseConn.ConnectionString = “Provider=Microsoft.jet.oledb.4.0; Data Source=” & Pth & “\DisplayCenterData.mdb; User Id=admin; Password=;”
Dim dt As Date
dt = FormatDateTime(DatePurchaseFrm.Value, DateFormat.ShortDate)
MsgBox(dt)
PurchaseSql = “Select * from PUrchase where PurchaseDate => ‘” & txtDate.text &”‘”
PurchaseConn.ConnectionString = “Provider=Microsoft.jet.oledb.4.0; Data Source=” & Pth & “\DisplayCenterData.mdb; User Id=admin; Password=;”
PurchaseDa = New OleDbDataAdapter(PurchaseSql, PurchaseConn)
PurchaseDs = New DataSet
PurchaseDa.Fill(PurchaseDs, “DisplayCenterData”)
PurchaseDt = PurchaseDs.Tables(“DisplayCenterData”)
PurchaseCmb = New OleDbCommandBuilder(PurchaseDa)
PurchaseDa.InsertCommand = PurchaseCmb.GetInsertCommand
PurchaseDa.UpdateCommand = PurchaseCmb.GetUpdateCommand
i also tried following quries but non of them is working
” PurchaseSql = “Select * from PUrchase where PurchaseDate => ‘” & DatePurchaseFrm.Value.ToString(“dd-MM-yyyy”) & “‘ order By PUrchaseID”
”PurchaseSql = “Select * from PUrchase where PurchaseDate =@05/04/2010 order By PUrchaseID”
”PurchaseSql = “SELECT * FROM purchase WHERE PUrchaseDate between ‘” & Format(Me.DatePurchaseFrm.Value, “dd/dd/yyyy”) & “‘ AND ‘” & Format(Me.DatePurchaseTo.Value, “dd/MM/yyyy”) & “‘”
”PurchaseSql = “Select * from PUrchase where PurchaseDate ='” & DatePurchaseFrm.Value & “‘ order By PUrchaseID”
”PurchaseSql = “Select * from PUrchase where CONVERT(CHAR(10),PurchaseDate) =>” & DatePurchaseFrm.Value.ToString(“dd-mm-yyyy”) & ” order By PUrchaseID”
”PurchaseSql = “SELECT * FROM Purchase WHERE PurchaseDate>= DateValue(‘” & DatePurchaseFrm.Value & “‘)” & ” ORDER BY PurchaseID”
”PurchaseSql = “select * from Purchase”
”PurchaseSql = “Select * from PUrchase where CONVERT(CHAR(10),PurchaseDate,103) BETWEEN ‘” & DatePurchaseFrm.Value.ToString(“dd/MM/yyyy”) & “‘ AND ‘” & DatePurchaseTo.Value.ToString(“dd/MM/yyyy”) & “‘ order By PUrchaseID”
”PurchaseSql = “Select * From Employees Where HireDate Between #1/1/1990# and #12/31/2010#”
txtPurchaseID.DataBindings.Clear()
txtCompanyID.DataBindings.Clear()
txtBookID.DataBindings.Clear()
txtPurchaseDate.DataBindings.Clear()
txtPurchaseID.DataBindings.Add(“text”, PurchaseDt, “purchaseID”)
txtCompanyID.DataBindings.Add(“text”, PurchaseDt, “companyID”)
txtBookID.DataBindings.Add(“text”, PurchaseDt, “BookID”)
txtPurchaseDate.DataBindings.Add(“text”, PurchaseDt, “PUrchaseDate”)
please help to solve the problem
