Saving database changes

lincstech
lincstech used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
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?

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
@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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
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

Author

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

Author

Commented:
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
Most Valuable Expert 2012
Top Expert 2014

Commented:
@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.

Author

Commented:
@Scott McDaniel could you attach a small project example for me ?
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
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

Most Valuable Expert 2012
Top Expert 2014

Commented:
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"

Author

Commented:
Done that aswell and the changes still don't seems to be saving :(
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
Just before I do that Is their a difference between Access  and SQl server in that code ? I'm using Access
Most Valuable Expert 2012
Top Expert 2014
Commented:
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 & "'"

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial