Solved

Saving database changes

Posted on 2014-03-06
16
272 Views
Last Modified: 2014-03-11
Hello Experts,

I’m in need of some help with a small project which I’m working on. I have two form “Form1” contains a datagridview, form2 contains three text boxes. I’m using currently, a Microsoft Access database, the issue or area which I am stuck on is when the contents of the datagrid is displayed on form2 I have no way to save any changes back to the database.

To help understand more I have attached a small test project.

Project.zip
0
Comment
Question by:lincstech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 38

Expert Comment

by:PatHartman
ID: 39909396
I can't download the example at this time.  Why are you using a non-native control?  What is the data grid doing for you that you cannot do with a subform?  Have you posted this question on the FAQ site of the grid control manufacturer?
0
 

Author Comment

by:lincstech
ID: 39909528
Well, The datagrid displays the information in the database table. When you click on the record and click the display button it displays the entire contents into the relevant text boxes on a separate form.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39909946
@PayHartman,
Grid control manufacturer is Microsoft as it is the built in grid in .NET Winforms.


>when the contents of the datagrid is displayed on form2 I have no way to save any changes back to the database.

You said Form2 has 3 textboxes. Where did the grid come from? If you meant Form1, here is a tutorial

http://www.mindstick.com/Articles/30148105-6777-467a-9ecc-82a2118387d0/?Insert%20Update%20Delete%20Records%20in%20CSharp%20.NET
0
Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

 

Author Comment

by:lincstech
ID: 39910060
The issue I'm having is saving the changes. Im open to any suggestions on other methods to achieve what has been demonstrated in the attached project.
0
 
LVL 85
ID: 39911006
I usually just do this:

Dim con As New OleDbConnection
con.ConnectionString = "blah"
con.Open

Using cmd As New OleDbCommand("Update Table1 SET Field1=" & Textbox1.Text & " AND Field2=" & Textbox2.Text & " WHERE YourIDField=" & TheRecordIDField
  cmd.ExecuteNonQuery
End Using

con.close

It's quick and easy, and as long as you're comfortable working directly with SQL and such, it's relatively painless. Note you'd have to delimit the data appropriately, depending on the datatype of the various columns. For Text fields, you would use single quotes, and for Dates you use the hashmark, so if Field1 is a Text field and Field2 is a Date field:

Using cmd As New OleDbCommand("Update Table1 SET Field1='" & Textbox1.Text & "' AND Field2=#" & Textbox2.Text & "# WHERE YourIDField=" & TheRecordIDField
  cmd.ExecuteNonQuery
End Using
0
 

Author Comment

by:lincstech
ID: 39912062
@Scott McDaniel  can that be achieved if the textboxes are on another form ? please take a look at the project I attached.
0
 

Author Comment

by:lincstech
ID: 39914399
So this is what I’m having a problem with. I have two forms Form1 and Form2

Form1
form 1 With the following code

Imports System.Data.OleDb
Public Class Form1
    Dim con As New OleDbConnection
    Dim ds As New DataSet
    Dim dt As New DataTable
    Dim da As New OleDbDataAdapter
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Try
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database1.mdb"
            con.Open()
            ds.Tables.Add(dt)
            da = New OleDbDataAdapter("Select * From Table1", con)
            da.Fill(dt)
            DataGridView1.DataSource = dt.DefaultView
            con.Close()
        Catch ex As Exception
            MsgBox(Err.Description)
        End Try

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Form2.TextBox1.Text = Me.DataGridView1.SelectedRows(0).Cells(0).Value
        Form2.TextBox2.Text = Me.DataGridView1.SelectedRows(0).Cells(1).Value
        Form2.TextBox3.Text = Me.DataGridView1.SelectedRows(0).Cells(2).Value
        Form2.Show()

    End Sub
End Class

Open in new window



As you can see when button 1 is clicked the contents from form1's datagridview is displayed in three text boxes on form2 displaying the three cells.

Form2

As you can see their is a save button on that page, this is where the problem is how do I save any changes on form2 when the data is populated from form 1's datagrid ?

I thought of maybe using the textbox leave function and reverse the textbox data back to the datagridview but I need to be able to save the changes using the save button on form2
0
 
LVL 85
ID: 39914680
@Scott McDaniel  can that be achieved if the textboxes are on another form ? please take a look at the project I attached.
Yes. The location of the data is irrelevant. You just gather that data, create a valid SQL UPDATE statement, and fire that against your database. As I mentioned in my comment, you'd open an OleDb connection, then an OldDBCommand, and set the CommandText, and then issue ExecuteNonQuery.
0
 

Author Comment

by:lincstech
ID: 39914716
@Scott McDaniel could you attach a small project example for me ?
0
 
LVL 85
ID: 39915684
The code I posted above is what the project would be.

Essentially, you create a connection to the Access database and then issue an UPDATE statement with that connection.
0
 

Author Comment

by:lincstech
ID: 39915918
Could you just check this code. It doesn't produce an error however, it doesn't seems to save any changes back to the database

   Dim con As New OleDbConnection
        Try
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database1.mdb"
            con.Open()

            Using cmd As New OleDbCommand("Update Table1 set F_Name='" & TextBox2.Text & "' And L_Name='" & TextBox3.Text & "' where ID='" & TextBox1.Text & "'", con)
                cmd.ExecuteNonQuery()
            End Using
            con.Close()

            MsgBox("Saved")
        Catch ex As Exception
            MsgBox(Err.Description)
        Finally
            con.Close()
        End Try

Open in new window

0
 
LVL 85
ID: 39916061
Looks like it should work, assuming your Data Source is correct. Generally that would be a fully qualified path to the database, like "C:\SomeFolder\MyDatabase.mdb"
0
 

Author Comment

by:lincstech
ID: 39916104
Done that aswell and the changes still don't seems to be saving :(
0
 
LVL 85
ID: 39916527
Can you show the actual SQL that you're passing into the command?

Sounds like either (a) you have invalid SQL or (b) you're not connected to the right database.
0
 

Author Comment

by:lincstech
ID: 39917085
Just before I do that Is their a difference between Access  and SQl server in that code ? I'm using Access
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39917431
I missed this:

"Update Table1 set F_Name='" & TextBox2.Text & "' And L_Name='" & TextBox3.Text & "' where ID='" & TextBox1.Text & "'"

That should be:

"Update Table1 set F_Name='" & TextBox2.Text & "', L_Name='" & TextBox3.Text & "' where ID='" & TextBox1.Text & "'"
0

Featured Post

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

632 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