How to save excel sheet1 data, sheet2 data, sheet3 data on mysql database im using C#

Question

0.00/5 (No votes)

See more:

Hi everyone.

I have an excel sheet, sheet1, sheet2 and sheet3

I have a datareader that works to read and load excel files and sheets in to my datagridview, however

I want to save different sheet from different table on my database

How to do this? can any one help me to do this?

Thank's in advance

What I have tried:

C#

private void btnSaved_Click(object sender, EventArgs e)
        {
            int qtr = 0;
            string att_qtr = "ATTENDANCE";

            MySqlConnection con = new MySqlConnection("Server = DESKTOP-9H7QBOH; Database = sti_spms; UID = root; Password = 1234;");
                try
                {
                    string query = "INSERT INTO tbl_secondsem_grades(STUDENT_NO, NAME, SUBJECT, SECTION, GRADE, INITIAL_GRADE, QTR)" + "Values(@STUDENT_NO, @NAME, @SUBJECT, @SECTION, @GRADE, @INITIAL_GRADE, @QTR)";
                    query +="INSERT INTO tbl_attendance(STUDENT_ID, NAME, SUBJECT, SECTION, TOTAL_ABSENCES)" + "Values(@STUDENT_ID, @NAME, @SUBJECT, @SECTION, @TOTAL_ABSENCES)";
                    MySqlCommand cmd = new MySqlCommand(query, con);
                    DataTable dt = new DataTable();
                    con.Open();
                    for (int i = 0; i < dataGridView1.Rows.Count -1; i++)
                    {
                       
                        if(quarter.Equals("1st QTR"))
                        {
                            qtr = 1;
                        }
                        else if(quarter.Equals("2nd QTR"))
                        {
                            qtr = 2;
                        }
                        else if(quarter.Equals("3rd QTR"))
                        {
                            qtr = 3;
                        }
                        else if(quarter.Equals("4th QTR"))
                        {
                            qtr = 4;
                        }
                        else if (quarter.Equals("ATTENDANCE"))
                        {
                            att_qtr = "ATTENDANCE";
                        }
                        else
                        {
                            MessageBox.Show("Error!");
                        }

                        //int num = Convert.ToInt32(dataGridView1.Rows[i].Cells["STUDENT NO"].Value.ToString());
                        cmd.Parameters.AddWithValue("@STUDENT_NO", dataGridView1.Rows[i].Cells["STUDENT NO"].Value.ToString());
                        cmd.Parameters.AddWithValue("@NAME", dataGridView1.Rows[i].Cells["NAME"].Value.ToString());
                        cmd.Parameters.AddWithValue("@SUBJECT", dataGridView1.Rows[i].Cells["SUBJECT"].Value.ToString());
                        cmd.Parameters.AddWithValue("@SECTION", dataGridView1.Rows[i].Cells["SECTION"].Value.ToString());
                        cmd.Parameters.AddWithValue("@INITIAL_GRADE", dataGridView1.Rows[i].Cells["INITIAL GRADE"].Value.ToString());
                        cmd.Parameters.AddWithValue("@GRADE", dataGridView1.Rows[i].Cells["QG"].Value.ToString());
                        cmd.Parameters.AddWithValue("@QTR", qtr);
                        cmd.Parameters.AddWithValue("@STUDENT_ID", dataGridView1.Rows[i].Cells["STUDENT ID"].Value.ToString());
                        cmd.Parameters.AddWithValue("@NAME", dataGridView1.Rows[i].Cells["NAME"].Value.ToString());
                        cmd.Parameters.AddWithValue("@SUBJECT", dataGridView1.Rows[i].Cells["SUBJECT"].Value.ToString());
                        cmd.Parameters.AddWithValue("@SECTION", dataGridView1.Rows[i].Cells["SECTION"].Value.ToString());
                        cmd.Parameters.AddWithValue("@Total_Absences", dataGridView1.Rows[i].Cells["Total_Absences"].Value.ToString());
                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Data  Sucessfully Saved!");
                }
    
        }

Posted 20-Sep-17 5:53am

Member 13396840

Updated 21-Sep-17 4:06am

Add a Solution

Comments

Richard MacCutchan 20-Sep-17 12:52pm

What is the problem with your code?

Member 13396840 20-Sep-17 14:22pm

can't save the data from excel sheet (Attendance) to tbl_attendance sir it says the STUDENT_NO already define, so i tried to change the it to STUDENT_ID still it says STUDENT_ID already define

Richard MacCutchan 20-Sep-17 15:22pm

Presumably your STUDENT_ID field is an auto identity.

Member 13396840 21-Sep-17 3:45am

i don't know what to do sir

Richard MacCutchan 21-Sep-17 4:14am

Neither do I, because I have no real idea what your system is doing. If you get an error then you need to look at your code to see why it occurs. All you have told us is that the error you see is "it says the STUDENT_NO already define" which really tells us nothing. What is the definition of STUDENT_NO in your database, and where are you trying to define it a second time?

Member 13396840 21-Sep-17 9:33am

what my system doing is i'm importing an excel file which there's a sheet1(1st Qtr grade), sheet2(2nd Qtr Grade), and sheet3(Attendance) on it and then displayed it on datagridview then save it to my tbl_grades and tbl_attendance on my database. if i select sheet1, sheet2 or sheet3 to display it on datagridview it's working fine and when i saved the data from sheet1 and sheet2 on my tbl_grades its work fine too but when i saved the data from my sheet3 on tbl_attendance there's an error occuring on my system, my problem is how can i save the data from sheet3 to tbl_attendance?

Richard MacCutchan 21-Sep-17 9:51am

What error; please do not expect us to guess what you see when you run your code?

Member 13396840 21-Sep-17 9:58am

the error that i'm always encountered is that STUDENT_NO already define which i changed to STUDENT_ID but still i got error for this and said that STUDENT_ID already define.

Member 13396840 21-Sep-17 9:37am

sir did you experience to to import and load an excel file that has sheet1, sheet2 and sheet3 then saved the data from those sheets to different table in your database?? how you handle that kind of scenarios sir? using c#

Richard MacCutchan 21-Sep-17 9:54am

Your concatenation of two INSERT clauses does not look correct.

Member 13396840 21-Sep-17 9:59am

can you correct my concatenation of my two INSERT clauses sir?

Richard MacCutchan 21-Sep-17 10:04am

No, run them separately so you can catch any errors more easily. Try and break your code down into manageable steps so you can run it in your debugger and see exactly what is happening.

Member 13396840 21-Sep-17 10:11am

this code works fine sir but it only saved sheet1(1stQTR grade) and sheet2(2ndQTR)

private void btnSaved_Click(object sender, EventArgs e)
{
int qtr = 0;
string att_qtr = "Attendance";

MySqlConnection con = new MySqlConnection("Server = DESKTOP-9H7QBOH; Database = sti_spms; UID = root; Password = 1234;");
try
{
string query = "INSERT INTO tbl_secondsem_grades(STUDENT_NO, NAME, SUBJECT, SECTION, GRADE, INITIAL_GRADE, QTR)" + "Values(@STUDENT_NO, @NAME, @SUBJECT, @SECTION, @GRADE, @INITIAL_GRADE, @QTR)";
MySqlCommand cmd = new MySqlCommand(query, con);
DataTable dt = new DataTable();
con.Open();
for (int i = 0; i < dataGridView1.Rows.Count -1; i++)
{

if(quarter.Equals("1st QTR"))
{
qtr = 1;
}
else if(quarter.Equals("2nd QTR"))
{
qtr = 2;
}
else if(quarter.Equals("3rd QTR"))
{
qtr = 3;
}
else if(quarter.Equals("4th QTR"))
{
qtr = 4;
}
else if (quarter.Equals("Attendance"))
{
att_qtr = "Attendance";
}
else
{
MessageBox.Show("Error!");
}

//int num = Convert.ToInt32(dataGridView1.Rows[i].Cells["STUDENT NO"].Value.ToString());
cmd.Parameters.AddWithValue("@STUDENT_NO", dataGridView1.Rows[i].Cells["STUDENT NO"].Value.ToString());
cmd.Parameters.AddWithValue("@NAME", dataGridView1.Rows[i].Cells["NAME"].Value.ToString());
cmd.Parameters.AddWithValue("@SUBJECT", dataGridView1.Rows[i].Cells["SUBJECT"].Value.ToString());
cmd.Parameters.AddWithValue("@SECTION", dataGridView1.Rows[i].Cells["SECTION"].Value.ToString());
cmd.Parameters.AddWithValue("@INITIAL_GRADE", dataGridView1.Rows[i].Cells["INITIAL GRADE"].Value.ToString());
cmd.Parameters.AddWithValue("@GRADE", dataGridView1.Rows[i].Cells["QG"].Value.ToString());
cmd.Parameters.AddWithValue("@QTR", qtr);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}

}

Member 13396840 21-Sep-17 10:00am

i think i have a problem on this sir?
cmd.Parameters.AddWithValue("@STUDENT_NO", dataGridView1.Rows[i].Cells["STUDENT NO"].Value.ToString());
cmd.Parameters.AddWithValue("@NAME", dataGridView1.Rows[i].Cells["NAME"].Value.ToString());
cmd.Parameters.AddWithValue("@SUBJECT", dataGridView1.Rows[i].Cells["SUBJECT"].Value.ToString());
cmd.Parameters.AddWithValue("@SECTION", dataGridView1.Rows[i].Cells["SECTION"].Value.ToString());
cmd.Parameters.AddWithValue("@INITIAL_GRADE", dataGridView1.Rows[i].Cells["INITIAL GRADE"].Value.ToString());
cmd.Parameters.AddWithValue("@GRADE", dataGridView1.Rows[i].Cells["QG"].Value.ToString());
cmd.Parameters.AddWithValue("@QTR", qtr);
cmd.Parameters.AddWithValue("@STUDENT_ID", dataGridView1.Rows[i].Cells["STUDENT ID"].Value.ToString());
cmd.Parameters.AddWithValue("@NAME", dataGridView1.Rows[i].Cells["NAME"].Value.ToString());
cmd.Parameters.AddWithValue("@SUBJECT", dataGridView1.Rows[i].Cells["SUBJECT"].Value.ToString());
cmd.Parameters.AddWithValue("@SECTION", dataGridView1.Rows[i].Cells["SECTION"].Value.ToString());
cmd.Parameters.AddWithValue("@Total_Absences", dataGridView1.Rows[i].Cells["Total_Absences"].Value.ToString());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();

Member 13396840 21-Sep-17 10:07am

i have tbl_grades on my database, inside of it is STUDENT_NO, NAME, SUBJECT, SECTION, GRADE, INITIAL_GRADE and QTR. and i have tbl_attendance also on my database, inside of it is STUDENT_ID, NAME, SUBJECT, SECTION and TOTAL_ABSENCES

Richard MacCutchan 21-Sep-17 10:15am

Please slow down and think. You receive an error but you have still not told us exactly what the message is, or when and where it occurs.

Member 13396840 21-Sep-17 10:27am

sorry sir for late rply this the error ive encounter right now

Column named STUDENT NO cannot be found.
Parameter name: columnName

Richard MacCutchan 21-Sep-17 11:05am

That is just a simple spelling mistake that you should be can easily see.

Member 13396840 21-Sep-17 10:28am

is this correct sir?

cmd.Parameters.AddWithValue("@STUDENT_NO", dataGridView1.Rows[i].Cells["STUDENT NO"].Value.ToString());
cmd.Parameters.AddWithValue("@NAME", dataGridView1.Rows[i].Cells["NAME"].Value.ToString());
cmd.Parameters.AddWithValue("@SUBJECT", dataGridView1.Rows[i].Cells["SUBJECT"].Value.ToString());
cmd.Parameters.AddWithValue("@SECTION", dataGridView1.Rows[i].Cells["SECTION"].Value.ToString());
cmd.Parameters.AddWithValue("@INITIAL_GRADE", dataGridView1.Rows[i].Cells["INITIAL GRADE"].Value.ToString());
cmd.Parameters.AddWithValue("@GRADE", dataGridView1.Rows[i].Cells["QG"].Value.ToString());
cmd.Parameters.AddWithValue("@QTR", qtr);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@STUDENT_ID", dataGridView1.Rows[i].Cells["STUDENT ID"].Value.ToString());
cmd.Parameters.AddWithValue("@NAME", dataGridView1.Rows[i].Cells["NAME"].Value.ToString());
cmd.Parameters.AddWithValue("@SUBJECT", dataGridView1.Rows[i].Cells["SUBJECT"].Value.ToString());
cmd.Parameters.AddWithValue("@SECTION", dataGridView1.Rows[i].Cells["SECTION"].Value.ToString());
cmd.Parameters.AddWithValue("@Total_Absences", dataGridView1.Rows[i].Cells["Total_Absences"].Value.ToString());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();

Richard MacCutchan 21-Sep-17 11:12am

Obviously not; like I said earlier, slow down and think. You are just throwing random lines of code at this and hoping that somehow it will sort itself out.

Look at the table you are trying to update: what are the columns that require data? Write them down on a piece of paper.
Now create one INSERT statement for that table and those columns. Tick off each column on your list as you add it to the statement.
Now do the same for the value names in the INSERT statement.
Now add the parameters one at a time in a similar manner.
Now read through it again to see that you have spelled everything correctly.
With luck you should now be ready to process that statement. Do it in the debugger to check that all parameters contain the correct values. Check the result of the execute statement to see that it worked.
If an exception occurs, make sure you look at all the information inside it to see what it is telling you.

Member 13396840 21-Sep-17 13:03pm

thank you so much sir :)

Member 13396840 20-Sep-17 14:27pm

im trying to save different sheet like sheet1(1stQTR grade), sheet2(2ndQTR grade), sheet3(Attendance) to my different tables on my database but when i try to save the data from sheet3 im getting this error "STUDENT_NO Already Define"

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2017-09-21T04:06:00

Solution 1

C#

catch (Exception ex)
                {
                    MessageBox.Show("Data  Sucessfully Saved!");
                }

That is just beyond ridiculous.

Posted 21-Sep-17 4:06am

Richard MacCutchan

Comments

Member 13396840 21-Sep-17 10:13am

wrong code sir?

Richard MacCutchan 21-Sep-17 10:15am

Why are you putting out a success message when you catch an exception? Do you understand what exceptions are?

Member 13396840 21-Sep-17 10:20am

i'm sorry sir i'm working on ive changed it in MessageBox.Show(ex.Message);