none
Visual Basic edit form help? RRS feed

  • Question

  • I was able to program an add form in visual basic. 

    I'm trying to use a similar method/logic to edit items and save it back to a Microsoft access database. 

    Please find my subroutine that tries to edit here: 

    #Region " Edit to DB "
        Public Sub EditSelected(ByVal row As DataRow)
            Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                          UPDATE Table1
                              (PRODID ,
                                TS,
                               Material,
                               Directory 
                              ) 
    
                            SET
                               
                            (   @PRODID ,
                                @TS,
                                @Material,
                                @Directory
                            )
                        WHERE PRODID = @PRODID
                        </SQL>.Value
                    cmd.Parameters.AddWithValue("@PRODID", row.Field(Of Integer)("PRODID"))
                    cmd.Parameters.AddWithValue("@Material", row.Field(Of String)("Material"))
                    cmd.Parameters.AddWithValue("@TS", row.Field(Of String)("TS"))
                    cmd.Parameters.AddWithValue("@Directory", row.Field(Of String)("Directory"))
    
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    '
                    ' Get new identifier which is an auto-incrementing field/primary key
                    '
                    cmd.CommandText = "Select @@Identity"
                    row.SetField(Of Integer)("ID", CInt(cmd.ExecuteScalar))
                End Using
            End Using
    
    
        End Sub
    
    #End Region

    the way I invoke the edit form has the user select a row in data grid view which shows the edit button.

    User clicks on the edit button to show the edit form. 

    I've been able to pass the data from the main form to edit form successfully. 

    THE ISSUE:

    It's pretty obvious that if I use INSERT INTO instead of UPDATE this code creates a new entry with PRODID 1 (assuming the user selected id 1 on main form data grid view) instead of overwriting the old product id.

    I know I have to use the UPDATE command but I'm not sure of the syntax.  I'd like some help with that.

    If anything isn't clear please let me know

    CP



    Wednesday, July 26, 2017 9:56 PM

All replies

  • What you show is why there is created ORM ("Object Related Mapping"). We can discuss what is ORM and what not. But using CRUD (Create, Read, Update and Delete) you are sure your solution is weak. What you try to do is almost impossible with a real database in a multi user environment. 

    Start with using a simple ORM to learn that. A good sample about that is the TableAdapterManager (although it has issues as well). 

    https://msdn.microsoft.com/en-us/library/bb384426.aspx

    Something related to that is bound presentation. 


    Success
    Cor

    Wednesday, July 26, 2017 10:04 PM
  • Could you please elaborate on what you mean by:

    "But using CRUD (Create, Read, Update and Delete) you are sure your solution is weak. "

    Wednesday, July 26, 2017 10:10 PM
  • Here are the basics for SELECT, UPDATE, ADD using a class which has try/catch statements where if there is an error HasErrors is set to true, if a method call fails the caller checked HasErrors, if true then the error is in ErrorMessage property of the class.

    In regards to adding a new record, you don't touch the identifier as you most likely know but instead the primary key (identifier) is generated for you assuming you set the primary key to auto-increment. So on each add the primary key is set to one greater than the last primary key value. The new key is returned in the parameter Identifier where you pass in a integer value e.g. Dim newId As Integer = 0 then for the AddNewRow("Karen","pass",newId)

    The class is a code sample I use for answering similar questions so I'm using a different table. 

    Imports System.Data.OleDb
    
    Public Class Operations
    
        ''' <summary>
        ''' Creates our connection string to the database which is easy to follow
        ''' and there is no string concatenation done here
        ''' </summary>
        ''' <remarks></remarks>
        Private Builder As New OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "names.accdb")
            }
    
    
        Public Property HasErrors As Boolean
        Public Property ErrorMessage As String
        Public Function GetAll() As DataTable
            Dim dt As New DataTable
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDbCommand("", cn)
                    cmd.CommandText = "SELECT id,UserName,UserPassword FROM Users"
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                End Using
            End Using
            Return dt
        End Function
        Public Function UpdateRow(ByVal UserName As String, ByVal UserPassword As String, ByVal Identfier As Integer) As Boolean
            Dim Success As Boolean = True
            Try
                Using cn As New OleDbConnection(Builder.ConnectionString)
                    Using cmd As New OleDbCommand("", cn)
    
                        cmd.CommandText =
                            <SQL>
                                UPDATE 
                                    users 
                                SET 
                                    UserName = @UserName, 
                                    UserPassword = @UserPassword 
                                    WHERE id = @Identifier
                            </SQL>.Value
    
                        cmd.Parameters.AddWithValue("@UserName", UserName)
                        cmd.Parameters.AddWithValue("@UserPassword", UserPassword)
                        cmd.Parameters.AddWithValue("@Identfier", Identfier)
    
                        cn.Open()
    
                        Success = cmd.ExecuteNonQuery() = 1
    
                    End Using
                End Using
    
            Catch ex As Exception
                HasErrors = True
                ErrorMessage = ex.Message
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function AddNewRow(ByVal UserName As String, ByVal UserPassword As String, ByRef Identfier As Integer) As Boolean
            Dim Success As Boolean = True
    
            Try
                Using cn As New OleDbConnection(Builder.ConnectionString)
                    Using cmd As New OleDbCommand("", cn)
    
                        cmd.CommandText = "INSERT INTO users (UserName,UserPassword) Values (@UserName,@UserPassword)"
    
                        cmd.Parameters.AddWithValue("@UserName", UserName.Trim)
                        cmd.Parameters.AddWithValue("@UserPassword", UserPassword.Trim)
    
                        cn.Open()
    
                        cmd.ExecuteNonQuery()
    
                        cmd.CommandText = "Select @@Identity"
                        Identfier = CInt(cmd.ExecuteScalar)
    
                    End Using
                End Using
    
            Catch ex As Exception
                HasErrors = True
                ErrorMessage = ex.Message
                Success = False
            End Try
    
            Return Success
    
        End Function
    End Class


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, July 26, 2017 10:28 PM
    Moderator
  • Could you please elaborate on what you mean by:

    "But using CRUD (Create, Read, Update and Delete) you are sure your solution is weak. "

    CRUD was the first way of using a Database. It was just one step after using Random Files with which you were proud you could do it with one Entity. Currently we use many entities in our databases and if it is about Internet endless users can use it.

    That does not fit anymore if one creates on Internet an order, you have to create orderlines and stock in it. But in the same time others can do the same thing in about the same time. You have to create a mechanism which cannot be done by one person. But if it is about a team then you have to communicate so much that with CRUD you are sure somebody else is adding a field while somebody else is removing that.

    Take a look at this one just to get an idea. It is not the only one.

    https://msdn.microsoft.com/en-us/library/aa937723(v=vs.113).aspx

    Of course I've also done it first using Crud and I can still do that. But after being busy with that after 2 tables makes that the fun is gone. With ORM software it takes minutes where Crud takes a days. 

    If you see what Karen shows, she has not yet made the delete, she shows something that with ORM software takes probably  5 lines for such an easy table. 

    And yes many code lines do never make solutions strong.


    Success
    Cor


    Wednesday, July 26, 2017 11:57 PM
  • Hi creepyspirals,

    Here is the sample covers that "How to Insert, Update, Delete and Search Values to/from MS Access database, please refer to:

    https://www.codeproject.com/Articles/26801/Insert-Update-Delete-Search-Values-in-MS-Access

    By the way, if you create table in access database and the default Id field is auto-increment. but you can also increase manually.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 27, 2017 7:03 AM
    Moderator