Solved

Problem Updating a Database

Posted on 2009-07-07
5
338 Views
Last Modified: 2013-12-25
I am having a problem updating information to a MS Access Database.

Before i code this much more, i simply have a link to a MS Access Database, a DataGridView, a TextBox, and a Button on Form1.

The database is linked and when running the program it pulls the columns from the database and populates the DataViewGrid. This DataViewGrid will not allow changes, i want the changes to occur in textboxs and that information then updated back to the database.

i am able to make the DataGridView change with the updated information from Textbox1, however that information is not being written back to the actual database and i can't seem to figure out why..

The code below is my source code for all of Form1, and Button1_Click.
Public Class Form1
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'CsvFileDS.CSVFILE' table. You can move, or remove it, as needed.
        Me.CSVFILETableAdapter.Fill(Me.CsvFileDS.CSVFILE)
 
    End Sub
 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim TempTable As DataTable = CsvFileDS.Tables("CSVFILE")
        Dim NewEntry As DataRow = TempTable.NewRow
 
        NewEntry("DOS") = TextBox1.Text
 
        TempTable.Rows.Add(NewEntry)
 
    End Sub
End Class

Open in new window

0
Comment
Question by:jokeefe1130
  • 3
  • 2
5 Comments
 
LVL 27

Expert Comment

by:Ark
ID: 24809179
Me.CSVFILETableAdapter.Update(YourDataSetNameHere)
0
 

Author Comment

by:jokeefe1130
ID: 24810181
OK, that worked and i was able to update the information in the database, but now i have another problem... "NullReferenceException was unhandled", "Object reference not set to an instance of an object"

        Dim TempTable As DataTable = Database1DataSet.Tables("CsvFile")
        Dim NewEntry As DataRow = TempTable.NewRow '<--ERROR IS HERE
 
        NewEntry("DOS") = "2009-07-08"
        NewEntry("LOS") = "BLS"
        NewEntry("PTNAME") = "DOE, JOHN"
 
        TempTable.Rows.Add(NewEntry)
 
        CsvTableTableAdapter.Update(Database1DataSet.CsvTable)

Open in new window

0
 
LVL 27

Accepted Solution

by:
Ark earned 500 total points
ID: 24810294
Try
Dim TempTable As DataTable = Database1DataSet.CSVFILE
I suggest using bindingsource instead of DataTables
'********Can be set in design time**********
dim bsCSVFILE As New BindingSource
bsCSVFILE.DataSource = Database1DataSet
bsCSVFILE.DataMember="CsvFile"
'*********************
       dim drv As DataRowView=bsCSVFILE..AddNew()
       drv("DOS") = "2009-07-08"
       drv("LOS") = "BLS"
       drv("PTNAME") = "DOE, JOHN"
bsCSVFILE.EndEdit
Me.CSVFILETableAdapter.Update(Database1DataSet.CsvTable)
0
 

Author Comment

by:jokeefe1130
ID: 24810609
Your Solution worked prefectly!

I will update the "Accept Solutions" shortly,

If you able to, i am also in need of a few other specifics with this same database.
1. Update an existing row/cell in the row
2. Delete the row

Your help is greatly appreciated, i havent worked with programming in many years and so much of the  syntax and funcations have changed. Last time i worked with programming, the database interface was "recordset..." VB '97
 
Thank you very much
0
 
LVL 27

Expert Comment

by:Ark
ID: 24811158
Hi
The .net database approach is slightly different then old VB. Then main idea is:
1. Connect to database, get info (TableAdapter(s).Fill), disconnect
2. Working with dataset (like database mirror) - adding new records, modifying, deleting in memory
3. Finally - connect to database again, update changes (tableAdapter(s).Update), disconnect.
    3.1 Dataset.GetChanges gives changed records
    3.2 Dataset.AcceptChanges marks those records as unchanged to repeat 2-3 steps

So, if using BindingSourse(s):
1. Connect to DB (Fill adapters)
2. set bindingsource(s) DataSource(=DataSet) and DatatMember(s)(=TableName(s))
3. Work wit bindingsourse(s)
   3.1. Navigate: bs.Position=bs.Find("LOS","BLS")
   3.2 Add New (see my previous post)
   3.3 Change Cells: bs("PTNAME")="whatever", bs("otherColumn") = "newString"
   3.4 Delete bs.DeleteCurrent (or bs.Delete(bs(bs.Find("LOS","BLS")))
   3.4. Accept (bs.EndEdit)  or discard (bs.CancelEdit) in-memory changes
4. Update DataBase (TableAdapter.Update)
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Macro Excel - Multiple If conditions 2 71
Added a column screws up code 5 57
Update in Sql 7 37
converting visio 2010 to powerpoint 2010  - formatting issues 5 68
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

813 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

10 Experts available now in Live!

Get 1:1 Help Now