Making SQL statements work
I have some databases that are Access files. These files are getting full and I am trying to create a program to remove some of the files not needed. The Problem that I have is the code, which I will post below, only deletes one table and not the others in the code. Am I missing something?
VB Code:
Dim xCon As New OleDb.OleDbConnection
Dim xCom As New OleDb.OleDbCommand
Dim xAdap As New OleDb.OleDbDataAdapter(xCom)
Dim xComBuild As New OleDb.OleDbCommandBuilder(xAdap)
Dim ConnnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Store2Office\Emp.mdb;Mode=ReadWrite;Persist Security Info=False"
Dim emp As String = "Emp.mdb"
Dim hqomfox As String = "hqomfox.mdb"
Dim s2o As String = "s2o.mdb"
Dim product As String = "product.mdb"
Dim s2ohourly As String = "s2ohourly.mdb"
Dim inventory As String = "inventory.mdb"
Dim composer As String = "composer.mdb"
Dim e2 As String = "e2.mdb"
'EMP.MDB and tables
xCom.CommandText = "Delete * from charges where s_date<#" & TextBox1.Text & "#"
xCom.ExecuteNonQuery()
xCom.CommandText = "Delete * from clockchanges where indate<#" & TextBox1.Text & "#"
xCom.ExecuteNonQuery()
xCom.CommandText = "Delete * from clockdata where indate<#" & TextBox1.Text & "#"
xCom.ExecuteNonQuery()
xCom.CommandText = "Delete * from mhj_schedule where s_date<#" & TextBox1.Text & "#"
xCom.ExecuteNonQuery()
xCom.CommandText = "Delete * from schedule where indate<#" & TextBox1.Text & "#"
xCom.ExecuteNonQuery()
xCom.Connection = xCon
xCon.ConnectionString = ConnnectionString
If xCon.State = ConnectionState.Closed Then
xCon.Open()
End If
xCom.ExecuteNonQuery()
xCon.Close()
Re: Making SQL statements work
You cannot execute a command if the connection is not open. You're calling ExecuteNonQuery five times before you open the connection, then you open the connection and call ExecuteNonQuery once. It's only that last one that will work. You have to open the connection first, then execute all your commands, then close the connection.
Also, don't use a TextBox for date entry. Use a DateTimePicker.
VB Code:
xCom.Connection = xCon
xCon.ConnectionString = ConnnectionString
xCon.Open()
xCom.Parameters.AddWithValue("@date", Me.DateTimePicker1.Value.Date)
xCom.CommandText = "DELETE FROM charges WHERE s_date < @date"
xCom.ExecuteNonQuery()
xCom.CommandText = "DELETE FROM clockchanges WHERE indate < @date"
xCom.ExecuteNonQuery()
xCom.CommandText = "DELETE FROM clockdata WHERE indate < @date"
xCom.ExecuteNonQuery()
xCom.CommandText = "DELETE FROM mhj_schedule WHERE s_date < @date"
xCom.ExecuteNonQuery()
xCom.CommandText = "DELETE FROM schedule WHERE indate < @date"
xCom.ExecuteNonQuery()
xCon.Close()
Re: Making SQL statements work
Thanks I will try that.. and I just checked out the datetimepicker. Thanks I like that idea.