Member Avatar for sandunkumara

hi,

I want to insert muliple textbox values to muliple column in access db from vb.net

Edited by sandunkumara because: typing errors
  • 2 Contributors
  • 1 Reply
  • 219 Views
  • 16 Minutes Discussion Span
  • Latest Post Latest Post by djjeavons

Dani AI

Generated

As is trying to write several TextBox values into an Access table, was right to point toward parameterized statements. A few targeted, practical tips will make that approach robust and fast for multiple fields/rows.

OleDb notes you must watch parameter ordering: the OLE DB provider binds parameters by position, so the order you add parameters has to match the placeholders in your SQL. Also avoid relying on implicit type inference (AddWithValue) for every field — explicitly create parameters with the correct OleDbType and size so you don’t get unexpected conversions or performance problems. (learn.microsoft.com)

For inserting many rows, open one connection, start a transaction, create a single prepared command with typed parameters, then loop and set each parameter’s Value before calling ExecuteNonQuery; commit at the end. This reuses the command and reduces round trips and locking. Example pattern (VB.NET):

Using cn As New OleDbConnection(connString)
  cn.Open()
  Using tx = cn.BeginTransaction()
    Using cmd As New OleDbCommand("INSERT INTO MyTable (ColA, ColB, ColC) VALUES (?,?,?)", cn, tx)
      cmd.Parameters.Add("pA", OleDbType.VarWChar, 100)
      cmd.Parameters.Add("pB", OleDbType.VarWChar, 100)
      cmd.Parameters.Add("pC", OleDbType.Integer)
      cmd.Prepare()
      For Each r In rowsToInsert
        cmd.Parameters(0).Value = If(String.IsNullOrWhiteSpace(r.A), DBNull.Value, r.A)
        cmd.Parameters(1).Value = If(String.IsNullOrWhiteSpace(r.B), DBNull.Value, r.B)
        cmd.Parameters(2).Value = If(r.C Is Nothing, DBNull.Value, r.C)
        cmd.ExecuteNonQuery()
      Next
      tx.Commit()
    End Using
  End Using
End Using

Use transactions and Prepare when you run the same statement repeatedly — it helps both correctness and speed. (learn.microsoft.com)

Quick troubleshooting checklist: make sure the ACE/JET provider is installed and matches your app bitness (x86 vs x64); bracket any column names that are reserved or contain spaces; pass DBNull.Value for SQL NULLs; and inspect parameter values in the debugger if rows fail. If the provider is missing or mismatched, install the Access Database Engine redistributable that matches your environment. (microsoft.com)

These suggestions expand on ’s reply: explicit parameter types, consistent ordering, transactions, and careful null handling solve most “no rows” or type-mismatch issues when inserting multiple TextBox values into Access from VB.NET.

Member Avatar for djjeavons

Hi

This would usually be done with an INSERT statement. For example, if you had two text boxes for First Name and Last Name and you wanted to insert that data into a table called Contacts with fields FirstName and LastName then your INSERT statement would look like:

INSERT INTO Contacts (FirstName, LastName) VALUES ('Joe', 'Bloggs')

To do this via code, taking the data from your text boxes you would need to construct the string accordingly, something like:

INSERT INTO Contacts (FirstName, LastName) VALUES ('" & firstNameTextBox.Text & "', '" & lastNameTextBox.Text & "')"

The above however opens you up to some issues such as SQL Injection attack and having to know the correct characters in which to enclose your values (apostraphe for strings, hash/pound character for dates and nothing for numeric). A better approach would be to use parameterised queries:

INSERT INTO Contacts (FirstName, LastName) VALUES (?, ?)

So, using the parameterised option, your code would be something like:

Dim connectionString As String = "Your connection string" 'Check www.connectionstrings.com for Access
Dim insertStatement As String = "INSERT INTO Contacts (FirstName, LastName) VALUES (?, ?)

    Using connection As New OleDbConnection(connectionString)

        Using command As New OleDbCommand(insertStatement, connection)

            command.Parameters.AddWithValue("?", firstNameTextBox.Text)
            command.Parameters.AddWithValue("?", lastNameTextBox.Text)

            connection.Open()
            command.ExecuteNonQuery()

        End Using

    End Using

For more information on working with Access and VB.NET, you might find this tutorial useful: . It covers basic CRUD (Create, Read, Update and Delete) operations. It is written in C# but there is a link to a VB.NET version.

HTH

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.