Solved

dataadapter datagridview

Posted on 2006-11-03
14
711 Views
Last Modified: 2011-10-03
I want to fill a datagridview, enter data into it, then save the data back to the database.
It should be pretty simple, but I can't get it to work.

I was using another post, but all the comments were getting cluttered and no one was answering the question.  It is pretty important, I have been struggling for days.  If you want to see the other question, it is here: http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_22044293.html

select * from users
ID (auto increment), username, password
0
Comment
Question by:jackjohnson44
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 3

Expert Comment

by:Kudzullc
ID: 17869872
Do you need to specify Headers?

If not, do this....


Dim myTable As New DataTable
Dim cmdFillForm As New OleDb.OleDbDataAdapter("select * from users ID (auto increment), username, password", onnectionOleDB)
cmdFillForm.Fill(myTable)
dgViewUsers.DataSource = myTable

dgViewUsers being the drag and drop datagridview component.

This will simply display the select in a datagridview.  Very simple and can be modified.  You can also label static information for the column headers.  play around with this, then modify.

gl,
Lucas
0
 

Author Comment

by:jackjohnson44
ID: 17869909
thanks, but my problem was mostly with updating the database after tying something into my dataview.  Can you please show how to do that?
0
 
LVL 3

Expert Comment

by:Kudzullc
ID: 17869912
ahhh, i always do this...  i see the issue i the other post and i completely missed the idea of the post.  to be completely honest, i would only use datagrid for viewing only and create totally separate small forms to add data and simply refresh on me.close.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jackjohnson44
ID: 17869927
I thought the datagrids were made to do what I am saying.
0
 
LVL 3

Expert Comment

by:Kudzullc
ID: 17869995
They have the data members to do this, yes, but i would programatically move around this functionality.  The only reason i could think of someone needing to have this access is in an admin situation.  If users are allowed this funationality then the data is vunerable and may start showing anomalies based on inconstent data when 'many' users access this at the same time.  I have not read the entire thread in other forum and didnt read the 'save back to database" part in the original post.  you have many experts following through and they should be able to provide extensive answers as well as advice.

When i posted, i just knew how to fill a datagridview and manipulate the data going in.  That's all!  Once upon a time, I tinkered around with the functionailty of the Datagrid members.  Now i do manual builds and the data is manipulated using forms.

sry&gl,
Lucas
0
 

Author Comment

by:jackjohnson44
ID: 17870022
thanks

so you would display data in the grid and enter it in a different way?
0
 
LVL 3

Expert Comment

by:Kudzullc
ID: 17870047
jj,

sure.  i would set the datagridview to readonly and only allow them to search thru refreshing (dynamic sql build) and create a seperate form to add, modify and delete.  That way you control the data more effeciently and less oppurnity for mistakes.  i have done it like this many times.

Lucas
0
 

Author Comment

by:jackjohnson44
ID: 17870077
thanks, one more quick question:

Why did you specifu the auto increment?
Does this do anything to the dataset?

select * from users ID (auto increment), username, password
0
 
LVL 3

Expert Comment

by:Kudzullc
ID: 17870095
jj,

i think binding the data to a form is going to be you best bet. If you have trouble doing this, then read through the link below...

http://www.startvbdotnet.com/ado/simplebinding.aspx

It is a very good discussion/tutorial on binding data to a form.  Once you have the form, simply put a button on the form to fill a datagridview.  

OR, lol just thought of this.  You can set a refresh routine so that on the bottom of the form, below your binded data, a datagridview could be present.  Everytime you update, insert or delete, you can refresh the datagrid to give realtime views.

Lucas
0
 
LVL 3

Expert Comment

by:Kudzullc
ID: 17870104
I just copied the SQL string you had in original post.  Thats all!

Lucas
0
 
LVL 3

Expert Comment

by:Kudzullc
ID: 17870111
Should have been

"select  ID , username, password from users"
0
 
LVL 5

Expert Comment

by:maralans
ID: 17870779
Dim cm As CurrencyManager

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

        yourDataAdapter.Fill(yourDataSet, "yourTable")
        yourDataGrid.DataSource = yourDataSet.Tables("yourTable")

        cm = CType(BindingContext(yourDataSet.DataSource), CurrencyManager)
        Dim cb As Oledb.oledbCommandBuilder = New oledb.oledbCommandBuilder(yourDataAdapter)
     
       
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
       yourDataAdapter.Update(yourDataSet, "yourTable")
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click
        cm.EndCurrentEdit()
        cm.RemoveAt(cm.Position)
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        cm.EndCurrentEdit()
        cm.AddNew()
    End Sub


0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 17871205
It looks like a bit of explanation of what you need, and why, might be helpful.

You only need two objects declared at form level

    Private myAdapter As OleDbDataAdapter
    Private myTable As New DataTable

There is no harm in declaring others - e.g. connection, currencymanager - at form level if you wish, but, with just those two you can manage.

To function properly, myAdapter will need instantiating - for which it will need a connection and a select statement - and the necessary update commands will need to be created for it.  Once myAdapter has been set up it can then be used to fill myTable and it can be accessed from anywhere on the form to update the database.  So the form load sub needs code like this

   'create the connection that we need for myAdapter
   Dim ConnectionString As String = <your string goes here>
   Dim con As New OleDbConnection(ConnectionString)

   'instantiate myAdapter
   myAdapter = New OleDbDataAdapter("select * from users", con)
   'as the connection is now "part of" myAdapter we won't need to refer to it again

   'get the update commands
   dim cb As New OleDbCommandBuilder(myAdapter)
   'the update commands are now, in effect, "part of" myAdapter
   'technically, that is not strictly correct, but it's OK to treat it as if it were correct

   'use myAdapter to fill myTable
   myAdapter.Fill(myTable)

   'bind myTable to the DataGridView
   DataGridView1.DataSource = myTable

And that's enough to get things started.

Now, when any change is made to the data in the datagridview it will also be made in myTable.  This won't automatically happen as soon as any change is made in a single cell, but it will happen automatically as soon as the suer moves to another row.  But your code can force it to happen even while the focus remains on the same row with this code

   BindingContext(myTable).EndCurrentEdit

Whenever you then want changes that have been made in the datatable to get back to the database all the code you need is

   'make sure first that there are no outstanding edits
   BindingContext(myTable).EndCurrentEdit
   'then update the database from the datatable
   myAdapter.Update(myTable)

It's up to you where you put that code.  It is not necessary (so far as datahandling within your app is concerned) for it to be called every time any record is altered.  All alterations - deletions, insertion of new records, amendment of existing records - are saved up by the datatable and, when that code is called, the dataadapter (or the commandbuilder on its behalf) will check each record to see what, if anything, needs doing and use the appropriate command - Delete, Insert or Update - to do it.  But, on the other hand, if you feel you need to do it after every change, you can have buttons to achieve that.  Obviously, you will want to make sure it is called at least once, before your app closes.

There can be all sorts of bells and whistles added to the basic code outlined above.  But, as it stands, it will do the job.

Roger
0
 

Expert Comment

by:mrbaffl
ID: 20079562
Mine won't update!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Suggested Courses

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question