CODEWITHSUNDEEP
vb.netms-access
HarleyCode
HarleyCode

Reputation: 47

How to add a record to a database VB.NET, using datasets?

I have a Microsoft Access database containing three tables - "Class 1", "Class 2" and "Class 3". In each table, there are three columns: "ID", "Name" and "Score". In Visual Basic, I have two variables - name and score. I would like to add the name and score on a new row in the database, using datasets. I looked at the MSDN tutorial, but I don't really understand it:

https://msdn.microsoft.com/en-us/library/5ycd1034.aspx

Dim newCustomersRow As NorthwindDataSet.CustomersRow
newCustomersRow = NorthwindDataSet1.Customers.NewCustomersRow()
newCustomersRow.CustomerID = "ALFKI"
newCustomersRow.CompanyName = "Alfreds Futterkiste"
NorthwindDataSet1.Customers.Rows.Add(newCustomersRow)

Where does the "NorthwindDataSet1" come from? I understand where "NorthwinDataSet" comes from = that is the name of the data set created via the data source tab.

I am not sure how to add the record to the table. I started like this:

Dim newScoreRow As scoresDataSet.Class_1Row

I am not sure how to write the next line of code because I don't understand the second line of code in the MSDN documentation.

After that second line of code has been written, I understand that I write something like:

newScoreRow.Name = name
newScoreRow.Score = Str(Score)

In summary, where does the "NorthwindDataSet1" come from? How would I finish my code?

Upvotes: 1

Views: 2414

Answers (1)

вʀaᴎᴅᴏƞ вєнᴎєƞ
вʀaᴎᴅᴏƞ вєнᴎєƞ

Reputation: 1678

NorthwindDataSet1 is an instance of a Typed DataSet called NorthWindDataSet. Take a look at these MSDN Pages Working with Datasets in Visual Studio and How to: Create a Typed Dataset

Following the second link I posted above I created a new DataSet called scoresDataSet and I added a table called Class_1 and under the table I created the columns ID, Name, and Scorejust like you had in your question.

The Typed DataSet Class file that was generated for me is way to large to post here on SO, but here is the code that I used to add a new row to the Class_1 table in the DataSet. The addNewRowButton is just a button on the form. I also added two textboxes on the form called nameTextBox and scoreTextbox.

Private ScoresDataSet1 As New scoresDataSet()

Private Sub addNewRowButton_Click(sender As Object, e As EventArgs) Handles addNewRowButton.Click
    Dim name As String = nameTextBox.Text
    Dim score As String = scoreTextbox.Text

    Dim newScoreRow As scoresDataSet.Class_1Row
    newScoreRow = ScoresDataSet1.Class_1.NewClass_1Row

    newScoreRow.Name = name
    newScoreRow.Score = score

    ScoresDataSet1.Class_1.Rows.Add(newScoreRow)

End Sub

The trick here is that your data actually exists in an Access Database so rather than manually creating an DataSet like I did, you'll want to Add a Data Source to your project using the Data Source Configuration Wizard and choosing Microsoft Access Database File.

In visual Studio 2012, just go to:

  1. Project
  2. Add New Data Source...
  3. Database
  4. Dataset
  5. New Connection...
  6. Change...

And choose the appropriate option and fill out the other fields in those windows. Once you've completed that and you build your project you should have a new DataSet object and associated TableAdapters in the toolbox under your application's Components from the form designer.

Once you add a copy of your newly created DataSet and associated TableAadapter to your form you can use the TableAdapters to populate data into your dataset from the access database and you can use the TableAdapters to store data into your access database.

Upvotes: 2

Related Questions