problems with vb2005..with microsoft access

wongth7 · #1 (**permalink**) 07-03-2009, 5:28 AM

hi guys..i have some problem with my VB program..hope u guys can help me out

in my form, there's 8 textboxes that will display data from the database, 3 button's which is '<<' '>>' and 'DELETE'

explanation on my program
---------------------------------
the ' << ' and ' >> ' button will browse through records from the database...while the "delete" button will delete the record from the database...
i have no problem browsing through the records from the database and no problem deleting the records with those buttons

but there's some minor problem..
----------------------------------------
problem 1 - when i click on the 'delete' button to delete a record, the data will still appear in the textboxes when i click on the '<<' and '>>' buttons, although the data in the database already been deleted.

problem 2 (same problem related to problem 1) - when i keep clicking on the 'delete' button to delete all the records, the program will then crash and this error message appear 'Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.'..........how to avoid this??

problem 3 - if the database is empty and i log into the form and click on the '<<' , '>>' buttons, the program will then crash and this error message appear 'There is no row at position 1.' ....how to lock those button's when the database is empty or any other ways??

please show me sample codes or something as i will find it hard to understand if you just explain those logic things etc for me

here's my code....

Code:

_________________________________________________________________
Private Sub frmUser_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

       con.ConnectionString = "PROVIDER = Microsoft.Jet.OLEDB.4.0;Data Source = H:\Testing\Project\user.mdb"
       con.Open()
       sql = "SELECT * FROM Table1"
       da = New OleDb.OleDbDataAdapter(sql, con)
       da.Fill(ds, "Table1List")
       con.Close()
       maxrows = ds.Tables("Table1List").Rows.Count

       txtName.Text = ds.Tables("Table1List").Rows(0).Item(0)
       txtUsername.Text = ds.Tables("Table1List").Rows(0).Item(1)
       txtNickname.Text = ds.Tables("Table1List").Rows(0).Item(2)
       txtPassword.Text = ds.Tables("Table1List").Rows(0).Item(3)
       txtCity.Text = ds.Tables("Table1List").Rows(0).Item(4)
       txtAddress.Text = ds.Tables("Table1List").Rows(0).Item(5)
       txtTown.Text = ds.Tables("Table1List").Rows(0).Item(6)
       txtPostcode.Text = ds.Tables("Table1List").Rows(0).Item(7)

   End Sub

_________________________________________________________________

Public Sub navigaterecords(ByVal position As Integer)

       txtName.Text= ds.Tables("Table1List").Rows(position).Item(0)
       txtUsername.Text = ds.Tables("Table1List").Rows(position).Item(1)
       txtNickname.Text = ds.Tables("Table1List").Rows(position).Item(2)
       txtPassword.Text = ds.Tables("Table1List").Rows(position).Item(3)
       txtCity.Text = ds.Tables("Table1List").Rows(position).Item(4)
       txtAddress.Text = ds.Tables("Table1List").Rows(position).Item(5)
       txtTown.Text = ds.Tables("Table1List").Rows(position).Item(6)
       txtPostcode.Text = ds.Tables("Table1List").Rows(position).Item(7)

   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(inc)
       Else
           MessageBox.Show("No more applications!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
       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(inc)
       Else
           MessageBox.Show("No more applications!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
       End If

   End Sub
_________________________________________________________________

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click


       con.ConnectionString = "PROVIDER = Microsoft.Jet.OLEDB.4.0;Data Source = H:\Testing\Project\user.mdb"
       con.Open()
       sql = "SELECT * FROM Table1"
       da = New OleDb.OleDbDataAdapter(sql, con)
       da.Fill(ds, "Table1List")
       con.Close()
       maxrows = ds.Tables("Table1List").Rows.Count

       Dim cb As New OleDb.OleDbCommandBuilder(da)
       Dim intresult As Integer

       intresult = MessageBox.Show("Confirm Delete?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
       If intresult = Windows.Forms.DialogResult.Yes Then
           ds.Tables("Table1List").Rows(inc).Delete()
           da.Update(ds, "Table1List")
           maxrows = maxrows - 1
           inc = 0
           navigaterecords(inc)

       End If
_________________________________________________________________

wongth7 · #2 (**permalink**) 07-03-2009, 1:58 PM

can someone help please..

cjard · #3 (**permalink**) 07-04-2009, 7:09 AM

Read the DW2 link in my signature, section "Creating a Simple Data App"

At the end of the tutorial you will have an app with a toolbar with the < and> and delete buttons

And it will take you maybe 10 minutes

And it will work

wongth7 · #4 (**permalink**) 07-04-2009, 11:16 AM

Quote:

Originally Posted by cjard

Read the DW2 link in my signature, section "Creating a Simple Data App"

At the end of the tutorial you will have an app with a toolbar with the < and> and delete buttons

And it will take you maybe 10 minutes

And it will work

thanks for the link...but i dont plan to use bindingNavigator..i want to use my own button..any other ideas to solve my problems???

ggunter · #5 (**permalink**) 07-05-2009, 11:43 AM

wongth7,

Don't take this the wrong way but you really would be better off using cjard's suggestion.

Generally speaking, you load an internal dataset with data from a database. When you click the "delete" button, it simply "marks" the record as deleted but the record stays until the the internal dataset is written back to the database and the dataset is cleared. Therefore, you would need to add code to skip a record that is marked as deleted plus more code to deal with all the other related situations (like, what if all the records are marked as deleted, or just the last, or just the first, etc.

).

Cjard's suggestion does all that background coding for you.

wongth7 · #6 (**permalink**) 07-05-2009, 12:10 PM

Quote:

Originally Posted by ggunter

wongth7,

Don't take this the wrong way but you really would be better off using cjard's suggestion.

Generally speaking, you load an internal dataset with data from a database. When you click the "delete" button, it simply "marks" the record as deleted but the record stays until the the internal dataset is written back to the database and the dataset is cleared. Therefore, you would need to add code to skip a record that is marked as deleted plus more code to deal with all the other related situations (like, what if all the records are marked as deleted, or just the last, or just the first, etc.

).

Cjard's suggestion does all that background coding for you.

erm...i appreciate that, but there's too many things for me to change on the interface if im gonna use bindingNavigator..and i dont have much time left...do anyone has other ideas??

cjard · #7 (**permalink**) 07-07-2009, 11:03 AM

Quote:

Originally Posted by wongth7

erm...i appreciate that, but there's too many things for me to change on the interface if im gonna use bindingNavigator..and i dont have much time left...do anyone has other ideas??

Follow my suggestion, get the BindingNavigator on form, see how it works, copy how it works on your own button, delete it

Don't reinvent the wheel, jsut adapt the very good one microsoft already made. Youll want to use BindingSource.MoveNext and MovePrevious

MattP · #8 (**permalink**) 07-07-2009, 11:17 AM

Quote:

Originally Posted by wongth7

erm...i appreciate that, but there's too many things for me to change on the interface if im gonna use bindingNavigator..and i dont have much time left...do anyone has other ideas??

Sometimes it's better to scrap an existing idea and implement a new one. Code your own binding navigator if you've got buttons you want to use.

What is the coding for Next & Previous Button?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)