Data not inserting

Expand Post »

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

Similar Threads

The operation has timed out error while inserting data through web service in IT Professionals' Lounge
data insertion problem in php in PHP
Data inserting problem in ASP.NET
Get data out of excel file stored as an image in MS SQL
Why Data Structures???...QUESTIONS INSIDE in C++

sid.coco

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

13 posts
since Feb 2009

Permalink

Mar 5th, 2009

Re: Data not inserting

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

LizR

Reputation Points: 196

Solved Threads: 190

Posting Virtuoso

Offline

1,735 posts
since Aug 2008

Permalink

Mar 5th, 2009

Re: Data not inserting

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

C# Syntax (Toggle Plain Text)
...
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");
...
 
...
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.

C# Syntax (Toggle Plain Text)
//...
String query = " SELECT * FROM StudentDetails WHERE name = @name";
 
SqlCommand cmd = new SqlCommand(query,con);
cmd.Parameters.Add(new SqlParameter("@name", txtName.Text));
 
cmd.ExecuteNonQuery();
//...
String query = " SELECT * FROM StudentDetails WHERE name = @name";

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

cmd.ExecuteNonQuery();

Last edited by ChaseVoid; Mar 5th, 2009 at 2:07 pm.

ChaseVoid

Reputation Points: 40

Solved Threads: 12

Junior Poster

Offline

116 posts
since Aug 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in C# Forum Timeline: FileNet P8 4.0 .NET API

Next Thread in C# Forum Timeline: file path help

DaniWeb Message

Cancel Changes

User Name
Password