CODEWITHSUNDEEP
vb.netms-accessdatetime
user2604026
user2604026

Reputation: 23

Delete all records from MS Access

I am using ms acces with 1 of the columns with name TIME as Date/Time type using the format dd/mm/yyy hh:nn:ss

For e.g.

TIME 06/03/ 2014 23:43:24

now using vb.net I have gotten this code off the net to insert data into the table Table1

Dim TrDate As DateTime = DateTime.Now
Dim CurrentDate = Format(TrDate, "yyyy/MM/dd hh:mm:ss tt")


Private Sub Timer7_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer7.Tick

    dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & FilePath)
    Try
        dbconn.Open()
        strSQL = "INSERT INTO Table1 ([TIME]) VALUES (?)"
        objcmd = New OleDbCommand(strSQL, dbconn)

        objcmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@TIME", CurrentDate))
        objcmd.ExecuteNonQuery()

        dbconn.Close()
    Catch myException As System.Exception
        Windows.Forms.MessageBox.Show(myException.Message)
    End Try
End Sub

// This query works fine 

  Private Sub Timer8_Tick_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer8.Tick
    dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & FilePath)
    Try
        Dim TrDate1 As Date = Date.Now
        Dim CurrentDate1 = Format(TrDate1, "yyyy/MM/dd hh:mm:ss tt")
        dbconn.Open()
        strSQL = "DELETE FROM Table1 WHERE TIME < CurrentDate1"
        objcmd = New OleDbCommand(strSQL, dbconn)
        objcmd.ExecuteNonQuery()

        dbconn.Close()
    Catch myException As System.Exception
        Windows.Forms.MessageBox.Show(myException.Message)
    End Try
End Sub

// Delete query doesnt execute giving the error "No value given for on eor //more parameters"

I wish to delete all the records from 1 day before today. I wish to run TIMER8 only once every day.

Upvotes: 1

Views: 1659

Answers (1)

Justin Paul Pa&#241;o
Justin Paul Pa&#241;o

Reputation: 936

You have an error in this line

strSQL = "DELETE FROM Table1 WHERE TIME < CurrentDate1"

You must concatenate the CurrentDate1 variable itself to the query like so:

strSQL = "DELETE FROM Table1 WHERE TIME < " & CurrentDate1

But the recommended way to construct SQL statements is to use a parameterized SQL statement just like what you did in the first snippet of code instead of string concatenation.

strSQL = "DELETE FROM Table1 WHERE TIME < ?"
objcmd = New OleDbCommand(strSQL, dbconn)
objcmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@TIME", CurrentDate1))
objcmd.ExecuteNonQuery()

Upvotes: 2

Related Questions