使用ms-access更新期间出错

本文介绍了使用ms-access更新期间出错的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 想要使用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 + ，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 [ 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更新期间出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！