By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 2,191 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

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

P: 1
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...
Expand|Select|Wrap|Line Numbers
  1. namespace CPHExcelReader
  2. {
  3. public partial class Form1 : Form
  4. {
  5. private Microsoft.Office.Interop.Excel.Application ExcelObj = null;
  6.  
  7. public Form1()
  8. {
  9. InitializeComponent();
  10. ExcelObj = new Microsoft.Office.Interop.Excel.Application();
  11. // See if the Excel Application Object was successfully constructed
  12. if (ExcelObj == null)
  13. {
  14. MessageBox.Show("ERROR: EXCEL couldn't be started!");
  15. System.Windows.Forms.Application.Exit();
  16. }
  17. // Make the Application Visible
  18. ExcelObj.Visible = true;
  19. }
  20.  
  21. private void Form1_Load(object sender, EventArgs e)
  22. {
  23. // prepare open file dialog to only search for excel files (had trouble setting this in design view)
  24.  
  25. // Here is the call to Open a Workbook in Excel
  26. // It uses most of the default values (except for the read-only which we set to true)
  27. 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);
  28. // get the collection of sheets in the workbook
  29. Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
  30. // get the first and only worksheet from the collection of worksheets
  31. Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
  32. // loop through 10 rows of the spreadsheet and place each row in the list view
  33. for (int i = 1; i <= 57; i++)
  34. {
  35. Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "B" + i.ToString());
  36. System.Array myvalues = (System.Array)range.Value2;
  37. string[] strArray = ConvertToStringArray(myvalues);
  38. dataGridView1.Rows.Add(strArray);
  39. int result = PopulateDatabase(strArray[0].ToString(), strArray[1].ToString());
  40. label1.Text += result + ": ";
  41. }
  42.  
  43. }
  44.  
  45. string[] ConvertToStringArray(System.Array values)
  46. {
  47. // create a new string array
  48. string[] theArray = new string[values.Length];
  49. // loop through the 2-D System.Array and populate the 1-D String Array
  50. for (int i = 1; i <= values.Length; i++)
  51. {
  52. if (values.GetValue(1, i) == null)
  53. theArray[i - 1] = "";
  54. else
  55. theArray[i - 1] = (string)values.GetValue(1, i).ToString();
  56. }
  57. return theArray;
  58. }
  59.  
  60. private int PopulateDatabase(string item, string price)
  61. {
  62. string conStr = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\CPHMaterial.mdf;Integrated Security=True;User Instance=True";
  63. DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
  64. // Obtain a database specific connection object
  65. DbConnection conn = factory.CreateConnection();
  66. // Set the connection string
  67. conn.ConnectionString = conStr;
  68. // Create a database specific command object
  69. DbCommand comm = conn.CreateCommand();
  70. // Set the command type to stored procedure
  71. comm.CommandType = CommandType.Text;
  72. comm.CommandText = "INSERT INTO MasterMaterialList(Item, Price) VALUES (@Item, @Price)";
  73. DbParameter param = comm.CreateParameter();
  74. param.ParameterName = "@Item";
  75. param.Value = item;
  76. param.DbType = DbType.String;
  77. param.Size = 50;
  78. comm.Parameters.Add(param);
  79. param = comm.CreateParameter();
  80. param.ParameterName = "@Price";
  81. param.Value = Decimal.Parse(price);
  82. param.DbType = DbType.Decimal;
  83. comm.Parameters.Add(param);
  84.  
  85. int success = -1;
  86. try
  87. {
  88. comm.Connection.Open();
  89. success = (int)comm.ExecuteNonQuery();
  90. }
  91. catch (Exception e)
  92. {
  93. label1.Text += e.Message;
  94. }
  95. finally
  96. {
  97. comm.Connection.Close();
  98. }
  99. return success;
  100. }
  101. }
  102. }
Feb 22 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.