none
执行adapter.update时,发生INSERT INTO语法错误 RRS feed

  • 问题

  • 初学ADO.NET,正在进行数据库操作的演练。以下代码在更新数据源(执行button5_Cilck)时提示:INSERT INTO 语法错误 很多东西我都不懂,可能别的地方也有错误,请高手一并指正,谢谢
    测试表的结构

    表名:客户
    字段1:序号  长整数型
    字段2:姓名 字符型 长度12
    字段3:单位 字符型 长度30

        Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter
        Dim myTable As DataTable = New DataTable
        
        Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
            Dim cn As OleDbConnection = New OleDbConnection( _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Jet OLEDB:Engine Type=6;Data Source=myNew1.accdb ")
    
            Dim strSelect As String = "select * from 客户"
            Dim sqlUpdata As String = _
            "UPDATA 客户" & _
            "set 序号=@序号" & _
            "姓名=@姓名" & _
            "单位=@单位"
            Dim sqlInsert As String = "INSERT INTO 客户 (序号,姓名,单位) value (@序号,@姓名,@单位)"
            Dim sqlDelete As String = "DELETE FROM 客户 WHERE 序号=@序号"
            myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    
            ' 设置适配器 SelectCommand 属性
            myAdapter.SelectCommand = New OleDbCommand(strSelect, cn)
    
            '设定数据适配器的 UpdateCommand 属性
            myAdapter.UpdateCommand = New OleDbCommand(sqlUpdata, cn)
            ' 设定 UPDATE 表达式的各个参数
            myAdapter.UpdateCommand.Parameters.Add("@序号", OleDbType.VarChar, 12, "序号")
            myAdapter.UpdateCommand.Parameters.Add("@姓名", OleDbType.VarChar, 12, "姓名")
            myAdapter.UpdateCommand.Parameters.Add("@单位", OleDbType.VarChar, 20, "单位")
    
            '设定数据适配器的 InsertCommand 属性
            myAdapter.InsertCommand = New OleDbCommand(sqlInsert, cn)
            ' 设定 INSERT 表达式的各个参数
            myAdapter.InsertCommand.Parameters.Add("@序号", OleDbType.VarChar, 12, "序号")
            myAdapter.InsertCommand.Parameters.Add("@姓名", OleDbType.VarChar, 12, "姓名")
            myAdapter.InsertCommand.Parameters.Add("@单位", OleDbType.VarChar, 20, "单位")
    
            '设定数据适配器的 DeleteCommand 属性
            myAdapter.DeleteCommand = New OleDbCommand(sqlDelete, cn)
            ' 设定 DELETE 表达式的各个参数
            Dim delParameter As OleDbParameter = myAdapter.DeleteCommand.Parameters.Add("@序号", OleDbType.VarChar, 12, "序号")
            delParameter.SourceVersion = DataRowVersion.Original
    
            Try
                cn.Open()
                myAdapter.Fill(myTable)
                cn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "注意")
            End Try
    
            '增加一些新数据
            Dim newRow As DataRow
            For i As Integer = 1 To 10
                newRow = myTable.NewRow
                newRow(0) = 30 + i
                newRow(1) = "李明"
                newRow(2) = "山东电力"
                myTable.Rows.Add(newRow)
            Next
    
            myDataGrid.CaptionText = myTable.TableName '显示表名
            myDataGrid.SetDataBinding(myTable, "") '显示数据
    
        End Sub
        Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
            If myTable.HasErrors Then
                MessageBox.Show("数据表有错误,不能更新", "请注意", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
                Try
                    Dim number As Integer = myAdapter.Update(myTable)
                    myTable.AcceptChanges()
                    MessageBox.Show("更新笔数:" & number, "提示", MessageBoxButtons.OK)
                Catch ex As Exception
                    MessageBox.Show(ex.ToString, "注意")
                 End Try
            End If
        End Sub
    
    

     

    • 已编辑 老伙计 2010年4月3日 17:02 上次编辑后空行太多
    2010年4月3日 16:17

答案