How to insert multiple rows into an access database?

Question

I'm trying to insert multiple rows into an Access database. The table is named sales with the columns sandwich and price. The data I'm trying to insert is contained in multiple TextBox controls.

So far I've tried this code:

Dim sql As String = "INSERT INTO [sales] ([sandwich]) VALUES ([@sandwich])"
Try
    Using conn As New OleDbConnection(constring)
        Using cmd As New OleDbCommand(sql, conn)
            conn.Open()
            For Each Order As TextBox In grpbill.Controls
                If Order.Text.Length > 0 Then
                    cmd.Parameters.AddWithValue("@sandwich", Order.Text)
                    cmd.ExecuteNonQuery()
                End If
            Next Order
        End Using
        conn.Close()
    End Using
Catch ex As Exception
    MsgBox(ex.Message)
End Try

It's working fine. The program is checking for full TextBox controls only and inserting the correct number of rows however when I check the database the values are all the same, inserting the value of the last TextBox over and over.

Any ideas how to fix this?

I doubt it is working right at all with more than one textBox in the grpbox - you cannot AddWithValue over and over - if there are 3 items, it will try to add 3 parameters with the same name which if course will fail. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Mar 20, 2017 at 16:36
First I would change grpbill.Controls to be grpbill.Controls.OfType(Of TextBox)(). That way you know you're only ever going to deal with a TextBox. And you should consider adding your parameter before the For and then updating the value in each loop. — Bugs
– Bugs, Commented Mar 20, 2017 at 16:37
@Plutonix as I stated at the end of my question the program is entering the same value over and over. I wouldn't be asking if nothing was wrong :S — Daniel Darko
– Daniel Darko, Commented Mar 20, 2017 at 16:39
If you are adding parameter(s) within a loop you should also use cmd.parameters.clear before adding them. This will ensure you dont run into any exceptions with parameters already added. — Dave B
– Dave B, Commented Mar 20, 2017 at 16:41

Bugs · Accepted Answer · 2017-03-21 10:44:40Z

You should add the parameter outside the For Each loop and the update the value on each loop. Do away with .AddWithValue and use .Add. .AddWithValue has to infer the database type for your query parameter.

You should also consider using grpbill.Controls.OfType(Of TextBox)() instead of grpbill.Controls as this will ensure you only loop through the TextBox control collection and not include a Label or GroupBox or a Panel etc.

Note that I have the following LINQ statement:

From t In grpbill.OfType(Of TextBox)()
Where t.TextLength > 0
Order By t.TabIndex

With this you can now remove your If statement. Also be sure to set the TabIndex for each TextBox so that they are added to the database in the right order.

I've also removed a Using to shorten the code a little. You can use a comma instead. This is just personal preference.

Lastly, you don't need conn.Close() as the Using will handle all that for you.

Code:

Dim sql As String = "INSERT INTO [sales] ([sandwich]) VALUES ([@sandwich])"
Try
    Using conn As New OleDbConnection(constring),
          cmd As New OleDbCommand(sql, conn)
        conn.Open()
        cmd.Parameters.Add("@sandwich", OleDbType.VarChar)
        For Each Order As TextBox In From t In grpbill.OfType(Of TextBox)()
                                     Where t.TextLength > 0
                                     Order By t.TabIndex
                cmd.Parameters("@sandwich").Value = Order.Text
                cmd.ExecuteNonQuery()
        Next 
    End Using
Catch ex As Exception
    MsgBox(ex.Message)
End Try

Community · Accepted Answer · 2017-05-23 12:17:23Z

I don't think you are declaring and setting values.

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim Name As String = txtName.Text
    Dim Email As String = tbxEmail.Text
    Dim Comment As String = tbxComment.Text

    Using dbConn = New OleDbConnection(".......")
        dbConn.Open()
        Dim strSql = "INSERT INTO [user] (username, email, comments) VALUES (?,?,?)"
        Using objcmd = New OleDbCommand(strSql, dbConn)
            objcmd.Parameters.AddWithValue("@username", Name)
            objcmd.Parameters.AddWithValue("@email", Email)
            objcmd.Parameters.AddWithValue("@comments", Comment)
            objcmd.ExecuteNonQuery()
        End Using
    End Using
    Response.Write("Submitted Successfully")
End Sub

Insert text from a text box into and Access 2010 DataBase using VB.Net

Collectives™ on Stack Overflow

How to insert multiple rows into an access database?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related