Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

Saving database changes

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
lincstech
Asked:
lincstech
1 Solution
 
PatHartmanCommented:
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
 
lincstechAuthor 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.
0
 
CodeCruiserCommented:
@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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
lincstechAuthor 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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
lincstechAuthor Commented:
@Scott McDaniel  can that be achieved if the textboxes are on another form ? please take a look at the project I attached.
0
 
lincstechAuthor 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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
@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
 
lincstechAuthor Commented:
@Scott McDaniel could you attach a small project example for me ?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
lincstechAuthor 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

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
lincstechAuthor Commented:
Done that aswell and the changes still don't seems to be saving :(
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
lincstechAuthor Commented:
Just before I do that Is their a difference between Access  and SQl server in that code ? I'm using Access
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now