DaniWeb Forum Index > Software Development > C#

C# RSS

Data not inserting

Please support our C# advertiser: Intel Parallel Studio Home

•

Join Date: Feb 2009

Posts: 13

Reputation: sid.coco is an unknown quantity at this point

Solved Threads: 0

sid.coco

Offline

Newbie Poster

Data not inserting

Mar 5th, 2009

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

•

Join Date: Aug 2008

Posts: 1,735

Reputation: LizR has a spectacular aura about

Solved Threads: 186

LizR

Offline

Posting Virtuoso

Re: Data not inserting

Mar 5th, 2009

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

Did I just hear "You gotta help us, Doc. We've tried nothin' and we're all out of ideas" ? Is this you? Dont let this be you! I will put in as much effort as you seem to.

•

Join Date: Aug 2007

Posts: 111

Reputation: ChaseVoid is an unknown quantity at this point

Solved Threads: 12

ChaseVoid

Offline

Junior Poster

Re: Data not inserting

Mar 5th, 2009

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

 Help with Code Tags 
 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.

 Help with Code Tags 
 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();