Need help in database Please

[carlos001]

Hi,
I am new and speak spanish,but this is a great forum and i try to explain my problem. I have a exercise to connecta to a database all work, but when i create a new record the record is no visible until i close and reopen my app.
the problem is in btnCommit . I think i need 2 thinks first what row fill navegate sub when i add the record for example my rows is the variable (inc). What inc you recommend and the second i think, i need clear the dataset and refill with new data from the data adapter if the data adapter have the update? and refill the data set to view the new record without close the app. Please Help!!
Sorry my english!! and i attach the project (Remember change the string to the database conection)
I code is:

Public Class Form1
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim dbSource As String
    Dim con As New SqlClient.SqlConnection
    Dim ds As New DataSet
    Dim da As SqlClient.SqlDataAdapter
    Dim sql As String
   
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dbSource = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Carl\Desktop\VBNET\Netfiles\databases\AddressBook.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
        con.ConnectionString = dbSource
        con.Open()
        sql = "SELECT * FROM tblContacts"
        da = New SqlClient.SqlDataAdapter(sql, con)
        da.Fill(ds, "AddressBook")
 
        con.Close()
 
        MaxRows = ds.Tables("AddressBook").Rows.Count
        inc = 0
        NavigateRecords()
 
    End Sub
 
    Private Sub NavigateRecords()
        txtFirstName.Text = ds.Tables("AddressBook").Rows(inc).Item(1)
        txtSurname.Text = ds.Tables("AddressBook").Rows(inc).Item(2)
    End Sub
 
    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If inc <> MaxRows - 1 Then
            inc = inc + 1
            NavigateRecords()
        Else
            MsgBox("Last Record")
        End If
    End Sub
 
    Private Sub BtnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPrevious.Click
        If inc > 0 Then
            inc = inc - 1
            NavigateRecords()
        Else
            MsgBox("First Record")
        End If
    End Sub
 
    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        If inc <> MaxRows - 1 Then
            inc = MaxRows - 1
            NavigateRecords()
        End If
    End Sub
 
    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        If inc <> 0 Then
            inc = 0
            NavigateRecords()
        End If
    End Sub
 
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim cb As New SqlClient.SqlCommandBuilder(da)
 
        ds.Tables("AddressBook").Rows(inc).Item(1) = txtFirstName.Text
        ds.Tables("AddressBook").Rows(inc).Item(2) = txtSurname.Text
 
        da.Update(ds, "AddressBook")
 
        MsgBox("Data updated")
    End Sub
 
    Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
        btnCommit.Enabled = True
        btnAddNew.Enabled = False
        btnUpdate.Enabled = False
        btnDelete.Enabled = False
        txtFirstName.Clear()
        txtSurname.Clear()
    End Sub
 
    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        btnCommit.Enabled = False
        btnAddNew.Enabled = True
        btnUpdate.Enabled = True
        btnDelete.Enabled = True
        inc = 0
        NavigateRecords()
    End Sub
 
    Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click
        If inc <> -1 Then
            Dim cb As New SqlClient.SqlCommandBuilder(da)
            Dim dsNewRow As DataRow
            dsNewRow = ds.Tables("AddressBook").NewRow()
            dsNewRow.Item("FirstName") = txtFirstName.Text
            dsNewRow.Item("Surname") = txtSurname.Text
            ds.Tables("AddressBook").Rows.Add(dsNewRow)
            da.Update(ds, "AddressBook")
            ds.Tables("AddressBook").AcceptChanges()
            ds.Tables("AddressBook").Clear()
            con.Open()
            sql = "SELECT * FROM tblContacts"
            da = New SqlClient.SqlDataAdapter(sql, con)
            da.Fill(ds, "AddressBook")
 
            con.Close()
 
            inc = MaxRows - 1
            NavigateRecords()
            MsgBox("New Record added to the Database")
            btnCommit.Enabled = False
            btnAddNew.Enabled = True
            btnUpdate.Enabled = True
            btnDelete.Enabled = True
        End If
    End Sub
 
    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If MessageBox.Show("Do you really want to Delete this Record?", _
"Delete", MessageBoxButtons.YesNo, _
MessageBoxIcon.Warning) = Windows.Forms.DialogResult.No Then
            MsgBox("Operation Cancelled")
            Exit Sub
        End If
        Dim cb As New SqlClient.SqlCommandBuilder(da)
        ds.Tables("AddressBook").Rows(inc).Delete()
        MaxRows = MaxRows - 1
        Select Case inc
            Case Is < 1
                inc = 1
            Case Is >= MaxRows - 1
                inc = MaxRows - 1
            Case Else
 
        End Select
        NavigateRecords()
        da.Update(ds, "AddressBook")
    End Sub
End Class

[stateofidleness]

you should be able to call a da.Fill(ds, "AddressBook")
followed by a da.Update() and it should work

[carlos001]

No work for what???LOL
i use this:

Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click
        If inc <> -1 Then
            Dim cb As New SqlClient.SqlCommandBuilder(da)
            Dim dsNewRow As DataRow
            dsNewRow = ds.Tables("AddressBook").NewRow()
            dsNewRow.Item("FirstName") = txtFirstName.Text
            dsNewRow.Item("Surname") = txtSurname.Text
            ds.Tables("AddressBook").Rows.Add(dsNewRow)
            da.Fill(ds, "AddressBook")
            da.Update(ds, "AddressBook")
            inc = MaxRows - 1
            NavigateRecords()
            MsgBox("New Record added to the Database")
            btnCommit.Enabled = False
            btnAddNew.Enabled = True
            btnUpdate.Enabled = True
            btnDelete.Enabled = True
        End If
    End Sub

[stateofidleness]

are you using a data-bound control to display the database?

[carlos001]

nop check this

Private Sub NavigateRecords()
txtFirstName.Text = ds.Tables("AddressBook").Rows(inc).Item(1)
txtSurname.Text = ds.Tables("AddressBook").Rows(inc).Item(2)
End Sub
and

dsNewRow.Item("FirstName") = txtFirstName.Text
dsNewRow.Item("Surname") = txtSurname.Text

[carlos001]

no i no use the wizard i try to learn first the code logistic, check this

Private Sub NavigateRecords()
txtFirstName.Text = ds.Tables("AddressBook").Rows(inc).Item(1)
txtSurname.Text = ds.Tables("AddressBook").Rows(inc).Item(2)
End Sub
and

dsNewRow.Item("FirstName") = txtFirstName.Text
dsNewRow.Item("Surname") = txtSurname.Text

[stateofidleness]

so it sounds like the problem is that you ARE updating your database, you just aren't updating your form's controls to display the new data (textbox, DataGridView, listbox, or whatever it may be)

You would need to have a procedure that would basically go through all your database information-displaying controls and "re-get" the new values. hope that makes sense.

a data-bound control would show you the new info right away as soon as you did the Fill/Update combination, your self-created controls will not, so you have to do it manually with code.

[carlos001]

you have a example

[Hack]

What are you using on your form?

[carlos001]

Hi,
I find the problem and is easy i no refresh the row count after update the dataset and data adapter the bottom is now:

Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click
        If inc <> -1 Then
            Dim cb As New SqlClient.SqlCommandBuilder(da)
            Dim dsNewRow As DataRow

            dsNewRow = ds.Tables("AddressBook").NewRow()

            dsNewRow.Item("FirstName") = txtFirstName.Text
            dsNewRow.Item("Surname") = txtSurname.Text
            ds.Tables("AddressBook").Rows.Add(dsNewRow)
            da.Update(ds, "AddressBook")
            da.Fill(ds, "AddressBook")

            MaxRows = ds.Tables("AddressBook").Rows.Count

            inc = MaxRows - 1
            NavigateRecords()
            MsgBox("New Record added to the Database")

            btnCommit.Enabled = False
            btnAddNew.Enabled = True
            btnUpdate.Enabled = True
            btnDelete.Enabled = True

            
        End If
    End Sub

[stanav]

1. You should use a bindingsource and a bindingnavigator control instead of coding your own next/previous/firt/last... buttons. This will make your life a whole lot easier.
2. When you add a new datarow to you addressbook datatable in button commit click, you update the database and that all yoo have to do. There no need to clear the datatable and re-fill it. To update the count, you just re-read the row count from the addressbook datatable. And I saw that you already spotted this in your last post

[carlos001]

Yes and thanks !!
But i try to learn all functions in code first. if anyone have a example of data binding to this program. Y try to activate the autocomplete and when the auto complete suggest the program get the complete row for the others field.
Any help?