Solved

dataadapter datagridview

Posted on 2006-11-03
14
704 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
14 Comments
 
LVL 3

Expert Comment

by:Kudzullc
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

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

Expert Comment

by:Kudzullc
Comment Utility
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
Comment Utility
thanks

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

Expert Comment

by:Kudzullc
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jackjohnson44
Comment Utility
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
Comment Utility
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
Comment Utility
I just copied the SQL string you had in original post.  Thats all!

Lucas
0
 
LVL 3

Expert Comment

by:Kudzullc
Comment Utility
Should have been

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

Expert Comment

by:maralans
Comment Utility
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
Comment Utility
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
Comment Utility
Mine won't update!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now