I want to simply insert a few string values which i have successfully retrieved from an Excel Sheet. I have tried the "stored procedure" method but am not able to get what what i want. Just a simple C# code to insert value in SQL server 2005 using the SQLquery commands. Here's my code please tell me what's wrong in it
OleDbConnection ExcelCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileSource.Text + ";Extended Properties=Excel 8.0");
ExcelCon.Open();
try
{
//Create Dataset and fill with imformation from the Excel Spreadsheet for easier reference
DataSet ExcelDataSet = new DataSet();
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(" SELECT Name,Address,Contact FROM [" + txtSheetName.Text + "$]", ExcelCon);
ExcelAdapter.Fill(ExcelDataSet);
ExcelCon.Close();
txtJustCheck.Text = "DataSet Filled";
//Creating Database Connection
SqlConnection DBCon = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Students.mdf;Integrated Security=True;User Instance=True");
string qry;
//Travers through each row in the dataset
foreach (DataRow ExcelRow in ExcelDataSet.Tables[0].Rows)
{
if (ExcelRow["Name"].ToString() != "")
{
txtJustCheck.Text = ExcelRow["Name"].ToString();
//maxid = maxid + 1;
qry = "INSERT INTO StudentDetails(Name, Address, Contact, Institute, Course, Batch) VALUES('" + ExcelRow["Name"]+ "','" + ExcelRow["Address"]+ "','" + ExcelRow["Contact"]+ "','" + txtInstitute.Text + "','" + cbxCourse.Text + "','" + txtBatch.Text + "')";
DBCon.Open();
SqlCommand cmd = new SqlCommand("",DBCon);
//String SQLCommand = "BEGIN TRANSACTION\r\n";
//SQLCommand += qry;
//SQLCommand += "Commit Transaction";
cmd.CommandText = qry;
int Count = cmd.ExecuteNonQuery();
if (Count == 1)
{
txtJustCheck.Text = "Chal Raha Hai";
}
DBCon.Close();