Solved

Saving database changes

Posted on 2014-03-06
16
265 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

708 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

12 Experts available now in Live!

Get 1:1 Help Now