使用ms-access更新期间出错 [英] Error during update using ms-access

问题描述

想要使用datagrid中的值更新我的数据（ms-access）。错误显示 - UPDATE语句中出现语法错误。守则如下所示。请帮忙。



我尝试过：

  private   void  updateToolStripMenuItem_Click（ object  sender，EventArgs e）
 {
 cc.con.Open（）; 
  string  date = Convert.ToDateTime（dgvReportFees.SelectedRows [ 0 ]。单元格[ 0 ]。值）.ToString（  MM-dd-yyyy ）; 
  //   string date = Convert.ToString（dgvReportFees.SelectedRows [0] .Cells [0] .Value .ToString（））;  
  int  ReceiptNo = Convert.ToInt32（dgvReportFees.SelectedRows [ 0 ]。单元格[ 1 ]。值）; 
  string  studentname = dgvReportFees.SelectedRows [ 0 ]。单元格[ 2 ] Value.ToString（）; 
  int  regno = Convert.ToInt32（dgvReportFees.SelectedRows [ 0 ]。单元格[ 3 ]值）; 
  string  fathername = dgvReportFees.SelectedRows [ 0 ]。单元格[ 4 ]。Value.ToString（）; 
  string  mothername = dgvReportFees.SelectedRows [ 0 ]。单元格[ 5 ]。Value.ToString（）; 
  int  roomno = Convert.ToInt32（dgvReportFees.SelectedRows [ 0 ]。单元格[ 6 ]值）; 
  string  occupancy = dgvReportFees.SelectedRows [ 0 ]。单元格[ 7 ] Value.ToString（）; 
  string  paymenttype = dgvReportFees.SelectedRows [ 0 ]。单元格[ 8 ] Value.ToString（）; 
  string  chequeno = dgvReportFees.SelectedRows [ 0 ]。单元格[ 9 ] Value.ToString（）; 
  string  chequedate = Convert.ToDateTime（dgvReportFees.SelectedRows [ 0 ]。单元格[ 10 ]。值）.ToString（  MM-dd-yyyy ）; 
  string  bank = dgvReportFees.SelectedRows [ 0 ]。单元格[ 11 ] Value.ToString（）; 
  int  chequeamount = Convert.ToInt32（dgvReportFees.SelectedRows [ 0 ]。单元格[ 12 ]值）。 
  int  regfee = Convert.ToInt32（dgvReportFees.SelectedRows [ 0 ]。单元格[ 13 ]值）。 
  int  security = Convert.ToInt32（dgvReportFees.SelectedRows [ 0 ]。单元格[ 14 ]值）。 
  int  monthlyrent = Convert.ToInt32（dgvReportFees.SelectedRows [ 0 ]。单元格[ 15 ]值）。 
  int  no_of_monthrent = Convert.ToInt32（dgvReportFees.SelectedRows [ 0 ]。单元格[ 16 ]值）。 
  int  dayrent = Convert.ToInt32（dgvReportFees.SelectedRows [ 0 ]。单元格[ 17 ]值）。 
  int  latefee = Convert.ToInt32（dgvReportFees.SelectedRows [ 0 ]。单元格[ 18 ]值）。 
  int  servicetax = Convert.ToInt32（dgvReportFees.SelectedRows [ 0 ]。单元格[ 19 ]值）。 
  int  total = Convert.ToInt32（dgvReportFees.SelectedRows [ 0 ]。单元格[ 20 ]值）。 
 
 
  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 +  ，占用率=' +占用率+  '，Payment_Type =' + paymenttype +  '，Cheque_No =' + chequeno +  '，Cheque_Date =' + chequedate +  ' ，Bank_Name =' + bank +  '，Cheque_Amount = + chequeamount + < span class =code-string> ，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（ 数据已成功更新）; 
 dgvReportFees.Refresh（）; 
} 

解决方案

为什么要打电话给 Convert.ToInt32 on已经有价值的物品？



你有：

 Convert.ToDateTime（dgvReportFees.SelectedRows [< span class =code-digit> 0 ]。单元格[ 10 ]。值）.ToString（   MM-dd-yyyy）; 

为什么将DateTime转换为字符串，只是为了将其转换回a DateTime？

然后使用字符串连接将所有这些整数转换回命令字符串中的字符串。这会让你暴露于SQL注入和数据库的潜在破坏。

你发布一条消息说数据已成功更新而不检查你的命令是否真的成功。

不要那样做！不要连接字符串以构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。



您的问题可能会同时消失。

我没有转换您的实际查询 - 这是你的工作 - 但它将沿着以下几点：

 使用（SqlConnection con =  new  SqlConnection（strConnect））
 {
 con.Open（）; 
 使用（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（）; 
} 
} 
 

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:

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();
        }

解决方案

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

You have:

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.

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:

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();
        }
    }

这篇关于使用ms-access更新期间出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！