Solved

dataadapter datagridview

Posted on 2006-11-03
14
708 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
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

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…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

816 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