Web Development



i want to search date using MS Access Database with vb.net, ado.net, oledb

By sindhux ·
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

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)

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.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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

So once you've built up the sql

by Tony Hopkinson In reply to i want to search date usi ...

What does it look like?

Right or wrong?

What do you mean by doesn't work, doesn't find the values you expect, or doesn't like your SQL?

A tip if you are going to do it this way, choose a universal date format.
yyyy-MM-dd always works.

Try something like this

SQL = "Select * From Table Where DateValue = @SearchDate"
Put that in the command then
Create a Parameter (oleDBParameter)?
name of "SearchDate", Type dbType,date, Value of DatePurchaseForm.Value
Add that to the command , then call fill.

You'll have to muddle through help and intellisense for teh exatct doings, I do C# and avoid access...

No more conversions to strings, and no more developing bad habits like building sql from user input. That's how SQL Injection attacks happen.

Related Discussions

Related Forums