Importing data from excel to SQL database Error

Zimbini Walaza

Aug 26 2008 5:36 PM

4.1k

This is my code and it runs but when i click the import button it Gives me the error "Object reference not set to an instance of an object."

i don't know what the problem is plz help.i did add the microsoft.office.interop.excel reference.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Xml.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.Office.Interop.Excel;

using TimetableSchedDAC;

namespace ImportStudent

{

public partial class frmImportStudents : Form

{

private Microsoft.Office.Interop.Excel.Application ExcelObj = null;

string envChecker;

TimetableSchedDAL studentdal;

public frmImportStudents()

{

InitializeComponent();

}

public frmImportStudents(TimetableSchedDAL studObj,string EnvChecker)

{

studentdal = studObj;

envChecker = EnvChecker;

InitializeComponent();

ExcelObj = new Microsoft.Office.Interop.Excel.Application();

if (ExcelObj == null)

{

MessageBox.Show("ERROR: EXCEL couldn't be started!",

"Error : Starting Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);

System.Windows.Forms.Application.Exit();

}

ExcelObj.Visible = true;

}

private void btnBrowse_Click(object sender, EventArgs e)

{

lblResults.Visible = true;

this.ofd.FileName = "*.xlsx";

if(this.ofd.ShowDialog() == DialogResult.OK)

{

btnImport.Enabled = true;

}

lblResults.Text = "File successfully loaded!!";

}

private void frmImportStudents_Load(object sender, EventArgs e)

{

lblResults.Visible = false;

btnImport.Enabled = false;

}

private void btnImport_Click(object sender, EventArgs e)

{

lblResults.Visible = true;

ImportToStudent();

}

public void ImportToStudent()

{

try

{

string filename = ofd.SafeFileName;

string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "E:\\..\\PSAtechZTimetableSystem\\Spread.xlsx" + "; Extended Properties='Excel 8.0; IMEX=1; HDR=YES'";

// Here is the call to Open a Workbook in Excel

// It uses most of the default values (except for the read-only which we set to true)

Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(ofd.FileName, 0, true,5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true,true,true);

// get the collection of sheets in the workbook

Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

// get the first and only worksheet from the collection of worksheets

Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

using (SqlConnection conPSAtechZ = new SqlConnection(connection))

{

conPSAtechZ.ConnectionString = connection;

using (SqlCommand commandExcel = conPSAtechZ.CreateCommand())

{

System.Data.DataTable table = new System.Data.DataTable();

commandExcel.CommandText = "SELECT [StudentID] AS [Student_Number]," + "[ModuleCode] AS [Module_Code], " + "FROM [" + worksheet.Name.ToString() + "$]";

//Open the Excel Connection

conPSAtechZ.Open();

using (SqlDataReader dr = commandExcel.ExecuteReader(CommandBehavior.CloseConnection))

{

while (dr.Read() && dr.HasRows)

{

lblResults.Text = "Importing...";

try

{

studentdal.RegisterStudents(new RegisterStudent(Convert.ToString(dr["StudentID"]), Convert.ToString(dr["ModuleCode"])));

commandExcel.CommandType = CommandType.StoredProcedure;

lblResults.Text = "Successfully Imported!!";