none
Help how to save data more than one at the same time RRS feed

  • Question

  • Good day everyone,anyone can help me how to save data more than one at the same time.
    I have Tools ID,Tools Name and Tools Quantity in the Form that I created.

    Ex. I was inputed in the quantity 2 value then the Tools ID at Tools Name will save in the database
    2 rows at the same time with same data.

    I have this code like this but I have an error in Primary key which is duplication.

      Dim i As Integer
            For i = 1 To txtquantity.Text

                cmd = New OleDbCommand
                cmd.CommandText = " insert into tblsticker values (Sticker_id,Sticker_toolsid,Sticker_toolsname) "
    cmd.Parameters.AddWithValue("@Sticker_id", autogeneratesticker)
                cmd.Parameters.AddWithValue("@Sticker_toolsid", txttoolsid.Text)
                cmd.Parameters.AddWithValue("@Sticker_toolsname", txttoolsname.Text)
                cmd.Connection = cn
                cmd.ExecuteNonQuery()
                MessageBox.Show("Sticker Successfully Saved!")
            Next

    I was thinking and trying Auto Number in the MS access database and not include the Sticker ID which is the primary key
    but I have an error like this: Number of query values and destination field are not the same.

    Dim i As Integer
            For i = 1 To txtquantity.Text

                cmd = New OleDbCommand
                cmd.CommandText = " insert into tblsticker values (Sticker_toolsid,Sticker_toolsname) "
                cmd.Parameters.AddWithValue("@Sticker_toolsid", txttoolsid.Text)
                cmd.Parameters.AddWithValue("@Sticker_toolsname", txttoolsname.Text)
                cmd.Connection = cn
                cmd.ExecuteNonQuery()
                MessageBox.Show("Sticker Successfully Saved!")
            Next

    I hope anyone can help me of my problem or other way how to do it. I'm using VB 2010 with MS Access database.

    Thank you
    clynnekent
    Monday, March 30, 2020 1:29 AM

Answers

  • Hi,

    There are other solutions as well. You can try the one I provided below.

    Imports System.Data.OleDb
    Public Class Form1
        Dim constr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Database3.mdb; User Id=admin; Password="
        Dim conn As OleDbConnection
        Dim oda As OleDbDataAdapter
        Dim cmd As OleDbCommand
        Dim dt As New DataTable
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using conn = New OleDbConnection(constr)
                conn.Open()
                oda = New OleDbDataAdapter("Select * From Test", conn)
                oda.Fill(dt)
                DataGridView1.DataSource = dt
            End Using
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim i As Integer
            For i = 1 To CInt(TextBox3.Text)
                Using conn = New OleDbConnection(constr)
                    conn.Open()
    
                    cmd = New OleDbCommand
                    cmd.CommandText = "insert into Test(Sticker_toolsid,Sticker_toolsname) values (@Sticker_toolsid,@Sticker_toolsname)"
                    cmd.Parameters.AddWithValue("@Sticker_toolsid", TextBox1.Text)
                    cmd.Parameters.AddWithValue("@Sticker_toolsname", TextBox2.Text)
                    cmd.Connection = conn
                    cmd.ExecuteNonQuery()
    
                    dt = New DataTable
                    oda = New OleDbDataAdapter("Select * From Test", conn)
                    oda.Fill(dt)
                    DataGridView1.DataSource = dt
                End Using
            Next
        End Sub
    End Class
    
    
    

    Access document:

    Effect:

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Alex Li-MSFT Wednesday, April 1, 2020 1:41 AM
    • Marked as answer by clynnekent Wednesday, April 1, 2020 4:05 PM
    Tuesday, March 31, 2020 3:14 AM
    Moderator

All replies

  • Looks like the problem is with your insert statement.  You need to specify which columns you're inserting to, for example:

    INSERT INTO yourtablename
      (your, columns, listed, here) 
    VALUES 
      (your, values, listed, here)

    Monday, March 30, 2020 2:13 AM
  • Hi,

    There are other solutions as well. You can try the one I provided below.

    Imports System.Data.OleDb
    Public Class Form1
        Dim constr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Database3.mdb; User Id=admin; Password="
        Dim conn As OleDbConnection
        Dim oda As OleDbDataAdapter
        Dim cmd As OleDbCommand
        Dim dt As New DataTable
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using conn = New OleDbConnection(constr)
                conn.Open()
                oda = New OleDbDataAdapter("Select * From Test", conn)
                oda.Fill(dt)
                DataGridView1.DataSource = dt
            End Using
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim i As Integer
            For i = 1 To CInt(TextBox3.Text)
                Using conn = New OleDbConnection(constr)
                    conn.Open()
    
                    cmd = New OleDbCommand
                    cmd.CommandText = "insert into Test(Sticker_toolsid,Sticker_toolsname) values (@Sticker_toolsid,@Sticker_toolsname)"
                    cmd.Parameters.AddWithValue("@Sticker_toolsid", TextBox1.Text)
                    cmd.Parameters.AddWithValue("@Sticker_toolsname", TextBox2.Text)
                    cmd.Connection = conn
                    cmd.ExecuteNonQuery()
    
                    dt = New DataTable
                    oda = New OleDbDataAdapter("Select * From Test", conn)
                    oda.Fill(dt)
                    DataGridView1.DataSource = dt
                End Using
            Next
        End Sub
    End Class
    
    
    

    Access document:

    Effect:

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Alex Li-MSFT Wednesday, April 1, 2020 1:41 AM
    • Marked as answer by clynnekent Wednesday, April 1, 2020 4:05 PM
    Tuesday, March 31, 2020 3:14 AM
    Moderator