locked
Insert data to access, please.. RRS feed

  • Question

  •     Private Sub btnRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRegister.Click
            Dim cmd As New OleDb.OleDbCommand
            cmd.CommandText = "insert into recruiterdata(recruiteremail,rcfirstname,rclastname,rcpassword,rcphone,rccompany)" & _
                "values(" & Me.txtEmail.Text & ",'" & Me.txtFirstName.Text & "','" & Me.txtLastName.Text & "','" & _
                Me.txtPassword.Text & "','" & Me.txtPhone.Text & "','" & Me.txtCompany.Text & "')"
    
        End Sub

    After I click Register then I check database that add two row at the same time

    first row is added "1" to each field

    second row is added txtEmail.[text]     txtFirst.[text]     ........


    Anybody know how to fix it....?
    • Edited by colton_chan Saturday, March 2, 2013 10:36 PM
    Saturday, March 2, 2013 8:30 PM

Answers

  • This should help, create a form as per the image below (see control names in code). Next create an MS-Access 2007 database or higher named Database1.accdb (see in image below table name and fields for the code to work), place it in the same folder where your project compiles to i.e. Bin\Debug folder. Add the code below, compile, run, add a new row.

    Note when I add a new row the primary key is obtained and inserted with the first and last name data. You can toggle the primary key to display or not with a checkbox on the form.

    Imports System.Data.OleDb
    Public Class Form1
        WithEvents bsData As New BindingSource
        Private ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb"
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = ConnectionString
                }
                Using cmd As New OleDbCommand With
                    {
                        .CommandText = "SELECT Identifier, FirstName, LastName FROM Customers",
                        .Connection = cn
                    }
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                End Using
            End Using
            bsData.DataSource = dt
            DataGridView1.DataSource = bsData
            DataGridView1.Columns("FirstName").HeaderText = "First"
            DataGridView1.Columns("LastName").HeaderText = "Last"
        End Sub
        Private Sub cmdAddNewRow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddNewRow.Click
            Dim NewIdentifer As Integer = 0
            If Not String.IsNullOrEmpty(txtLastName.Text) AndAlso Not String.IsNullOrWhiteSpace(txtFirstName.Text) Then
                Using cn As New OleDbConnection(ConnectionString)
                    Using cmd As New OleDbCommand("INSERT INTO Customers (FirstName,LastName) Values(@FirstName,@LastName)", cn)
                        cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
                        cmd.Parameters.AddWithValue("@LastName", txtLastName.Text)
                        cn.Open()
                        cmd.ExecuteNonQuery()
                        ' Get new row primary key
                        cmd.CommandText = "Select @@Identity"
                        NewIdentifer = CInt(cmd.ExecuteScalar())
                        ' Add new row to DataTable
                        CType(bsData.DataSource, DataTable).Rows.Add(New Object() {NewIdentifer, txtFirstName.Text, txtLastName.Text})
                        ' Move to new row
                        bsData.Position = bsData.Find("Identifier", NewIdentifer)
                        ' Rest TextBox controls
                        txtFirstName.Text = ""
                        txtLastName.Text = ""
                    End Using
                End Using
            Else
                MsgBox("Please enter first and last name")
            End If
        End Sub
        Private Sub cnkShowIdentifier_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkShowIdentifier.CheckedChanged
            If DataGridView1.DataSource IsNot Nothing Then
                DataGridView1.Columns("Identifier").Visible = chkShowIdentifier.Checked
            End If
        End Sub
    End Class

    Screenshot and table schema

    Hope this helps.


    kevininstructor


    • Edited by KareninstructorMVP Sunday, March 3, 2013 2:14 AM Tweaked some text.
    • Marked as answer by Youen Zen Thursday, March 14, 2013 10:09 AM
    Sunday, March 3, 2013 2:13 AM

All replies

  • Hello,

    All you are doing in the code presented is creating a OleDbDbCommand and setting the CommandText but not showing how you are executing the command. I would have expected to see a call like cmd.ExecuteNonQuery().  When invoking ExecuteNonQuery always check the result which indicates how many rows were affected. For an insert statement it will always return 1 when the insert worked. With that said there is code causing your issue that is not shown in your post.


    kevininstructor

    Sunday, March 3, 2013 12:56 AM
  • do you have some useful website or let me know more how to do it?

    Sunday, March 3, 2013 1:24 AM
  • This should help, create a form as per the image below (see control names in code). Next create an MS-Access 2007 database or higher named Database1.accdb (see in image below table name and fields for the code to work), place it in the same folder where your project compiles to i.e. Bin\Debug folder. Add the code below, compile, run, add a new row.

    Note when I add a new row the primary key is obtained and inserted with the first and last name data. You can toggle the primary key to display or not with a checkbox on the form.

    Imports System.Data.OleDb
    Public Class Form1
        WithEvents bsData As New BindingSource
        Private ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb"
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = ConnectionString
                }
                Using cmd As New OleDbCommand With
                    {
                        .CommandText = "SELECT Identifier, FirstName, LastName FROM Customers",
                        .Connection = cn
                    }
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                End Using
            End Using
            bsData.DataSource = dt
            DataGridView1.DataSource = bsData
            DataGridView1.Columns("FirstName").HeaderText = "First"
            DataGridView1.Columns("LastName").HeaderText = "Last"
        End Sub
        Private Sub cmdAddNewRow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddNewRow.Click
            Dim NewIdentifer As Integer = 0
            If Not String.IsNullOrEmpty(txtLastName.Text) AndAlso Not String.IsNullOrWhiteSpace(txtFirstName.Text) Then
                Using cn As New OleDbConnection(ConnectionString)
                    Using cmd As New OleDbCommand("INSERT INTO Customers (FirstName,LastName) Values(@FirstName,@LastName)", cn)
                        cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
                        cmd.Parameters.AddWithValue("@LastName", txtLastName.Text)
                        cn.Open()
                        cmd.ExecuteNonQuery()
                        ' Get new row primary key
                        cmd.CommandText = "Select @@Identity"
                        NewIdentifer = CInt(cmd.ExecuteScalar())
                        ' Add new row to DataTable
                        CType(bsData.DataSource, DataTable).Rows.Add(New Object() {NewIdentifer, txtFirstName.Text, txtLastName.Text})
                        ' Move to new row
                        bsData.Position = bsData.Find("Identifier", NewIdentifer)
                        ' Rest TextBox controls
                        txtFirstName.Text = ""
                        txtLastName.Text = ""
                    End Using
                End Using
            Else
                MsgBox("Please enter first and last name")
            End If
        End Sub
        Private Sub cnkShowIdentifier_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkShowIdentifier.CheckedChanged
            If DataGridView1.DataSource IsNot Nothing Then
                DataGridView1.Columns("Identifier").Visible = chkShowIdentifier.Checked
            End If
        End Sub
    End Class

    Screenshot and table schema

    Hope this helps.


    kevininstructor


    • Edited by KareninstructorMVP Sunday, March 3, 2013 2:14 AM Tweaked some text.
    • Marked as answer by Youen Zen Thursday, March 14, 2013 10:09 AM
    Sunday, March 3, 2013 2:13 AM

  • Sunday, March 3, 2013 5:47 AM
  • In plain English, code is correct, your database table is saying this field must be populated. You have two options, remove the constraint on this field in the database or provide a value for this field in your code.

    Article on validation rules via MS-Access.


    kevininstructor

    Sunday, March 3, 2013 8:10 AM
  • In plain English, code is correct, your database table is saying this field must be populated. You have two options, remove the constraint on this field in the database or provide a value for this field in your code.

    Article on validation rules via MS-Access.


    kevininstructor



    Monday, March 4, 2013 2:36 AM
  • See my reply date/time stamped Sunday, March 03, 2013 2:13 AM

    kevininstructor

    Monday, March 4, 2013 3:04 AM
  • See my reply date/time stamped Sunday, March 03, 2013 2:13 AM

    kevininstructor

    What is bsdata and connectionstring?

    \

    Monday, March 4, 2013 3:55 AM
  • The connectionstring is shown in Kevin's example as below:

        Private ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb"
    

    You could get more examples about connection strings here: http://www.connectionstrings.com/.

    Best regards,


    Shanks Zen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 5, 2013 5:14 AM
  • This is from the reply I indicated you need to review in regards to bsData

    WithEvents bsData As New BindingSource
    Shanks Zen pointed you to the ConnectionString.

    Enjoy life

    Tuesday, March 5, 2013 9:49 AM
  • I have been made connection to access...in the other from(Login form)...I test it and success to login
    Tuesday, March 5, 2013 5:12 PM