unable to save data in database in vb.net

Question

I need a simple program which add,edit,save,delete data from database in vb.net. I have tried dataset or sqlcommandbuilder but both are not working. please look at my code I have used with sqlcommandbuilder, it is not saving anything in database. Please provide simplest method to save data in database from dataset or direct from textbox. In this vb.net is not showing any error but not saving

'set up a connection string'
Dim connectionstring As String

connectionstring = "Data Source=|DataDirectory|\inventorymanage.sdf;Password='XXX'"

Dim sqlquery As String = "SELECT * FROM ledger"

'create connection'
Dim connaction As SqlCeConnection = New SqlCeConnection(connectionstring)

Try
    'open connection'
    connaction.Open()



    'create data adapter'
    Dim da As SqlCeDataAdapter = New SqlCeDataAdapter(sqlquery, connaction)

    'create command builder'
    Dim commandbuilder As SqlCeCommandBuilder = New SqlCeCommandBuilder(da)


    'create dataset'
    Dim ds As New DataSet

    'fill dataset'
    da.Fill(ds, "ledger")

    'get datatable'
    Dim dt As DataTable = ds.Tables("ledger")

    Dim dsnewrow As DataRow

    dsnewrow = ds.Tables("ledger").NewRow()



    ds.Tables("ledger").Rows.Add(dsnewrow)
    With dt
        ' modify data rows in ledger
        .Rows(0).Item(1) = Textbox4.Text
        .Rows(0).Item(2) = TextBox5.Text
    End With



    da.Update(ds, "ledger")


    MsgBox("record added")
Catch ex As SqlCeException
    MsgBox("you got error" & ex.ToString & vbCrLf)

Finally
    'close connection'
    connaction.Close()
End Try

Answer 1

 Dim con As New OleDb.OleDbConnection
Dim sql As String
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CMS.mdb"
    con.Open()
    sql = "insert into tblStaffType (Staff_Type,Code) VALUES (@Staff_Type,@Code)"
    Dim command = New OleDb.OleDbCommand(sql, con)
    command.Parameters.AddWithValue("@staff_type", txtTypName.Text)
    command.Parameters.AddWithValue("@Code", txtTypCde.Text)
    command.ExecuteNonQuery()
    con.Close()
    MsgBox("saved")
End Sub

Answer 2

You should not Add the new DataRow to the DataTable until you're finished with it:

Dim newRow As DataRow = ds.Tables("ledger").NewRow
newRow(1) = Textbox4.Text ' set the value for the second field '
newRow(2) = Textbox2.Text ' set the value for the third field '
ds.Tables("ledger").Rows.Add(newRow)
da.Update(ds, "ledger")

http://msdn.microsoft.com/en-us/library/5ycd1034%28v=vs.100%29.aspx

Edit: Although i must admit that the order should not be important, but why are you using Dim dt As DataTable = ds.Tables("ledger") at all? This is just confusing since you actually don't need it.