Error during update using ms-access

Question

0.00/5 (No votes)

See more:

Want to update my data(ms-access) using the values from a datagrid. The error is showing- Syntax error in UPDATE statement. The Code is shown below . Please help .

What I have tried:

C#

private void updateToolStripMenuItem_Click(object sender, EventArgs e)
        {
            cc.con.Open();
            string date = Convert.ToDateTime(dgvReportFees.SelectedRows[0].Cells[0].Value).ToString("MM-dd-yyyy");
            //string date = Convert.ToString(dgvReportFees.SelectedRows[0].Cells[0].Value.ToString());
             int ReceiptNo = Convert.ToInt32(dgvReportFees.SelectedRows[0].Cells[1].Value);
            string studentname = dgvReportFees.SelectedRows[0].Cells[2].Value.ToString();
            int regno = Convert.ToInt32(dgvReportFees.SelectedRows[0].Cells[3].Value);
            string fathername = dgvReportFees.SelectedRows[0].Cells[4].Value.ToString() ;
            string mothername = dgvReportFees.SelectedRows[0].Cells[5].Value.ToString() ;
            int roomno = Convert.ToInt32(dgvReportFees.SelectedRows[0].Cells[6].Value);
            string occupancy = dgvReportFees.SelectedRows[0].Cells[7].Value.ToString();
            string paymenttype = dgvReportFees.SelectedRows[0].Cells[8].Value.ToString();
            string chequeno = dgvReportFees.SelectedRows[0].Cells[9].Value.ToString();
            string chequedate = Convert.ToDateTime(dgvReportFees.SelectedRows[0].Cells[10].Value).ToString("MM-dd-yyyy");
            string bank = dgvReportFees.SelectedRows[0].Cells[11].Value.ToString();
            int chequeamount = Convert.ToInt32(dgvReportFees.SelectedRows[0].Cells[12].Value);
            int regfee = Convert.ToInt32(dgvReportFees.SelectedRows[0].Cells[13].Value);
            int security = Convert.ToInt32(dgvReportFees.SelectedRows[0].Cells[14].Value);
            int monthlyrent = Convert.ToInt32(dgvReportFees.SelectedRows[0].Cells[15].Value);
            int no_of_monthrent = Convert.ToInt32(dgvReportFees.SelectedRows[0].Cells[16].Value);
            int dayrent = Convert.ToInt32(dgvReportFees.SelectedRows[0].Cells[17].Value);
            int latefee = Convert.ToInt32(dgvReportFees.SelectedRows[0].Cells[18].Value);
            int servicetax = Convert.ToInt32(dgvReportFees.SelectedRows[0].Cells[19].Value);
            int total = Convert.ToInt32(dgvReportFees.SelectedRows[0].Cells[20].Value);

            
            string query = " UPDATE tblFees SET Date ='" + date + "',  Receipt_No = " + ReceiptNo + " , Student_Name ='" + studentname + "', Registration_No = " + regno + ", Father_Name ='" + fathername + "', Mother_Name ='" + mothername + "', Room_No = " + roomno + ", Occupancy ='" + occupancy + "', Payment_Type ='" + paymenttype + "', Cheque_No ='" + chequeno + "', Cheque_Date = '" + chequedate + "', Bank_Name ='" + bank + "', Cheque_Amount = " + chequeamount + ", Registration_Fee = " + regfee + ", Security_Deposit = " + security + ", Monthly_Rent = " + monthlyrent + ", No_of_Months = " + no_of_monthrent + ", Day_Rent = " + dayrent + ", Late_Fee = " + latefee + ", Service_Tax = " + servicetax + ", Total = " + total + " WHERE Receipt_No = " + ReceiptNo + " " ;
            cc.cmd = new OleDbCommand(query, cc.con);
            cc.cmd.CommandText = query;
            cc.cmd.ExecuteNonQuery();
            cc.con.Close();
            MessageBox.Show("Data Updated Successfully");
            dgvReportFees.Refresh();
        }

Posted 6-May-16 3:25am

Member 11361964

Updated 6-May-16 3:58am

CHill60

v2

Add a Solution

Comments

F-ES Sitecore 6-May-16 9:32am

Use parameterised queries, it'll make your job easier (google ado.net parameterised queries) and also protect you against potential issues like sql injection.

Failing that look at the results of "query" via the debgging tools and look for an error. We don't know your database schema or your inputs so it's impossible to give much help. If any of your fields are date fields then that's always a potential problem (using ' rather than #, the format of the data and so on) but parameterised queries solves these issues for you.

Michael_Davies 6-May-16 10:33am

Use the debugger, dump the contents of the query string and take a close look.

If you are going to convert all of the grid view cells to local variables to string-them-up then you should convert all to string not to int. If fields liek chequeamount is a decimal field as it holds monetary values converting it to int will lose the fraction.

Any actual string fields you need to escape any special characters, for instance if the name entered in student name was O'connell that would cause a problem as it would close the text leaving connell' which is nonsense. As Griff says if you used parameterised queries this will all be taken care of.

Date fields are bound by # not ', as they are not string, in Access.

If you have used parameterised query please show that code.

2 solutions

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 · 2016-05-06T03:58:00

Why do you call Convert.ToInt32 on items that are already values?

You have:

C#

Convert.ToDateTime(dgvReportFees.SelectedRows[0].Cells[10].Value).ToString("MM-dd-yyyy");

Why convert a DateTime to a string, just so you can convert it back to a DateTime?
You then use string concatenation to convert all those integers back to strings in your command string. Which exposes you to SQL injection and the potential destruction of your database.
You post a message saying "Data Updated Successfully" without checking whether your command actually succeeded.

OriginalGriff · Answer 2 · 2016-05-06T03:51:00

Don't do that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

The chances are that your problem will disappear at the same time.
I'm not converting your actual query - that's your job - but it'll be along teh lines of:

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand com = new SqlCommand("UPDATE myTable SET myColumn1=@C1, myColumn2=@C2 WHERE Id=@ID", con))
        {
        com.Parameters.AddWithValue("@ID", id);
        com.Parameters.AddWithValue("@C1", myValueForColumn1);
        com.Parameters.AddWithValue("@C2", myValueForColumn2);
        com.ExecuteNonQuery();
        }
    }

Error during update using ms-access

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0