Click here to Skip to main content
15,442,578 members
Please Sign up or sign in to vote.
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
Updated 6-May-16 3:58am
v2
Comments
F-ES Sitecore 6-May-16 9:32am     CRLF
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     CRLF
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.

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.
 
Share this answer
 
Comments
[no name] 6-May-16 10:09am    
coz when the data is fetched from the grid the time part is also being fetched which is not required
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();
        }
    }
 
Share this answer
 
Comments
[no name] 6-May-16 10:08am    
tried that too.... but no effect
CHill60 6-May-16 10:56am    
Can you edit your question using the Improve question link and share the code as it now is

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900