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();

What Id suggest is look at the value of qry, check it visually for validity, if ok, then run it direct in something like msaccess or sql query builder or such, and see if you get an error, such as some value is a dup, its missing a required field, or whatever

Have you tried to use the Update method of the Adapter Class?

...
DataSet sqlDS = new DataSet("StudentDetails);
adapter.Fill(sqlDS, "StudentDetails");

foreach(DataRow dr in excelDS.Tables["StudentDetails"].Rows)
{
    sqlDS.Tables["StudentDetails"].Rows.Add(dr);
}

adapter.Update(sqlDS, "StudentDetails");
...

This will copy all data from excel to sql, if the structure of the data is the same. Of course there are better ways, but I haven't used ADO.net for a while so I'm unable to come up with a better solution.

Hope it helps. Please inform me okay.

Opps, forgot to mention about parametrized quires. They really help you know.

//...
String query = " SELECT * FROM StudentDetails WHERE name = @name";

SqlCommand cmd = new SqlCommand(query,con);
cmd.Parameters.Add(new SqlParameter("@name", txtName.Text));

cmd.ExecuteNonQuery();
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.