Solved

Saving database changes

Posted on 2014-03-06
16
268 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
16 Comments
 
LVL 34

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
 

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 84
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 84
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:lincstech
ID: 39914716
@Scott McDaniel could you attach a small project example for me ?
0
 
LVL 84
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 84
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 84
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 84

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

911 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

18 Experts available now in Live!

Get 1:1 Help Now