Need help with coding

[Aigini]

I want to create a code in vb.net 2008 that will delete data from its database table(Microsoft Access). The columns (attributes) in the database table are CWNo, Name, IC No, Nationality, Company, Join Date and Expiry Date.
Then it will insert data from a Microsoft Excel Sheet that contains the same attributes above into the same access database table.

Can you please give me a hint on how I can do this? Maybe you can provide me with an algorithm or even code that will be useful for me to complete the task above.

[regcure]

You first create an Connection object to connect to the Access DB. Then create a Command object with Command Text to delete the data from the table-Delete from Table. I hope you know how to do this.

Then create a Dataset and retrieve the Empty table and fill the Dataset.

Then use the following code to fill the Dataset from the Excel file:

Dim cn As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbDataAdapter
Dim ds As New System.Data.DataSet()

cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=C:\myData.XLS;Extended Properties=Excel 8.0;")

' Select the data from Sheet1 of the workbook.
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", cn)

cn.Open()
cmd.Fill(ds)
cn.Close()

Then use the Adapters Update Command to update the data in the Dataset to the Access Database.

Hope this helps. You can also find this code in MSDN.

[Aigini]

I've created the code below to create an Connection object to connect to the Access DB. Then create a Command object with Command Text to delete the data from the table-Delete from Table.
Is the code below correct?

Public Class frmMain

	Inherits System.Windows.Forms.Form
	
	Friend WithEvents OleConn As New System.Data.OleDb.OleDbConnection()
	Public Property DeleteCommand As OleDbCommand
	Public connectionString As String

	Dim instance As OleDbDataAdapter
	Dim value As OleDbCommand

	value = instance.DeleteCommand

	instance.DeleteCommand = value

	Using OleConn As New OleDBConnection(connectionString)

	Try
		OleConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data   source=" C:\CWMaster.mdb"
		OleConn.Open()
		Catch exc As Exception
				lblStatus.Text = exc.Message.ToString
	 End Try




	 Public Shared Function CreateEmployeeAdapter( OleConn As OleDbConnection) As OleDbDataAdapter 

		  Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter()
		  Dim command As OleDbCommand
		  Dim parameter As OleDbParameter

		  ' Create the SelectCommand.
		  command = New OleDbCommand("SELECT * FROM Customers ", OleConn)

		  dataAdapter.SelectCommand = command

		  ' Create the DeleteCommand.
		  command = New OleDbCommand( "DELETE * FROM Customers , OleConn)

		  parameter.SourceVersion = DataRowVersion.Original

		  dataAdapter.DeleteCommand = command

		  Return dataAdapter
	  End Function
		' The connection is automatically closed when the
		' code exits the Using block.
	End Using


End Class

[regcure]

heres the code:

Public Class frmMain

Inherits System.Windows.Forms.Form

Dim OleConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data source=" C:\CWMaster.mdb")

In Form_Load event or Button_Click event

Dim DeleteCommand As New OleDbCommand ("DELETE * FROM Customers”
, OleConn)

OleConn.Open ()

DeleteCommand.ExecuteNonQuery

OleConn.Close()

Dim cn As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbDataAdapter
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(cmd)
Dim ds As New System.Data.DataSet()

cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=C:\myData.XLS;Extended Properties=Excel 8.0;")

' Select the data from Sheet1 of the workbook.
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", cn)

cn.Open()
cmd.Fill(ds)
cmd.Update (ds, “Customers”)
cn.Close()

This should work.