Member Avatar for mavtcr

I want to save data in to my ACCESS database from textboxes. My code is here.

 Private Sub BtnSubmit_Click(sender As Object, e As EventArgs) Handles BtnSubmit.Click
 Dim cb As New OleDb.OleDbCommandBuilder(DA)
 Dim dsNewRow As DataRow
 DA = New OleDb.OleDbDataAdapter(SQL, CN)
 DA.Fill(DS, "Deposit")
 dsNewRow = DS.Tables("Deposit").NewRow()
 dsNewRow.Item("Bank") = TxtBank.Text
 dsNewRow.Item("Acno") = TxtAcno.Text
 dsNewRow.Item("Name") = TxtName.Text
 DS.Tables("Deposit").Rows.Add(dsNewRow)
 DA.Update(DS, "Deposit")
 MsgBox("New Record added to the Database")
 clearall()
 End Sub

On running the programme It is interrupted and getting the following message "Update requires a valid Insert Command when passed DataRow Collection with new rows". Please correct my code Or guide me how can I save the datas from my text boxes.. Thanks in advance...

  • 2 Contributors
  • 5 Replies
  • 372 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by oussama_1

Dani AI

Generated

Brief expert note: the runtime error "Update requires a valid InsertCommand when passed DataRow collection with new rows" means the DataAdapter has no InsertCommand to execute. In the original code the CommandBuilder is created before the DataAdapter is assigned, so it never had the adapter to build commands from. Also, CommandBuilder can only auto-generate SQL when the DataAdapter’s SelectCommand returns the table’s primary key (or the DataTable.PrimaryKey is set). used the DataAdapter/DataRow approach; suggested a direct command — that works but the posted concatenated SQL is vulnerable and brittle.

Safe, practical options:

  • Use a parameterized OleDbCommand for simple insert/update/delete (recommended for clarity and safety). Example insert pattern:
Using cn As New OleDb.OleDbConnection(connectionString),
      cmd As New OleDb.OleDbCommand("INSERT INTO Deposit (Bank, Acno, [Name]) VALUES (?, ?, ?)", cn)
    cmd.Parameters.Add("Bank", OleDb.OleDbType.VarChar).Value = TxtBank.Text
    cmd.Parameters.Add("Acno", OleDb.OleDbType.VarChar).Value = TxtAcno.Text
    cmd.Parameters.Add("Name", OleDb.OleDbType.VarChar).Value = TxtName.Text
    cn.Open()
    cmd.ExecuteNonQuery()
End Using
  • If continuing with DataAdapter + CommandBuilder, instantiate the DataAdapter first (for example, New OleDbDataAdapter("SELECT * FROM Deposit", cn)), then create the CommandBuilder on that adapter, ensure the select includes the primary key, then Fill, add the row, and call Update.

Other practical notes: avoid using user-visible fields (like Name) as the WHERE key — use an ID/primary key for UPDATE/DELETE. Wrap field names that may be reserved (e.g., [Name]) in square brackets. Always use parameterized commands (OleDb uses positional ? placeholders — parameter order matters). Use Using blocks and catch exceptions to surface error messages (connection string, field types, or missing PK are common causes).

Recommended Answers

All 5 Replies

Member Avatar for oussama_1 
        'add yourTableName
        Dim insertCommand As New OleDb.OleDbCommand("INSERT INTO yourTableName (Bank, Acno, Name) VALUES ('" & TxtBank.Text & "','" & TxtAcno.Text & "','" & TxtName.Text & "')", CN)
        Try
            CN.Open()
            insertCommand.ExecuteNonQuery()
            MsgBox("Record Added Successfully")
        Catch ex As Exception
        Finally
            CN.Close()
        End Try
Member Avatar for mavtcr

Thank you...But Where should I add your code in my code,Or should I cancel all my statements and add your code in my BtnSubmit _Click Please guide me

Member Avatar for oussama_1

yes replace your code with mine under btnSubmit click event

Member Avatar for mavtcr

Ok thank you.. And If I want to 1.Delete a record 2.Update record after modification What changes I have to make in the above code please...

Member Avatar for oussama_1

you should chnage the statment(Oledb command) like "DELETE FROM table WHERE Name =" & TxtName.Text & "" and other few modifications but this is a different question, there's a lot of free tutorials on this,try it on your own if you get any errors then post it.
if this question is solved then mark it as such
good luck.

Edited by oussama_1
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.