Access таблица ошибки вставки

• Remove From My Forums

• Question

• HI,

  I was uploading the excel data into specific table through import option. Actually im getting the below error message;

  Microsoft Access was unable to append all the data to the table.

  The contents of fields in <number> record(s) were deleted, and 0 record(s) were lost due to key violations.

      If data was deleted, the data you pasted or imported doesn’t match the field data types or the FieldSize property in the destination table.
      If records were lost either the records you pasted contain primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables.
      Do you want to continue anyway?

  I know the cause of this issue. that is because there is a new value in the data i uploaded but its not available in the linked table.

  For example: I have A and B tables, both these A and B are linked to each other. Now im uploading data into table A but that value is not available because its a new value.

  So my question is if the data is not imported why it is not pasting the error data in to paste errors table.

  Guys anybody have idea about this.

  ---

  Vinoth S

Как я могу вставить данные в таблицу MS Access? Когда я пытаюсь, я получаю ошибку.

Код:

If TextBox1.Text = Nothing And TextBox2.Text = Nothing Then
    MsgBox("No Username and Password inserted")
    TextBox1.Focus()

Else

    If Not con.State = ConnectionState.Open Then
        'open connection if it is not yet open
    End If

    cmd.Connection = con
    'add data to table
    cmd.CommandText = "insert into loginTable(username, password, typeofuser) values ('" & Me.TextBox1.Text & "', '" & Me.TextBox2.Text & "', '" & Me.ComboBox1.Text & "')"

    cmd.ExecuteNonQuery()

    'refresh data in list

    'close connection
    con.Close()
End If

Во-первых, вы не открываете соединение:

con.Open()

Далее password является зарезервированное слово в MS Access. Вам нужно заключить password в квадратные скобки:

[password]

Вы объединяете строки вместо использования параметров:

cmd.Parameters.Add("@username", OleDbType.VarChar).Value = txtUsername.Text
cmd.Parameters.Add("@password", OleDbType.VarChar).Value = txtPassword.Text
cmd.Parameters.Add("@typeofuser", OleDbType.VarChar).Value = cmbTypeOfUser.Text

Обратите внимание на правильное имя для TextBox и ComboBox элементов управления вместо использования TextBox1, TextBox2 и ComboBox1. Это помогает правильно идентифицировать каждый элемент управления:

txtUsername
txtPassword
cmbTypeOfUser

Отойдите от использования MsgBox и используйте MessageBox.Show. MsgBox существует для VB6 и в любом случае заканчивается делегированием MessageBox, поэтому имеет смысл использовать MessageBox.Show:

MessageBox.Show("No Username and Password inserted")

Наконец, я хотел бы рассмотреть возможность реализации использования, которое поможет закрыть и избавьтесь от ваших объектов SQL:

Using cmd As New OleDbCommand(command, connection)

End Using

В целом ваш код будет выглядеть примерно так:

If txtUsername.Text = Nothing And txtPassword.Text = Nothing Then

    MessageBox.Show("No Username and Password inserted")
    TextBox1.Focus()

Else

    Using con As New OleDbConnection(connectionString),
          cmd As New OleDbCommand("INSERT INTO [loginTable] ([username], [password], [typeofuser]) VALUES (@username, @password, @typeofuser)", con)

        con.Open()

        cmd.Parameters.Add("@username", OleDbType.VarChar).Value = txtUsername.Text
        cmd.Parameters.Add("@password", OleDbType.VarChar).Value = txtPassword.Text
        cmd.Parameters.Add("@typeofuser", OleDbType.VarChar).Value = cmbTypeOfUser.Text

        cmd.ExecuteNonQuery()

    End Using

End If

^{Это выходит за рамки этого вопроса, но я бы также посмотрел на шифрование паролей. Хранить их в виде простого текста — плохая практика. Посмотрите на вопрос SO; Лучший способ хранения пароля в базе данных, который может дать вам некоторые идеи о том, как лучше всего это сделать.}

Есть (по крайней мере) несколько способов сделать это. Итак, попробуйте это. , ,

Imports System.Data.OleDb

Public Class Form1

    Private ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersExcelDesktopCodingMicrosoft AccessNorthwind.mdb;"
    Private NewIdentifer As Integer = 0
    Private InsertStatement As String = "INSERT INTO Employee (LName) Values(@LName)"
    Private IdentifierStatement As String = "Select @@Identity"

    'Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Using cn As New OleDbConnection(ConnectionString)
            Using cmd As New OleDbCommand("SELECT * FROM Employee", cn)
                Dim dt As New DataTable
                cn.Open()
                Dim Reader As OleDbDataReader = cmd.ExecuteReader()
                dt.Load(Reader)
                Dim dv = dt.DefaultView
                DataGridView1.DataSource = dv
            End Using
        End Using
    End Sub

    'End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        If Not String.IsNullOrEmpty(txtLastName.Text) Then
            Using cn As New OleDbConnection(ConnectionString)
                Using cmd As New OleDbCommand(InsertStatement, cn)
                    cmd.Parameters.AddWithValue("@LName", txtLastName.Text)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = IdentifierStatement
                    NewIdentifer = CInt(cmd.ExecuteScalar())
                    Dim Row As DataRowView = CType(DataGridView1.DataSource, DataView).AddNew
                    Row("Fname") = NewIdentifer
                    Row("LName") = txtLastName.Text
                    Row.EndEdit()
                    DataGridView1.CurrentCell = DataGridView1(0, DataGridView1.RowCount - 1)
                    txtLastName.Text = ""
                End Using
            End Using
        Else
            MsgBox("Please enter a name")
        End If

    End Sub
End Class

Также попробуйте. . .

Imports System.Data.OleDb

Public Class Form1



    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        ' Requires: Imports System.Data.OleDb

        ' ensures the connection is closed and disposed
        Using connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=""C:UsersRyanDesktopCodingDOT.NETSamples VBInsert Into MS Access Table from TextboxWindowsApplication1binInsertInto.mdb"";" & _
            "Persist Security Info=False")
            ' open connection
            connection.Open()

            ' Create command
            Dim insertCommand As New OleDbCommand( _
                "INSERT INTO Table1([inputOne] , [inputTwo] , [inputThree]) " & _
                "VALUES (@inputOne, @inputTwo, @inputThree);", _
                connection)
            ' Add the parameters with value
            insertCommand.Parameters.AddWithValue("@inputOne", TextBox1.Text)
            insertCommand.Parameters.AddWithValue("@inputTwo", TextBox2.Text)
            insertCommand.Parameters.AddWithValue("@inputThree", TextBox3.Text)
            ' you should always use parameterized queries to avoid SQL Injection
            ' execute the command
            insertCommand.ExecuteNonQuery()

            MessageBox.Show("Insert is done!!")

        End Using

    End Sub
End Class