Cannot Add new row to database with DataAdapter.insertCommand vb.net

Question

I try to add new record to the database with command DataAdapter.InsertCommand. But I don't know why It doesn't insert new record to database

Here is my code:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
    Dim cnn As New OleDb.OleDbConnection
    Dim cmd As New OleDb.OleDbCommand
    Dim da As New OleDb.OleDbDataAdapter
    Dim ds As New DataSet
    Dim dt As New DataTable
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\1_Project\Project_of_VB_Net\AccessDatabase\StudentDatabase.accdb"
    cmd.Connection = cnn
    cmd.CommandText = "INSERT INTO StudentData(StudentID) VALUES (@studentid)"
    cmd.Parameters.AddWithValue("@studentid", "123")
    Try
        cnn.Open()
        da.InsertCommand  = cmd
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    Finally
        cnn.Close()
    End Try
End Sub

Please help me find out what's wrong with it?

You need to execute the query. AddWithValue is sub-optimal especially if StudentId is a number ("123" is not). But why are you inserting Ids? Thats the DB's job. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Apr 8, 2017 at 17:50
@Plutonix I think that does da.insertcommand=cmd that mean "execute the query"? StudentID field is a text when i declare in database. i insert IDs because I would like when i click button add, it willl create a new row which have a ID first (ID is a text and is put from textbox) — Bruce
– Bruce, Commented Apr 8, 2017 at 18:37
I have never used a data adapter like you have and I don't see how it would work. Either use the command directly without an adapter (cmd.ExecuteNonQuery()) or configure your dataadaptor with the insert command, map the parameter to your studentid field, and then insert the record to your datatable and run da.Update(dt) — Haim Katz
– Haim Katz, Commented Apr 9, 2017 at 14:07
@HaimKatz Hi katz, I found the solution for my problem and update above . Thank you so much for your help :) — Bruce
– Bruce, Commented Apr 9, 2017 at 14:27
Don't put answers in the question. Just post your own answer below and accept it. — LarsTech
– LarsTech, Commented Apr 13, 2017 at 2:17

Haim Katz · Accepted Answer · 2017-04-13 02:13:03Z

2

I'm all in favor of using what works and I see the solution above works. The advantage of using a dataadapter however, is that it wraps all the sql commands needed for the CRUD operations of the datatable. You can configure your own commands or let visual studio configure them automatically by using a sqlcommandbuilder. Then once you have the adapter configured all you need to do is something like this

    Dim myds As New DataSet()
    Dim mytable as New datatable()
    myds.Tables.Add(mytable)
    Dim myconstr As String = "ConnectionStringtoDataBase"
    Dim myconn As New SqlConnection(myconstr)

    Dim da as new SqlDataAdapter( “SELECT * FROM StudentTable”,cnn)
    Dim mycb As New SqlCommandBuilder(da)
    da.Fill(mytable)
    Dim myrow As DataRow = mytable.NewRow()
    myrow("ID") = "thestudentid"
    myrow("StudentName") = "John doe"
    myrow("StudentID") = 12345
    myrow("StudentClass") = "VB 101"
    mytable.Rows.Add(myrow)
    da.Update(mytable)

The CommandBuilder will automatically add insert, update and delete commands to the dataadapter (if your select command is one table only, otherwise you'll have to configure it manually).

edited Apr 13, 2017 at 2:13

answered Apr 12, 2017 at 7:12

Haim Katz

4613 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bruce Over a year ago

It's seem to be a good idea to try. But could you please tell me more detail about it? How can I have the adapter configured. I tried with your part (Comment everything just except your part which you said) but I think it's not enough and it say error. COuld you please give an example or somthing

Haim Katz Over a year ago

I have updated my answer. My previous answer confused the tableadapter with the dataadapter. Truth is I haven't used a datadapter for quite a while, I find using typed datasets and the tableadapter much easier and helps prevent programming mistakes. If you are unfamiliar with them look them up in msdn. Once you start with them you won't go back to untyped datasets.

Collectives™ on Stack Overflow

Cannot Add new row to database with DataAdapter.insertCommand vb.net

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related