在VB.Net 2008中更新/编辑记录以访问数据库的问题 [英] Problem Updating/Editing Records to Access Database in VB.Net 2008

问题描述

我在将VB.Net应用程序中的记录更新到数据库（Access数据库）时遇到问题。我已经尝试了各种更新技术，并且不断获得更新语句中的语法错误或条件表达式中的数据不匹配。



继承代码我正在使用：

 公共 函数 save1stsemester（）
 ' 此代码将第一学期的记录更新到数据库 
  Dim  ds 作为 新 DataSet 
  Dim  dbSource  As  字符串 
  Dim  conn 作为 新 OleDb.OleDbConnection 
  Dim  dbcursor 作为 整数 =  0  
  Dim  da 作为 新 OleDb.OleDbDataAdapter 
  Dim  cmdUpdate  As  新 OleDb.OleDbCommand 
  Dim  msg1 作为 字符串 
  Dim 样式 As  MsgBoxStyle 
  Dim  result  As  MsgBoxResult 
  Dim 标题，备注 As   String  
  Dim  totalgrade  As   Integer  =  CInt （txtTotalMK.Text）
  Dim  totalload  As  整数 =  CInt （txtTotalLoad.Text）
  Dim  gpadecimal 作为 十进制 
 gpadecimal =  CDec (lblGPA1.Text）
 title =  成功 
样式= MsgBoxStyle.Information 
 调用 allremarks（）' 此函数将所有注释收集到单个变量中。 
 remarks = allremarks（）
  Dim  failflag  As   Boolean  
 如果 checkflag100（）=  True  然后 '  True表示所有课程均已通过，然后开启标志 
 failflag =  True  
 否则 
 failflag = 错误 
 结束 如果 
 如果 conn.State = ConnectionState.Open 那么 
  GoTo  cont 
 否则 
 如果 conn.State = ConnectionState.Closed 那么 
 dbProvider =    PROVIDER = Microsoft.Jet.OLEDB.4.0; 
 dbSource =  数据源= C：\ CGPA \ e-CGPA Stat\e-CGPA Stat\bin\Debug\Scoredata.mdb; Persist Security Info = False 
 conn.ConnectionString = dbProvider& ; dbSource 
 conn.Open（）
 da.SelectCommand = 新 OleDb.OleDbCommand（  SELECT * FROM 100Level1stSemester WHERE MatNO喜欢'％' + cmbMATNO.Text +  ％'）
 da.SelectCommand.Connection = conn 
  Dim  mycomand  As  新 OleDb.OleDbCommandBuilder（da）
 da.Fill（ds，  100Level1stSemester）
 cont：如果 cmbCourseLevel.SelectedItem =   100级 然后 
 < span class =code-comment>' 尝试 
 cmdUpdate.CommandText =   UPDATE 100Level1stSemester& _ 
   SET MatNo ='& cmbMATNO.Text&  '& _ 
  ，FName ='& cmbStatFName.Text&  '& _ 
  ，MName ='& cmbStatInitial.Text&  '& _ 
  ，SName ='& cmbStatSname.Text&  '& _ 
  ，STA110 ='& txtGR1.Text&  '& _ 
  ，MTH110 ='& txtGR2.Text&  '& _ 
  ，MTH112 ='& txtGR3.Text&  '& _ 
  ，ACC111 ='& txtGR4.Text&  '& _ 
  ，GST111 ='& txtGR5.Text&  '& _ 
  ，GST112 ='& txtGR6.Text&  '& _ 
  ，GST123 ='& txtGR7.Text&  '& _ 
  ，[STA110-SCORE] ='& txtSC1.Text&  '& _ 
  ，[MTH110-SCORE] ='& txtSC2.Text&  '& _ 
  ，[MTH112-SCORE] ='& txtSC3.Text&  '& _ 
  ，[ACC111-SCORE] ='& txtSC4.Text&  '& _ 
  ，[GST111-SCORE] ='& txtSC5.Text&  '& _ 
  ，[GST112-SCORE] ='& txtSC6.Text&  '& _ 
  ，[GST123-SCORE] ='& txtSC7.Text&  '& _ 
  ，[Tot-Grade-Point] ='&总和&  '& _ 
  ，[Tot-Credit-Load] ='&总负荷 '& _ 
  ，[1stSemesterGPA] ='& gpadecimal&  '& _ 
  ，备注='&言论&  '& _ 
  ，Flag ='&失败标志&  '& _ 
   WHERE MatNo ='& cmbMATNO.Text&  ' 
 cmdUpdate.CommandType = Data.CommandType.Text 
 cmdUpdate。 Connection = conn 
 cmdUpdate.ExecuteNonQuery（）
 cmdUpdate.Dispose（）
 conn.Close（）
 msg1 =   100级第一学期考试成绩和成绩更新成功。 
 result = MsgBox（msg1，style，title）
 cmdVerifySem1.Enabled = < span class =code-keyword> False  
 Grp1stSEM.Enabled =  True  
 '  Catch  
 MessageBox.Show（ 在更新学生的100级分数时发生错误！在数据库中检测到重复的条目。确保之前没有输入学生的分数，然后再试一次。）
 ' <跨度class =code-comment>结束尝试 
 结束 如果 
 结束 如果 
 结束 如果 
 结束 功能 

我正在使用Visual Studio.Net 2008（VB.Net）和Access 2003.



我评论了try和catch语句，以便跟踪错误发生的位置。

代码中的错误标志：（cmdUpdate.ExecuteNonQuery）



请帮忙。



谢谢

解决方案

Are表中的所有列都是字符串吗？我猜你的得分栏，成绩点等实际上是数字，所以他们不需要围绕他们的单引号。



另外，请注意，您应该考虑使用parms而不是手动输入这些单引号。它将帮助您解决此类数据类型问题并防止SQL注入。您可以开始研究此处 [ ^ ]。



希望这有帮助。

I''m having a problem updating records from my VB.Net application to the database (Access Database). I''ve tried all sorts of update technique and i keep getting either "syntax error in update statement" or "data mismatch in criteria expression".

heres the code i''m using:

Public Function save1stsemester()
    'this code updates 1st semester records to database
    Dim ds As New DataSet
    Dim dbSource As String
    Dim conn As New OleDb.OleDbConnection
    Dim dbcursor As Integer = 0
    Dim da As New OleDb.OleDbDataAdapter
    Dim cmdUpdate As New OleDb.OleDbCommand
    Dim msg1 As String
    Dim style As MsgBoxStyle
    Dim result As MsgBoxResult
    Dim title, remarks As String
    Dim totalgrade As Integer = CInt(txtTotalMK.Text)
    Dim totalload As Integer = CInt(txtTotalLoad.Text)
    Dim gpadecimal As Decimal
    gpadecimal = CDec(lblGPA1.Text)
    title = "Success"
    style = MsgBoxStyle.Information
    Call allremarks() ' this function gathers all the remarks to a single variable.
    remarks = allremarks()
    Dim failflag As Boolean
    If checkflag100() = True Then 'True means all courses were passed then turn on Flag
    failflag = True
    Else
    failflag = False
    End If
    If conn.State = ConnectionState.Open Then
    GoTo cont
    Else
    If conn.State = ConnectionState.Closed Then
    dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    dbSource = "Data Source =C:\CGPA\e-CGPA Stat\e-CGPA Stat\bin\Debug\Scoredata.mdb;Persist Security Info=False"
    conn.ConnectionString = dbProvider & dbSource
    conn.Open()
    da.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM 100Level1stSemester WHERE MatNO like '%" + cmbMATNO.Text + "%'")
    da.SelectCommand.Connection = conn
    Dim mycomand As New OleDb.OleDbCommandBuilder(da)
    da.Fill(ds, "100Level1stSemester")
    cont: If cmbCourseLevel.SelectedItem = "100 Level" Then
    ' Try
    cmdUpdate.CommandText = "UPDATE 100Level1stSemester " & _
    "SET MatNo = '" & cmbMATNO.Text & "'" & _
    ", FName = '" & cmbStatFName.Text & "'" & _
    ", MName = '" & cmbStatInitial.Text & "'" & _
    ", SName = '" & cmbStatSname.Text & "'" & _
    ", STA110 = '" & txtGR1.Text & "'" & _
    ", MTH110 = '" & txtGR2.Text & "'" & _
    ", MTH112 = '" & txtGR3.Text & "'" & _
    ", ACC111 = '" & txtGR4.Text & "'" & _
    ", GST111 = '" & txtGR5.Text & "'" & _
    ", GST112 = '" & txtGR6.Text & "'" & _
    ", GST123 = '" & txtGR7.Text & "'" & _
    ", [STA110-SCORE] = '" & txtSC1.Text & "'" & _
    ", [MTH110-SCORE] = '" & txtSC2.Text & "'" & _
    ", [MTH112-SCORE] = '" & txtSC3.Text & "'" & _
    ", [ACC111-SCORE] = '" & txtSC4.Text & "'" & _
    ", [GST111-SCORE] = '" & txtSC5.Text & "'" & _
    ", [GST112-SCORE] = '" & txtSC6.Text & "'" & _
    ", [GST123-SCORE] = '" & txtSC7.Text & "'" & _
    ", [Tot-Grade-Point] = '" & totalgrade & "'" & _
    ", [Tot-Credit-Load] = '" & totalload & "'" & _
    ", [1stSemesterGPA] = '" & gpadecimal & "'" & _
    ", Remarks = '" & remarks & "'" & _
    ", Flag = '" & failflag & "'" & _
    " WHERE MatNo = '" & cmbMATNO.Text & "'"
    cmdUpdate.CommandType = Data.CommandType.Text
    cmdUpdate.Connection = conn
    cmdUpdate.ExecuteNonQuery()
    cmdUpdate.Dispose()
    conn.Close()
    msg1 = "100 Level 1st semester exam score and grades updated successfully."
    result = MsgBox(msg1, style, title)
    cmdVerifySem1.Enabled = False
    Grp1stSEM.Enabled = True
    'Catch
    MessageBox.Show("An error occured while updating the student's 100 Level scores! Duplicate entry was detected in the database. Ensure that the student's scores has not been entered before and try again.")
    'End Try
    End If
    End If
    End If
    End Function

I''m using Visual Studio.Net 2008 (VB.Net) and Access 2003.

I commented the try and catch statement to enable me trace where the error is comming from.
The error flags at the code: (cmdUpdate.ExecuteNonQuery)

Please help.

Thanks

解决方案

Are all of the columns in your table really strings? I''m guessing your score columns, grade point, etc are actually numbers, so they don''t need the single quotes around them.

Also, a side note, you should look into using parms instead of manually putting in those single quotes. It will help you with data type issues like this and guards against SQL injection. You can start researching that here[^].

Hope this helps.

这篇关于在VB.Net 2008中更新/编辑记录以访问数据库的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！