473,605 Members | 2,665 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

1 New Member
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
0 1744

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

Similar topics

2
5128
by: Anita C | last post by:
Hi, How do I associate or map a specific column in a datatable to a particular element present in an xml document - to read into a datatable as well as write from the datatable to the xml element? Also, how can I associate all the attributes and their values of a particular element to the Name & Value columns of a datatable - to read into a datatable as well as write from the datatable to the xml element? Any help will be greatly...
18
18342
by: Dixie | last post by:
Can I set the Format property in a date/time field in code? Can I set the Input Mask in a date/time field in code? Can I set the Format of a Yes/No field to Checkbox in code? I am working on a remote update of tables and fields and can't find enough information on these things. Also, how do you index a field in code?
4
1597
by: Mervin Williams | last post by:
I have several tables involved in my application, but the two in question here are the company and address tables. The company table has business_address_id and mailing_address_id columns, which are both foreign keys to the address table. So, the stored procedure to which my SelectCommand points to reads as: ALTER PROCEDURE dbo.CompanyInfoByCompanyID ( @companyid int
2
3463
by: Jeff Thur | last post by:
I am trying to Execute a simple Stored Procedure using ASP/VB. I have spent numerous hours with this, researching books and looking on the internet. I can't get a direct answer. THis is my program, followed by the errors, Can anyone Please Help, Please, Please, Please. Sub cmdGO_Click(sender As Object, e As EventArgs) DataGrid1.DataSource = GetResults(TxtSort.Text) DataGrid1.DataBind()
15
2952
by: CR | last post by:
I've noticed that the trend these days is to declare variables in the middle of code instead of at the top. What is the advantage of this? It seems like it makes it hard to reuse variables. Here is how all the examples I've seen so far create an OleDbCommand Object: Dim cmd as new OleDbCommand("Select * FROM Table1",cnn) I had to figure out that it was the same as this:
52
3196
by: Julie | last post by:
I'm supporting an application at work. Below are some code segments that I can't understand how they work. First let me say, I would never code this standard. I'm just really creeped out that it works. Here's the setup. I have a function, called GetEmployeeCertifications, that is going to make a call to a SQL DB and return a result set. This module calls another function, called FillParameters, to determine if SQL parameters need to...
5
5341
by: Monty M. | last post by:
Hello; I was wondering if anyone can assist me with this problem. Here are the tools I am using: Language: C# Database: MS SQL Server 2000 Application: Visual Studio 2005 1. I have a table whose primary key is a varchar data type.
232
13218
by: robert maas, see http://tinyurl.com/uh3t | last post by:
I'm working on examples of programming in several languages, all (except PHP) running under CGI so that I can show both the source files and the actually running of the examples online. The first set of examples, after decoding the HTML FORM contents, merely verifies the text within a field to make sure it is a valid representation of an integer, without any junk thrown in, i.e. it must satisfy the regular expression: ^ *?+ *$ If the...
2
2971
by: Ryan Liu | last post by:
Hi, If I have a very big view in database, it covers 15 tables, each table has 1000 columns. When I issue select * from view, the database will give error -- too many columns. Can I use a DataAdapter fill DataTable couple times? For example, I only read 5000 columns from the view each time and I read 3 times. It has an id
0
8415
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8069
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
6742
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5886
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5445
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3912
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3958
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2438
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1537
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.