Monday, 2 July 2012

How to Retrieve records from ms access using date field in condition

Hi...... there
As you know SQL is a language which enable us to use database. If you've knowledge of SQL then you can operate any database software, for achieve this you just need to know how to run SQL Statement in that DBMS package
One thing you must know that there are some different in SQL in different DBMS package they use different data type and size, they treat date field as different way so if you want to use SQL you must know what are the SQL specification for that DBMS package
Develop Raj Kumar
Form of project
  • MS Access treat date field differently from SQL server
  • If you want to make a condition based on date you'll have to put date between ## e.g #7/28/2010
  • Import System.Data.OleDb name space for oledb class

     

     

     

    Save Button Code

    Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your path")
            Try
                con.Open()
                Dim dd As String
                dd = DateTimePicker1.Value.Date
                Dim com As New OleDbCommand()
                com.Connection = con
                com.CommandText = "insert into stu values(" & TextBox1.Text & ",'" & TextBox2.Text & "','" & dd & "')"
                com.ExecuteNonQuery()
                MsgBox("Saved")
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

    Search Button Code

     Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your Path")
            Dim da As New OleDbDataAdapter("select * from stu where dob=#" & DateTimePicker1.Value.Date & "#", con)
            Dim ds As New DataSet
            da.Fill(ds)
            DataGridView1.DataSource = ds.Tables(0)