C# Code to fill database does not give error but does not fill database?

[dprjessie]

Hello,
I am a Web programmer and I'm working on my first desktop application as a favor for a friend. I'm sure I have a stupid error here, but there is no error being thrown so I can't figure out what is wrong. I have code that reads an excel file and fills a datagridview and it works just fine. It is also supposed to fill a database though and that is not working. The executeNonQuery command is returning a 1 which as I understand, means it has successfully updated the database. And as I said, no error is thrown. Yet when I open the database nothing has been added. If anyone can figure out what's wrong with my code I would really appreciate it. Like I said, this will be my first Windows form app and so I'm thinking there may be a problem with my connection string or something. But the fact that there is no error and 1 is returned is really confusing. Any help will be greatly appreciated, I'm driving myself crazy here! Here's the code...

namespace CPHExcelReader
{
public partial class Form1 : Form
{
private Microsoft.Office.Interop.Excel.Application ExcelObj = null;
 
public Form1()
{
InitializeComponent();
ExcelObj = new Microsoft.Office.Interop.Excel.Application();
// See if the Excel Application Object was successfully constructed
if (ExcelObj == null)
{
MessageBox.Show("ERROR: EXCEL couldn't be started!");
System.Windows.Forms.Application.Exit();
}
// Make the Application Visible
ExcelObj.Visible = true;
}
 
private void Form1_Load(object sender, EventArgs e)
{
// prepare open file dialog to only search for excel files (had trouble setting this in design view)
 
// 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("C:\\Documents and Settings\\Jessie Martin\\My Documents\\Visual Studio 2005\\Projects\\CPHExcelReader\\CPHExcelReader\\UNDERSLAB.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, false, false, false);
// 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);
// loop through 10 rows of the spreadsheet and place each row in the list view
for (int i = 1; i <= 57; i++)
{
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "B" + i.ToString());
System.Array myvalues = (System.Array)range.Value2;
string[] strArray = ConvertToStringArray(myvalues);
dataGridView1.Rows.Add(strArray);
int result = PopulateDatabase(strArray[0].ToString(), strArray[1].ToString());
label1.Text += result + ": ";
}
 
}
 
string[] ConvertToStringArray(System.Array values)
{
// create a new string array
string[] theArray = new string[values.Length];
// loop through the 2-D System.Array and populate the 1-D String Array
for (int i = 1; i <= values.Length; i++)
{
if (values.GetValue(1, i) == null)
theArray[i - 1] = "";
else
theArray[i - 1] = (string)values.GetValue(1, i).ToString();
}
return theArray;
}
 
private int PopulateDatabase(string item, string price)
{
string conStr = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\CPHMaterial.mdf;Integrated Security=True;User Instance=True";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
// Obtain a database specific connection object
DbConnection conn = factory.CreateConnection();
// Set the connection string
conn.ConnectionString = conStr;
// Create a database specific command object
DbCommand comm = conn.CreateCommand();
// Set the command type to stored procedure
comm.CommandType = CommandType.Text;
comm.CommandText = "INSERT INTO MasterMaterialList(Item, Price) VALUES (@Item, @Price)";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@Item";
param.Value = item;
param.DbType = DbType.String;
param.Size = 50;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@Price";
param.Value = Decimal.Parse(price);
param.DbType = DbType.Decimal;
comm.Parameters.Add(param);
 
int success = -1;
try
{
comm.Connection.Open();
success = (int)comm.ExecuteNonQuery();
}
catch (Exception e)
{
label1.Text += e.Message;
}
finally
{
comm.Connection.Close();
}
return success;
}
}
}