I'm no VB expert. But here is some C# code.Hope you can get this converted.
//Read excel and return a dataTable
public DataTable readExcel()
{
// Create connection string variable. Modify the "Data Source"
// parameter as appropriate for your environment.
String sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Book1.xls;Extended Properties=Excel 12.0 Xml;";
// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(sConnectionString);
try
{
// Open connection with the database.
objConn.Open();
}
catch (Exception e)
{
throw e;
}
// The code to follow uses a SQL SELECT command to display the data from the worksheet.
// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();
// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData");
// Bind data to DataGrid control.
//DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
//DataGrid1.DataBind();
// Clean up objects.
objConn.Close();
return objDataset1.Tables["XLData"];
}
//read datatable and insert into Oracle
public int postCurrencyTable(DataTable currencyTable)
{
OleDbConnection conn = DBConnection.getConnection();
//Add a new column in datatable
DataColumn myDataColumn= new DataColumn();
myDataColumn.DataType = Type.GetType("System.DateTime");
myDataColumn.ColumnName = "sysdate";
currencyTable.Columns.Add(myDataColumn);
//get date from oralce server
String sysDate="";
OleDbCommand cmd = new OleDbCommand("", conn);
cmd.CommandText = "SELECT SYSDATE FROM DUAL";
OleDbDataReader reader = cmd.ExecuteReader();
reader.Read();
//while (reader.Read())
//{
//sysDate=reader.GetString(0);
sysDate = reader["SYSDATE"].ToString();
//}
reader.Dispose();
cmd.Dispose();
//add date into datatable
foreach (DataRow dr in currencyTable.Rows)
{
dr["sysdate"] = sysDate;
}
//You can Insert Now
foreach (DataRow dr in currencyTable.Rows)
{
string SQL = "INSERT INTO currencies_tab (curr_from, curr_to, rate, VALID_FROM) VALUES ( '" +
dr["CURR_FROM"].ToString() + "','" +
dr["CURR_TO"].ToString() + "'," +
dr["RATE"].ToString() + ",to_date('" +
dr["sysdate"].ToString().Remove(10) + "','DD/MM/YYYY'))";
OleDbCommand currCmd = new OleDbCommand(SQL, conn);
currCmd.ExecuteNonQuery();
}
return 0;
}