none
Need Update function with sql conn just for ID and Name

    Question

  • We insert some data in sql using vb.net and show that data in grid view  .Now i want to add more data and use update function and update the data then show that data in grid view
    Tuesday, January 2, 2018 9:53 AM

All replies

  • Hello,

    There are various ways to work with data e.g. via a TableAdapter/DataSet/BindingSource, using a DataAdapter, using Entity Framework or simply using SqlClient connection and command objects. Since you have not mentioned which one I will show an example using SqlClient which is my recommended method (then Entity Framework once you understand how to work with data).

    In this example I created a class, in your form, you create an instance of this class e.g.

    Dim Ops As New DataOperations

    Then call a method e.g.

    If Ops.UpdateRow(....

    Or 

    If Ops.AddNewCustromer(...

    How the data is obtained, could be from controls such as TextBox, ComboBox etc or a DataGridView, does not matter. If from a DataGridView you have setup with a DataTable and need to know about newly added or updated rows there are events for a DataTable to get changes (see my code sample in regards to getting changed data in DataTable objects). In short I can't list everything here as you didn't include enough information and for that matter I may be travelling down the wrong path right now but thought it prudent to provide this information.

    Code is suitable for VS2015 or VS2017, VS2013 should work too.

    Imports System.Data.SqlClient
    
    Public Class DataOperations
        Private mConnectionString As String = "Your connection string goes here"
        Private InsertStatement As String =
            <SQL>
                INSERT INTO Customer 
                (CompanyName,ContactName,ContactTitle) 
                VALUES (@CompanyName,@ContactName,@ContactTitle); 
                SELECT CAST(scope_identity() AS int);
            </SQL>.Value
    
        Private UpdateStatement As String =
            <SQL>
                UPDATE Customer 
                SET CompanyName = @CompanyName, 
                ContactName = @ContactName,
                ContactTitle = @ContactTitle 
                WHERE Identifier = @Identifier
            </SQL>.Value
    
        ''' <summary>
        ''' Add new record, last parameter when this function returns true
        ''' will contain the new key for the newly added record.
        ''' </summary>
        ''' <param name="CompanyName"></param>
        ''' <param name="ContactName"></param>
        ''' <param name="ContactTitle"></param>
        ''' <param name="NewIdentifier"></param>
        ''' <returns></returns>
        Public Function AddNewCustomer(
            ByVal CompanyName As String,
            ByVal ContactName As String,
            ByVal ContactTitle As String,
            ByRef NewIdentifier As Integer) As Boolean
    
            Using cn As New SqlConnection With {.ConnectionString = mConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = InsertStatement
                    cmd.Parameters.AddWithValue("@CompanyName", CompanyName)
                    cmd.Parameters.AddWithValue("@ContactName", ContactTitle)
                    cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle)
                    cn.Open()
                    Try
                        NewIdentifier = CInt(cmd.ExecuteScalar)
                        Return True
                    Catch ex As Exception
                        Return False
                    End Try
                End Using
            End Using
        End Function
        ''' <summary>
        ''' Pass in an existing DataRow which includes the primary key which
        ''' is used in the WHERE condition to find and update the record
        ''' </summary>
        ''' <param name="row"></param>
        ''' <returns></returns>
        ''' <remarks>
        ''' If you like to use parameters rather than a DataRow e.g.
        ''' UpdateRow(ByVal pCompanyName As String etc you can do that too.
        ''' </remarks>
        Public Function UpdateRow(ByVal row As DataRow) As Boolean
            Dim Result As Boolean = False
            Using cn As New SqlConnection With {.ConnectionString = mConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = UpdateStatement
                    cmd.Parameters.AddWithValue("@CompanyName", row.Field(Of String)("CompanyName"))
                    cmd.Parameters.AddWithValue("@ContactName", row.Field(Of String)("ContactTitle"))
                    cmd.Parameters.AddWithValue("@ContactTitle", row.Field(Of String)("ContactTitle"))
                    cmd.Parameters.AddWithValue("@Identifier", row.Field(Of Integer)("Identifier"))
                    cn.Open()
                    Try
                        If CInt(cmd.ExecuteNonQuery) = 1 Then
                            Result = True
                        End If
                    Catch ex As Exception
                        Return False
                    End Try
                End Using
            End Using
            Return Result
        End Function
    
    End Class
    

    So in closing, I've given you a path to consider taking. And if using MS-Access, you would use similar code e.g.

    Public Class Sample2
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
        }
    
        Private mExceptiom As Exception
        ''' <summary>
        ''' Each method when executed, if there is an exception thrown
        ''' then mException is set and can be read back via Exception property
        ''' only when a method returns false.
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property Exception As Exception
            Get
                Return mExceptiom
            End Get
        End Property
        ''' <summary>
        ''' Container for data read in from a database table
        ''' </summary>
        ''' <returns></returns>
        Public Property CustomersDataTable As DataTable
        Public Function LoadCustomers() As Boolean
            If Not IO.File.Exists(Builder.DataSource) Then
                Return False
            End If
    
            Try
    
                CustomersDataTable = New DataTable
    
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "SELECT Identifier, CompanyName, ContactTitle FROM Customers"
    
                        cn.Open()
    
                        CustomersDataTable.Load(cmd.ExecuteReader)
                        CustomersDataTable.DefaultView.Sort = "CompanyName"
                        CustomersDataTable.Columns("Identifier").ColumnMapping = MappingType.Hidden
    
                    End Using
                End Using
    
                Return True
            Catch ex As Exception
                mExceptiom = ex
                Return False
            End Try
        End Function
        ''' <summary>
        ''' Delete a customer by their primary key
        ''' </summary>
        ''' <param name="CustomerId"></param>
        ''' <returns></returns>
        Public Function DeleteCustomer(ByVal CustomerId As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "DELETE FROM Customers WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@Identifier", CustomerId)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function UpdateCustomer(ByVal CustomerId As Integer, ByVal CompanyName As String, ByVal ContactName As String) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "UPDATE Customer SET CompanyName = @CompanyName, ContactName = @ContactName WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@CompanyName", CompanyName)
                        cmd.Parameters.AddWithValue("@ContactName", ContactName)
                        cmd.Parameters.AddWithValue("@Identifier", ContactName)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        ''' <summary>
        ''' Add new row, if successful provide the new record's primary key
        ''' </summary>
        ''' <param name="Name"></param>
        ''' <param name="ContactName"></param>
        ''' <param name="Identfier"></param>
        ''' <returns></returns>
        Public Function AddNewRow(ByVal Name As String, ByVal ContactName As String, ByRef Identfier As Integer) As Boolean
            Dim Success As Boolean = True
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "INSERT INTO Customers (CompanyName,ContactName) Values(@CompanyName,@ContactName)"
    
                        cmd.Parameters.AddWithValue("@CompanyName", Name)
                        cmd.Parameters.AddWithValue("@ContactName", ContactName)
    
                        cn.Open()
                        Dim Affected As Integer = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            cmd.CommandText = "Select @@Identity"
                            Identfier = CInt(cmd.ExecuteScalar)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
                mExceptiom = ex
            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

    Tuesday, January 2, 2018 10:58 AM
    Moderator
  • Hi Taaabi,

    According to your description, you use gridview, your project is asp.net or vb.net? If it is vb.net, i guess that you use datagridview, about inserting data in Sql server, you could take a look the following:

    https://stackoverflow.com/questions/12634516/how-can-i-insert-data-into-sql-server-using-vbnet

    About updating data, you can refer to:

    http://www.visual-basic-tutorials.com/database/update-data.php

    Then you want to reload these data in datagridview,

    http://www.dotnetheaven.com/article/how-to-load-data-from-database-into-datagridview-in-vb.net

    If your project is asp.net, you could need to go to asp.net forum to ask this question for professional help.

    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.

    Wednesday, January 3, 2018 3:18 AM
    Moderator