Question Creating an "attendece form" issues

[AshleighK]

Hi

I designed an application where I register students, these records is added then to a database in my localdb using sqlserver etc.
everything works fine.

I want to create a form to do a "attendance register". Basically to select a date from a datetimepicker and then selecting, or by checking each student
present from 5 different classes. These attendances I want to export to an excel sheet and draw pie charts etc etc.

My problem is when I try to retrieve the data from my database into a list view box with only 3 fields(and not fields like address etc) I get the following error
" Executereader: Connection Property has not been initialized".

If someone could please help, or guide me to create this form/function to my existing program, I will really appreciate it.

Here is the code I used in my attendance from

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace test2
{
    public partial class attendence : Form
    {
        public SqlConnection cn = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog = Addressbook; Integrated Security = True; Connect Timeout = 30; Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
        public attendence()
        {
            InitializeComponent();


        }


        private void button1_Click(object sender, EventArgs e)
        {
                    }


        private void attendence_Shown(object sender, EventArgs e)
        {
            try
            {
                cn.Open();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                Application.ExitThread();
            }
        }


        private void btnPopulate_Click(object sender, EventArgs e)
        {
            listView1.Items.Clear();


            SqlCommand cm = new SqlCommand("SELECT * from BizContacts ORDER BY First_Name ASC,cn");
            try
            {
                SqlDataReader dr = cm.ExecuteReader();


                while (dr.Read())
                {
                    ListViewItem item = new ListViewItem(dr["First_Name"].ToString());
                    item.SubItems.Add(dr["Last_Name"].ToString());
                    item.SubItems.Add(dr["Class"].ToString());


                    listView1.Items.Add(item);


                }



            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    } 
}

 

[jmcilhinney]

That's because, as the error message says, you have set the Connection property of your command object. You can't execute a command against a database if you have opened a connection to that database first.

Looking more closely, I see the issue specifically. This:

SqlCommand cm = new SqlCommand("SELECT * from BizContacts ORDER BY First_Name ASC,cn");

should be this:

SqlCommand cm = new SqlCommand("SELECT * from BizContacts ORDER BY First_Name ASC", cn);

Even then though, you should be opening the connection before executing the command and closing it afterwards. Even better, you should not be using a class-level connection object but only creating one where you create the command.

You really ought not to be adding ListViewItems one by one like that either. If you read the documentation for that Add method, it will tell you what you should be doing.

 

[AshleighK]

Hi

I tried you're code and it worked fine.

I decided to use datagridviews instead.
The problem now is when I export the data in my datagridview, the headers and the first row of record does not load into my excel document.
From the 2nd row of data it does load in.

Can you maybe help me with this please?

{
        private void btnExcel_Click(object sender, EventArgs e)
        {
            _Application excel = new Microsoft.Office.Interop.Excel.Application();
            _Workbook workbook = excel.Workbooks.Add(Type.Missing);
            _Worksheet worksheet = null;
            try
            {
                worksheet = workbook.ActiveSheet;
                worksheet.Name = "Leerder Bywoning";
                for (int rowIndex = 0; rowIndex < dataGridView2.Rows.Count; rowIndex++)
                {
                    for (int colIndex = 0; colIndex < dataGridView2.Columns.Count; colIndex++)


                    {
                        if (rowIndex == 0)
                        {
                            worksheet.Cells[rowIndex + 1, colIndex + 1] = dataGridView2.Columns[colIndex].HeaderText;



                        }
                        else
                        {
                            worksheet.Cells[rowIndex, colIndex + 1] = dataGridView2.Rows[rowIndex].Cells[colIndex].Value.ToString();


                        }


                    }


                }
                   
                 if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                    {
                        workbook.SaveAs(saveFileDialog1.FileName);
                        Process.Start("excel.exe", saveFileDialog1.FileName);


                    }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel = null;
            }


        }


}

Kind Regards

 

[jmcilhinney]

This is the second time a moderator has added code formatting tags to your post. Please do it for us in future.

As for your issue, you don't get the first row of data because when your rowIndex is 0, i.e. when you're processing the first row, you get the headers instead. You then put those headers into row 1 of the worksheet, but you then overwrite that data when your rowIndex is 1.

What you need to do is loop through the columns and get the headers first, then loop through the rows and get the records, making sure that you start writing those records at the second row of the worksheet, under the headers instead of overwriting them.

You really should have been able to see this yourself if you debugged your code, which I'm guessing you haven't. If you don't know how to set breakpoints and step through code, now is the time to learn.