datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator
Go Back  Xtreme Visual Basic Talk > > > datagridview, dataset and bindingnavigator


Reply
 
Thread Tools Display Modes
  #1  
Old 03-24-2008, 10:00 AM
monkeynote's Avatar
monkeynote monkeynote is offline
Junior Contributor
 
Join Date: Mar 2005
Location: philippines
Posts: 205
Unhappy datagridview, dataset and bindingnavigator


hello guys!

im new to vb.net. i am a vb 6 programmer and decided to shift into .NET programming.

I would like to ask if how can i add/edit/update/delete fields in the database using datagridview and dataset?

i have a code that is very far from what i expected to do... i use OLEDB and my database is GSD.mdb.

Code:
Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim Path As String = System.IO.Directory.GetCurrentDirectory & "\GSD.mdb" Dim SQLStatement As String = "SELECT SupplierID, SupplierName, Address, ContactNo FROM Supplier" Dim Conn As OleDb.OleDbConnection Dim Comm As OleDb.OleDbCommand Try Conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Path & "';Jet OLEDB:Database Password=") Comm = New OleDb.OleDbCommand(SQLStatement) Conn.Open() Catch ex As Exception MsgBox(ex.Message, MsgBoxStyle.Information, "") End End Try Comm.Connection = Conn Dim dataAdapter As New OleDb.OleDbDataAdapter(Comm) Dim dataSet As New DataSet("Supplier") dataAdapter.Fill(dataSet, "Supplier") DataGridView1.DataSource = dataSet DataGridView1.DataMember = "Supplier" End Sub End Class

what this code does is that it only display the query in the datagridview but when i try to edit / add values in the datagridview, it does not update/append to the database.

how can i also integrate bindingnavigator in my datagridview? i've googled around but i can find any example

i hope that you can help me with my problem guys
__________________
GREAT INVENTIONS would not be DONE without FUN
Reply With Quote
  #2  
Old 03-24-2008, 11:48 AM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Hey monkey,

Bindingnav is not much use if you are using the datagrid... datagrid is going to give you all your records in a grid with a slider to view the records that dont fit on the visible part of the grid. The nav is usefull when you have a form with a single text box for each of your data points (db columns). the nav then allows you to fill the form from each record one at a time.

The best way I find to populate the datagrid is to create the grid then select the selection grip (little arrow) at the top right of the grid and set your data source through there. Create a new datasource from scratch. create the select, update, insert and delete commands via the datasource interface, it is a tonne easier.

Best of luck.
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #3  
Old 03-24-2008, 01:13 PM
martonx martonx is offline
Centurion
 
Join Date: Jan 2007
Location: Szeged, Hungary
Posts: 129
Default

The more easier version is use only the VB2005 graphical interface. You don't have to write any code.
You have to add a datasource in Data menu Add New Datasource.
After this you have to drag and drop you datatable to your form, and VB will make you everything (bindingsource, bindingnavigator, sql commands everything)!
Reply With Quote
  #4  
Old 03-24-2008, 02:04 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Martonx,
I am using VB.net 2005, you don't get the update insert and delete from dropping the dataset onto the form. You still have to go in and edit the dataset to include the DML.

What you suggested is simply the same thing I suggested but by creating the dataset first. The only differece is monkey is going to try to edit, add or delete records in the grid and be dissapointed that it didn't happen because he has not set up the DML...
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #5  
Old 03-25-2008, 12:43 AM
monkeynote's Avatar
monkeynote monkeynote is offline
Junior Contributor
 
Join Date: Mar 2005
Location: philippines
Posts: 205
Thumbs up

Quote:
Originally Posted by VisuallyImpared View Post
Bindingnav is not much use if you are using the datagrid... datagrid is going to give you all your records in a grid with a slider to view the records that dont fit on the visible part of the grid. The nav is usefull when you have a form with a single text box for each of your data points (db columns). the nav then allows you to fill the form from each record one at a time.
thanks for clearing the use of bindingnav control. i've been deceived by "add / delete" button at the right side of the control

im new to vb and it feels like entering into a dark crowded room...


thanks your advice guys! i'll do everything that you instructed i feel like a dinosaur programmer... doing stuff the hard way... ****
__________________
GREAT INVENTIONS would not be DONE without FUN
Reply With Quote
  #6  
Old 03-25-2008, 01:20 AM
monkeynote's Avatar
monkeynote monkeynote is offline
Junior Contributor
 
Join Date: Mar 2005
Location: philippines
Posts: 205
Unhappy

hi again...

i created a new form and tried your instruction that you've posted (drag a datagridview and clicking the green arrow... selecting the dataset that contains supplier table and enable add / edit / delete)

i thought that i can append new record but it appears that it does not add the new record that i added.

when i view the source code, it only displays Me.SupplierTableAdapter.Fill(Me.GSDDataSet.Supplier)

how can i add / edit / delete records in my datagridview?

how can i also validate some inputs and change character case (lowercase -> uppercase) in datagridview?

thanks for the advice again guys!
__________________
GREAT INVENTIONS would not be DONE without FUN
Reply With Quote
  #7  
Old 03-25-2008, 05:23 AM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

This is where the fun begins.

Open the XSD file for your datasource. on the bottom frame where you see your existing fill/get data adapter, right click and select add query.

on the pop up click next (use sql should be defaulted already) on the next part of the wizard you get the option to add an INSERT, UPDATE, DELETE, etc... the next window gives you a preview of the command it will build....


That should get you started... work with this for a bit. Try to work it out.. if you get lost again.. send a reply again and I will et back to you as soon as I can.

BTW, once you have created the new queries, you still have to hook them up to the different events on grid
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #8  
Old 03-25-2008, 06:46 AM
martonx martonx is offline
Centurion
 
Join Date: Jan 2007
Location: Szeged, Hungary
Posts: 129
Default

Quote:
Originally Posted by VisuallyImpared View Post
Martonx,
I am using VB.net 2005, you don't get the update insert and delete from dropping the dataset onto the form. You still have to go in and edit the dataset to include the DML.

What you suggested is simply the same thing I suggested but by creating the dataset first. The only differece is monkey is going to try to edit, add or delete records in the grid and be dissapointed that it didn't happen because he has not set up the DML...
You are right. I forgot that he use OLEDB, not Microsoft JET.
But if Monkey use a .mdb, i think my method is simplier, but Monkey you have to connet with Microsoft JET for access, not OLEDB.
In this case VB2005 will make automatically the SQL commands, too (INSERT,UPDATE...)
Reply With Quote
  #9  
Old 03-25-2008, 07:15 AM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Oh, ok then... that makes sense... I work 99% of the time in Oracle, so the interface is not as rich as it is when working with SQL server... it would make sense that if you can do this with SQL server that you can do it with Access.

Otherwise, you have to capture all the grid events and wire them up to the database events.....(anyone please correct me on this, I would love to find out that there is an easier way to do this in Oracle).
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #10  
Old 03-25-2008, 09:47 AM
monkeynote's Avatar
monkeynote monkeynote is offline
Junior Contributor
 
Join Date: Mar 2005
Location: philippines
Posts: 205
Default

uhmmm... im now confussed lolz!

where can i find the Jet connection in Add Data Source? because when i go to add data sources... i can only generate OLEDB connection.

i just simply want to simulate the Visual Studio Server Explorer Data Connections Form displaying all table records inside the datagridview together with bindingnavigator at the bottom. (when you click one table in the server explorer... it will display all the values and you can freely modify the table values).

sorry guys if this turns out to be spoonfeeding but i really need to learn something about vb.net and im trying my best to seek some advice from the experts. i also tried to google around but i can find anything :"(

thank you guys for your patience and advice...
__________________
GREAT INVENTIONS would not be DONE without FUN
Reply With Quote
  #11  
Old 03-26-2008, 05:00 AM
martonx martonx is offline
Centurion
 
Join Date: Jan 2007
Location: Szeged, Hungary
Posts: 129
Default

You can find here:

Add Data Source -> Database -> New Connection button -> Change button -> Choose the MS Access datasource.

Ooops, i understand your problem. Don't you have MS JET, do you? You need to install Microsoft Office with Access first! You have to make a full install.
In this case you will find MS JET in VB2005.
Reply With Quote
  #12  
Old 03-26-2008, 10:37 AM
monkeynote's Avatar
monkeynote monkeynote is offline
Junior Contributor
 
Join Date: Mar 2005
Location: philippines
Posts: 205
Thumbs up eurika!

Thanks for the reply

the code now works but the problem is the delete button... i cant delete data directly from the database when i press "X" on the bindingnavigator.

i'll attach the test program.

what i notice is that, i did not code anything! vb.net did it all for me... i'm so amazed!

how come that there are many code samples on the net that are hard coded? i cant seem to locate other commands / code in my project file. i also don't know what happened when i add my fields to my form... its so mind boggling

thank you for all the guides guys!
Attached Files
File Type: zip WindowsApplication2.zip (524.2 KB, 126 views)
__________________
GREAT INVENTIONS would not be DONE without FUN
Reply With Quote
  #13  
Old 04-01-2008, 12:48 AM
martonx martonx is offline
Centurion
 
Join Date: Jan 2007
Location: Szeged, Hungary
Posts: 129
Default

Hi, okay i will check your attachment.

I told that, VB2005 will make everything automatically. I wonder why doesn't work the delete button.

There is a lot of old fashioned programmer, who like to see everything, and do the less things for days.
Reply With Quote
  #14  
Old 04-01-2008, 01:18 AM
martonx martonx is offline
Centurion
 
Join Date: Jan 2007
Location: Szeged, Hungary
Posts: 129
Default

Hehe it is fully working. When you press delete button, after this, you must to press save.
When you press 'X' the data deleted only from Dataset, and not from database.
When you press save or disk icon, the dataset will update to database (will deleted).
Reply With Quote
  #15  
Old 04-16-2008, 03:26 AM
leo_83 leo_83 is offline
Newcomer
 
Join Date: Apr 2008
Posts: 2
Default Update requires a valid DELETE COMMAND, if a DataRow listing with deleted lines is pa

Hello,

The posts helped me configure all needed,however i get the following error when I try to delete a record and then save it to my database postgres

Update requires a valid DELETE COMMAND, if a DataRow listing with deleted lines is passed on.

what could be the possible reason, tried googling and most of the posts speak about like

'when you are updating a database using a dataset and calling the TableAdapter/DataAdapter's Update() method, you need to create the correct commands. These are InsertCommand, DeleteCommand, UpdateCommand.'

Could someone guide how to do the changes required, being also a newbie am afraid if i could figure it out.

need your help guys!

Thanks
Abhi
Reply With Quote
  #16  
Old 04-17-2008, 03:35 AM
martonx martonx is offline
Centurion
 
Join Date: Jan 2007
Location: Szeged, Hungary
Posts: 129
Default

I think you need to check your DELETE COMMAND in your Dataset. Probably your DELETE command is wrong?
Reply With Quote
  #17  
Old 07-08-2008, 08:30 PM
pjustus pjustus is offline
Newcomer
 
Join Date: Jul 2008
Posts: 1
Cool Add/update/delete/save in vb.net 2005 using bindingnavigator

Hi Monkey

Connecting ms access 2003 to vb.net 2005 using BindingNavigator PLUS Add/Update/Delete/Save controls

I will ask you to do the following:
1. Set up a new project in vb.net 2005, design your supplier GUI as required
2. From the menu bar select View | Server Explorer
3. Right-click on Data Connections, then choose Add Connection...
4. A Choose Data Source windows appears. Select Provider at the top, then select the appropriate provider for your database. (For Microsoft Access databases, choose Ms Access database from the list), then click Next
5. The Connection tab appears. Browse to your database file, and select it.
(Note: when asked if you want vb to make a copy of your db to an output......, simply say NO - otherwise you will not be able to see any changes you make to your underlining ms access db. (this can also be overcomed by selecting your form name in the solution explorer and making sure you select 'Do not copy' in the form's property section))
6. Delete Admin from the User name text box.
7. Click OK
8. Move to the Server Explorer window, and left-click on Data Connections. The file name that you selected should appear.
9. Close the Server Explorer window, and view the toolbox. Instead of selecting Windows Forms, choose Data.
Now we place a Binding Navigator on the form:
10. From the Data section of the Tool box, Drag and drop a Binding Navigator on the form. (Assuming you have an Access database).
11. Select the Binding navigator tool bar, then in the data section of the Binding Navigator properties, select data binding and expand it, then select Advanced and click the ellipse to bind the database table to your form
12. Also, click on the binding source and select your database table Binding source (SupplierBindingSource in your case) to establish the source of data from the database to your vb form.
13. Then select each textbox on your vb form one by one, and select data binding| Advanced in the properties section, then select and click the ellipse to individually bind the textboxes on your form to their respective underlining database fieldnames
14. Now, the setting-up is now complete, and you can enter code which manipulates your database.

I suggest you ignore the vb.net controls on the bindingnavigator placed on your form and WRITE your own codes as I have detailed below - they should be pretty straight forward with some caution:

=========================================================
Public Class frmGSD

Private Sub frmGSD_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'VbGSDDataSet.Supplier table. You can move, or remove it, as needed.
Me.SupplierTableAdapter.Fill(Me.VbGSDDataSet.Supplier)

End Sub

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
SupplierBindingSource.EndEdit()
Me.SupplierTableAdapter.Update(Me.VbGSDDataSet.Supplier)
MessageBox.Show("Record saved")
End Sub

Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click
Dim response As String
response = MsgBox("Are you sure you want to close application?", 20, "Warning")
If response = vbYes Then
Me.Close()
End If
End Sub


Private Sub cmdMvFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdMvFirst.Click
SupplierBindingSource.MoveFirst()
End Sub

Private Sub cmdMvPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdMvPrev.Click
SupplierBindingSource.MovePrevious()
End Sub

Private Sub cmdMvNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdMvNext.Click
SupplierBindingSource.MoveNext()
End Sub

Private Sub cmdMvLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdMvLast.Click
SupplierBindingSource.MoveLast()
End Sub

Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
SupplerBindingSource.AddNew()
End Sub

Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
On Error Resume Next
Dim response As String
response = MsgBox("The current record is about to be deleted!", 20, "Warning")
If response = vbYes Then
SupplierBindingSource.RemoveCurrent()
SupplierBindingSource.EndEdit()
Me.SupplierTableAdapter.Update(Me.VbGSDDataSet.Supplier)
MessageBox.Show("Record Deleted")
End If
End Sub
End Class
=========================================================
I hope I have managed to help you here.

Enjoy!

PJustus.

Last edited by pjustus; 07-08-2008 at 08:39 PM. Reason: Heading - Includes Connecting ms access 2003 to vb.net 2005 using BindingNavigator
Reply With Quote
  #18  
Old 07-08-2008, 10:48 PM
monkeynote's Avatar
monkeynote monkeynote is offline
Junior Contributor
 
Join Date: Mar 2005
Location: philippines
Posts: 205
Thumbs up thanks!

thank you for your reply... pjustus

the instructions in your post is very straightforward thank you so much! my form is now working

for others that want to accomplish this code in sqlserver / express you may also check out this video from microsoft

http://msdn.microsoft.com/en-us/vbasic/bb643827.aspx
__________________
GREAT INVENTIONS would not be DONE without FUN
Reply With Quote
Reply


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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator
 
datagridview, dataset and bindingnavigator
datagridview, dataset and bindingnavigator
 
-->