Insert more than one rows of data to Access

[reds8]

How to insert more than one rows of data to Access??

All my textboxes are created during runtime and I want to add the text in textboxes to database.
some times i want 3 rows and some times maybe 10 rows
This is the code to create textboxes.

For Me.count = count To number

            Dim label1 = New Label
            With label1
                .Name = "lblNo" & count
                .Size = New Size(32, 26)
                .Location = New Point(x, y)
                ' .Font = txtNum1.Font
                .TextAlign = HorizontalAlignment.Center
                .Text = count & "."
                .TabStop = False
                Controls.Add(label1)
            End With

            x += 53

            Dim myTextBox2 = New TextBox
            With myTextBox2
                .Name = "txtNum" & count
                .Size = New Size(50, 26)
                .Location = New Point(x, y)
                '.Font = txtNum1.Font
                .TextAlign = HorizontalAlignment.Center
                .MaxLength = 4
                Controls.Add(myTextBox2)
            End With

            x += 75

            Dim myTextBox3 = New TextBox
            With myTextBox3
                .Name = "txtBig" & count
                .Size = New Size(50, 26)
                .Location = New Point(x, y)
                '  .Font = txtNum1.Font
                .TextAlign = HorizontalAlignment.Center
                .MaxLength = 6
                Controls.Add(myTextBox3)
            End With

            x += 70

            Dim myTextBox4 = New TextBox
            With myTextBox4
                .Name = "txtSmall" & count
                .Size = New Size(50, 26)
                .Location = New Point(x, y)
                '.Font = txtNum1.Font
                .TextAlign = HorizontalAlignment.Center
                .MaxLength = 6
                Controls.Add(myTextBox4)
            End With
            x = 12
            y += 25
        Next

        If count > 11 Then
            MessageBox.Show("Maximum rows are 10", "Attention", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End If

[adam_k]

Why can't you loop through them and insert into your db?

[reds8]

Why can't you loop through them and insert into your db?

how to loop throught them??
write a loop for the insert or write the insert code inside the for loop which create text box??

[adam_k]

This is a way to do it. It might not be the best one, but it works and I think it's pretty straight forward.

Dim insert As String
        Dim values As String = ""
        Dim con As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand

        con.ConnectionString = "your connection string here"
        cmd.Connection = con
        cmd.Connection.Open()

        insert = "insert into test_table (field1, field2, field3) " & vbCrLf _
        & " values " & vbCrLf

        Dim txtnum As String = ""
        Dim txtbig As String = ""
        Dim txtsmall As String = ""

        For i = 0 To number
            For Each ctrl In Me.Controls
                If TypeOf ctrl Is TextBox Then
                    If ctrl.Name = "txtNum" & i Then
                        txtnum = ctrl.Text
                    ElseIf ctrl.Name = "txtBig" & i Then
                        txtbig = ctrl.text
                    ElseIf ctrl.Name = "txtSmall" & i Then
                        txtsmall = ctrl.text
                    End If
                End If
            Next

            values = "('" & txtnum & "','" & txtbig _
                & "','" & txtsmall & "')"

            cmd.CommandText = insert & values
            cmd.ExecuteNonQuery()
        Next

Let me know how this goes.

PS: You might want to add some checks that ctrl.text is not empty, but as this wasn't part of the requirements I've left it out.

[reds8]

This is a way to do it. It might not be the best one, but it works and I think it's pretty straight forward.

Dim insert As String
        Dim values As String = ""
        Dim con As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand

        con.ConnectionString = "your connection string here"
        cmd.Connection = con
        cmd.Connection.Open()

        insert = "insert into test_table (field1, field2, field3) " & vbCrLf _
        & " values " & vbCrLf

        Dim txtnum As String = ""
        Dim txtbig As String = ""
        Dim txtsmall As String = ""

        For i = 0 To number
            For Each ctrl In Me.Controls
                If TypeOf ctrl Is TextBox Then
                    If ctrl.Name = "txtNum" & i Then
                        txtnum = ctrl.Text
                    ElseIf ctrl.Name = "txtBig" & i Then
                        txtbig = ctrl.text
                    ElseIf ctrl.Name = "txtSmall" & i Then
                        txtsmall = ctrl.text
                    End If
                End If
            Next

            values = "('" & txtnum & "','" & txtbig _
                & "','" & txtsmall & "')"

            cmd.CommandText = insert & values
            cmd.ExecuteNonQuery()
        Next

Let me know how this goes.

PS: You might want to add some checks that ctrl.text is not empty, but as this wasn't part of the requirements I've left it out.

is this work with microsoft access database??

[adam_k]

I've used SQLClient for command and connection, because that's what I had readilly available to test the code, which won't work with Access.

The logic for the loops will definatelly work, so change SQLClient objects with OLE or whatever you are using.

[reds8]

I've used SQLClient for command and connection, because that's what I had readilly available to test the code, which won't work with Access.

The logic for the loops will definatelly work, so change SQLClient objects with OLE or whatever you are using.

thx

[reds8]

I've used SQLClient for command and connection, because that's what I had readilly available to test the code, which won't work with Access.

The logic for the loops will definatelly work, so change SQLClient objects with OLE or whatever you are using.

this is my coding and i get "syntax error in INSER into statement".
Do you know which part has problem??

Dim con As OleDbConnection
    Dim cmd As OleDbCommand
    Dim da As OleDbDataAdapter
    Dim ds As DataSet
    Dim sql As String

    con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Project Database.accdb") 

Try
            Dim txtNum As String = ""
            Dim txtBig As String = "'"
            Dim txtSmall As String = ""
            Dim txtTime As Date = TimeOfDay
            Dim number As Integer = cbAddRows.SelectedItem

            For i = 1 To number
                For Each ctrl In Me.Controls
                    If TypeOf ctrl Is TextBox Then
                        If ctrl.name = "txtNum" & i Then
                            txtNum = ctrl.text
                        End If
                    ElseIf ctrl.name = "txtBig" & i Then
                        txtBig = ctrl.text
                    ElseIf ctrl.name = "txtSmall" & i Then
                        txtSmall = ctrl.text
                    End If
                Next
                con.Open()
                sql = "INSERT INTO tblData (Number, Big, Small,enterTime) VALUES ('" & _
                      txtNum & "','" & txtBig & "','" & _
                      txtSmall & "','" & txtTime & "')"
                cmd = New OleDbCommand(sql, con)
                cmd.ExecuteNonQuery()
                con.Close()
                MsgBox("Datas are saved.")
            Next

        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Message)
        End Try

[adam_k]

It looks legit, but I'm thinking if Number is a reserved word.
You can try to enclose your fields in brackets [] and if that doesn't work when you get the error go to the immediate pane and write debug.print (sql) . When you press Enter you'll get in the immediate pane the exact sql statement that you are trying to run.

Give that a look and possibly try it in Access. Post it here if you don't get it working.