Saving Data in VB Net

[Ryobi]

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.

 

[jleach]

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

cmd.ExecuteNonQuery()

, which is used to execute a statement rather than Selecting, which is what you're doing with

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

 

[Ryobi]

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 ?

 

[jleach]

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).

 

[Ryobi]

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 ?

 

[jleach]

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/dotnet/framework/data/adonet/sql-server-connection-pooling

https://msdn.microsoft.com/en-us/library/ms971481.aspx#adonetbest_topic5

Cheers,

 

[Ryobi]

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

 

[vedika]

You may try this code to save data to VB.Net

Imports System.Data.OleDb
Public Class Form1
    Dim cnn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\ching13\Desktop\watta2.accdb"


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Using dbConnection = New OleDbConnection(cnn)
        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter
        dbConnection.Open()
        Dim cmd As New OleDbCommand
        cmd.Connection = dbConnection
        cmd.CommandText = "INSERT INTO Log-In( Username, [Password] ) VALUES (@user, @pass)"

        cmd.Parameters.Add(New OleDbParameter("@user", DbType.String))
        cmd.Parameters.Add(New OleDbParameter("@pass", DbType.String))

        cmd.Prepare()


        cmd.Parameters("@user").Value = TextBox1.Text
        cmd.Parameters("@pass").Value = TextBox2.Text

        cmd.ExecuteNonQuery()
        dbConnection.Close()


        MessageBox.Show("Data Saved")
        TextBox1.Text = ""
        TextBox2.Text = ""

    End Using
End Sub
End Class

 

[Ryobi]

Thank for the feed back. Your sample looks interesting. I will give give a try and the see if I can modify it to suit my purpose. You have two fields, but if I can get it to work I should be able to use other fields.

 

[Ryobi]

Veka,


I have tested your code and it worked. There were some were minor changes that I had to do. The statement Dim cmd as New OleDBcommand needed () at the end of the statement. I had to remark the cmd.Prepare() statement. For some reason it does now want to work in with visual basic Studio 2017. Thank you for your help.








Dim cmd As New OleDbCommand