添加和更新数据按钮 [英] Add and Update Data Button

问题描述

不知何故，它让我在运行时添加和更新，但它不会在MS Access中添加/更新。因此，当我关闭窗口并再次重新运行时，我输入的先前数据不再存在。知道为什么会这样吗？

Somehow it lets me add and update when I run but it doesn''t add/update in MS Access. So when I close the window and re-run again, the previous data that I input is not there anymore. Any idea why it happens?

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\invData.mdb;"
        cnn = New OleDbConnection(connectionString)
        Dim cmd As New OleDb.OleDbCommand
        If Not cnn.State = ConnectionState.Open Then
            'open connection if it is not yet open
            cnn.Open()
        End If
 
        cmd.Connection = cnn
        'check whether add new or update
        If Me.TextBox1.Tag & "" = "" Then
            'add new 
            'add data to table
            cmd.CommandText = "INSERT INTO NTIdb(ID, Status, DateDeliver, Client, POrder, StoreCode, StoreType, ProdUsage, ProdCategory, Brand, Description, Quantity, MainUnitSerial, HDD1, HDD2, OpticalDrive, Monitor, Keyboard, NetAdaptor, ProdKey, WTYEnt, AccExec, SalesOrder, SalesInv, DReceipt, ReceivedBy, WayBill, DueDate) " & _
                " VALUES(" & TextBox1.Text & ",'" & ComboBox1.SelectedItem() & "','" & _
                           DateTimePicker1.Value() & "','" & TextBox2.Text & "','" & _
                           TextBox3.Text & "', '" & TextBox4.Text & "', '" & TextBox5.Text & "','" & _
                           TextBox6.Text & "', '" & TextBox7.Text & "', '" & TextBox8.Text & "','" & _
                           TextBox9.Text & "', '" & NumericUpDown1.Value() & "', '" & TextBox10.Text & "','" & _
                           TextBox11.Text & "', '" & TextBox12.Text & "', '" & TextBox13.Text & "', '" & _
                           TextBox14.Text & "', '" & TextBox15.Text & "', '" & TextBox16.Text & "', '" & _
                           TextBox17.Text & "', '" & TextBox18.Text & "', '" & TextBox19.Text & "', '" & _
                           TextBox20.Text & "', '" & TextBox21.Text & "', '" & TextBox22.Text & "', '" & _
                           TextBox23.Text & "', '" & TextBox24.Text & "', '" & DateTimePicker2.Value() & "')"
 
            adptr.InsertCommand = New OleDbCommand(cmd.CommandText, cnn)
            cmd.ExecuteNonQuery()
        Else
            'update data in table
            cmd.CommandText = "UPDATE NTIdb " & _
                        " SET ID=" & TextBox1.Text & _
                        ", Status='" & ComboBox1.SelectedItem() & "'" & _
                        ", DateDeliver='" & DateTimePicker1.Value() & "'" & _
                        ", Client='" & TextBox2.Text & "'" & _
                        ", POrder='" & TextBox3.Text & "'" & _
                        ", StoreCode='" & TextBox4.Text & "'" & _
                        ", StoreType='" & TextBox5.Text & "'" & _
                        ", ProdUsage='" & TextBox6.Text & "'" & _
                        ", ProdCategory='" & TextBox7.Text & "'" & _
                        ", Brand='" & TextBox8.Text & "'" & _
                        ", Descrption='" & TextBox9.Text & "'" & _
                        ", Quantity='" & NumericUpDown1.Value() & "'" & _
                        ", MainUnitSerial='" & TextBox10.Text & "'" & _
                        ", HDD1='" & TextBox11.Text & "'" & _
                        ", HDD2='" & TextBox12.Text & "'" & _
                        ", OpticalDrive='" & TextBox13.Text & "'" & _
                        ", Monitor='" & TextBox14.Text & "'" & _
                        ", Keyboard='" & TextBox15.Text & "'" & _
                        ", NetAdaptor='" & TextBox16.Text & "'" & _
                        ", ProdKey='" & TextBox17.Text & "'" & _
                        ", WTYEnt='" & TextBox18.Text & "'" & _
                        ", AccExec='" & TextBox19.Text & "'" & _
                        ", SalesOrder='" & TextBox20.Text & "'" & _
                        ", SalesInv='" & TextBox21.Text & "'" & _
                        ", DReceipt='" & TextBox22.Text & "'" & _
                        ", ReceibedBy='" & TextBox23.Text & "'" & _
                        ", Waybill='" & TextBox24.Text & "'" & _
                        ", DueDate='" & DateTimePicker2.Value() & "'" & _
                        ", WHERE ID=" & TextBox1.Tag()
            cmd.ExecuteNonQuery()
        End If
        'refresh data in list
        RefreshData()
        'clear form
        Me.Button5.PerformClick()
 
        'close connection
        cnn.Close()
 
        '//To view Client
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\invData.mdb;"
 
        Dim ds1 As New DataSet
        Dim sql1 As String = "Select Client from NTIdb"
        cnn = New OleDbConnection(connectionString)
 
        Try
            cnn.Open()
            adptr = New OleDbDataAdapter(sql1, cnn)
            adptr.Fill(ds1)
 
            For a = 0 To ds1.Tables(0).Rows.Count - 1
                ComboBox2.Items.Add(ds1.Tables(0).Rows(a).Item(0))
            Next
 
            adptr.Dispose()
            cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
 
        'get data into list
        Me.RefreshData()
 
        If TextBox2.Text = sql1 Then
            TextBox2.Visible = False
            ComboBox2.Visible = True
        End If

推荐答案

每当你正在做任何数据库连接的代码，你应该使用try和catch块，以便你可以捕获错误。

根据你的代码是找到一件事就是请调试代码并采取插入和更新的查询并尝试在MSAccess中执行



还有一段时间由于.mdb文件打开执行此操作时，它无法更新数据，因此关闭.mdb。

这篇关于添加和更新数据按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！