Go Back   Access World Forums > Apps and Windows > VB.NET

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-30-2018, 06:41 PM   #1
Ryobi
Newly Registered User
 
Join Date: Mar 2012
Posts: 13
Thanks: 1
Thanked 0 Times in 0 Posts
Ryobi is on a distinguished road
Saving Data in VB Net

Hello,


I am new to VB net and connections. I am trying to save data from an Ms Access table using a table adapter. Everything works fine, except that I can save changes to the data. This is what I have:


MYConn = New OleDbConnection(StrConn)
Dim Sql As String = "Select * From Locations"
cmd = New OleDbCommand(Sql, MYConn)
Try
MYConn.Open()
Dim da As New OleDb.OleDbDataAdapter(Sql, MYConn)
Dim dt As New DataTable

da.Fill(dt)

BindingSource1.DataSource = dt
BindingNavigator1.BindingSource = BindingSource1

TxTLocCode.DataBindings.Add(New Binding("Text", BindingSource1, "Loc-Code"))
TxtLocation.DataBindings.Add(New Binding("Text", BindingSource1, "Location"))

MYConn.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try



I can go through the records but any changes I make are not saved. I need know I need something like Bindingsource1.Save( Table.Fields) or Bindingsource1.Update (Table.Fields) in a button to press to save the data. Do I need to create a dataset to save it or there something wrong with my bindings ? Any help would be appreciated.

Ryobi is offline   Reply With Quote
Old 06-07-2018, 04:59 PM   #2
jleach
Newly Registered User
 
jleach's Avatar
 
Join Date: Jan 2012
Location: New York, NY
Posts: 302
Thanks: 16
Thanked 68 Times in 68 Posts
jleach will become famous soon enough
Re: Saving Data in VB Net

Hi,

At no point are you actually saving the records. You fetch them from the server, but using ADO.NET, you need to explicitly update them as well.

It's been a long time since I used ADO directly to do this, but look at
Code:
cmd.ExecuteNonQuery()
, which is used to execute a statement rather than Selecting, which is what you're doing with
Code:
da.Fill
.

As a side note, try to separate your UI stuff from your data access stuff. For example, you open a connection, fill a dataset, apply the bindings to the UI, then close the connection. For one, the connection should be open only for as long as you need it (and the connection and command objects should be wrapped in a With block to ensure proper disposal - they're both IDisposables), and for two, if you return the data set to some other calling method then use that to bind the UI stuff, you've done well at separating concerns, which often means it's easier to make sense of other things on the whole.

hth
__________________
- Jack D. Leach

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Microsoft MVP 2012-2016
jleach is offline   Reply With Quote
Old 06-07-2018, 11:14 PM   #3
Ryobi
Newly Registered User
 
Join Date: Mar 2012
Posts: 13
Thanks: 1
Thanked 0 Times in 0 Posts
Ryobi is on a distinguished road
Re: Saving Data in VB Net

Hi, Thank you for replying. I am understand what you are saying but I am confused and I will try explain why. When data in text boxes on screen are binded to da.fill on the statements:

TxTLocCode.DataBindings.Add(New Binding("Text", BindingSource1, "Loc-Code"))
TxtLocation.DataBindings.Add(New Binding("Text", BindingSource1, "Location"))

Is the data being retrieve only from the da.fill to Text Box. So any changes made on the Text Box will not travel back to the da.fill. The changes will have be done via Cmd.ExecuteNonQuery(). Is this correct ?

Ryobi is offline   Reply With Quote
Old 06-08-2018, 01:36 AM   #4
jleach
Newly Registered User
 
jleach's Avatar
 
Join Date: Jan 2012
Location: New York, NY
Posts: 302
Thanks: 16
Thanked 68 Times in 68 Posts
jleach will become famous soon enough
Re: Saving Data in VB Net

Quote:
So any changes made on the Text Box will not travel back to the da.fill. The changes will have be done via Cmd.ExecuteNonQuery(). Is this correct ?
That's correct. The DataAdapter just fills the DataSet, and does nothing about writing that data back to the db. You will have to handle that with Cmd.ExecuteNonQuery (or look into an ORM like EntityFramework, but it's best to learn this stuff well before moving on to an ORM).
__________________
- Jack D. Leach

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Microsoft MVP 2012-2016
jleach is offline   Reply With Quote
Old 06-08-2018, 03:05 PM   #5
Ryobi
Newly Registered User
 
Join Date: Mar 2012
Posts: 13
Thanks: 1
Thanked 0 Times in 0 Posts
Ryobi is on a distinguished road
Re: Saving Data in VB Net

Thank you for the information. I am kind of new the connection stuff and have question on that. Lets say that I open a connection to get the data and fill the dataset and I close the connection. I will have to open another connection when I update, delete or add information. I am just wondering wouldn't it make sense to only only one open when the form is loaded and one close when the form is unloaded since the transactions (Add, Delete Update) occur between the loading and uploading. The only thing would be if for some reason the form hangs then the connection would remain open and lock the database. Does this make sense ?
Ryobi is offline   Reply With Quote
Old 06-08-2018, 04:45 PM   #6
jleach
Newly Registered User
 
jleach's Avatar
 
Join Date: Jan 2012
Location: New York, NY
Posts: 302
Thanks: 16
Thanked 68 Times in 68 Posts
jleach will become famous soon enough
Re: Saving Data in VB Net

ADO.NET handles connection pooling internally on your behalf. The standard practice therefore is to open the connection only long enough you need to complete the operation you're doing.

Internally, the connection (or even a number of them) may well stay open, so you're not handshaking with the database server each time you "open" a connection from code.

There's is some management for this in how ADO.NET handles connection pooling, but it's really only needed in rare edge cases.

https://docs.microsoft.com/en-us/dot...ection-pooling

https://msdn.microsoft.com/en-us/lib...netbest_topic5

Cheers,
__________________
- Jack D. Leach

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Microsoft MVP 2012-2016
jleach is offline   Reply With Quote
Old 06-08-2018, 06:19 PM   #7
Ryobi
Newly Registered User
 
Join Date: Mar 2012
Posts: 13
Thanks: 1
Thanked 0 Times in 0 Posts
Ryobi is on a distinguished road
Re: Saving Data in VB Net

Your information has been very helpful and the puzzle of connections is become clear. Thank you for all your help.


Ryobi is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Data not saving to ID louisa Forms 3 02-19-2010 01:02 PM
Saving Data cosmarchy Forms 2 01-21-2010 08:21 AM
Data Not Saving mithani Forms 4 06-28-2007 05:06 PM
Passing data from main form to subform not saving data to table weilerdo Forms 0 03-02-2006 03:46 AM
Refreshing a data entry form after saving data astarbyfar Forms 3 07-06-2005 11:25 PM




All times are GMT -8. The time now is 07:55 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World