Post #9 : Programming " CRUD Part 3 - How to Update data in SQL Server using VB.net with sample project"

Hi. Jack


Now we will try to  change the data that we have input, we will work with one button that we have made in the first trial here.

we will combine save and update command in one button.


the concept is, when the save button is click the first thing to do is do checking for code entered, if the code is not yet registered then do as the new entry, otherwise if the code already registered we do is save the data changes.

to check whether the code is already registered or not in the database use the script as below.

the script will generate integer 1 if the code entered is already registered and 0 if the code entered is not registered.

code :

Private Function is_code_exist() As Integer
        Dim value As Integer = 0
        If Not tcode.Text = String.Empty Then
            'call connection string
            Using iKon = New SqlConnection(server_connection.connection_string)
                If iKon.State = ConnectionState.Open Then iKon.Close()
                iKon.Open()
                Try
                    'in this line you'll create command to insert the date into database using parameter @
                    Using iCmd = New SqlCommand("select * from cars where code = @code", iKon)
                        'declare sqlparameter and fillit with object (textbox,combobox, or data collection like list(of t)
                        iCmd.Parameters.Add(New SqlParameter("@code", tcode.Text))
                        'read data from database using reader
                        Using reader As SqlDataReader = iCmd.ExecuteReader
                            ' if reader has row, result not empty
                            If reader.HasRows = True Then
                                'if code is exist return 1 or true
                                value = 1
                            Else
                                'if code is exist return 0 or false
                                value = 0
                            End If
                        End Using
                    End Using
                Catch ex As Exception
                    MsgBox("Error " & ex.Message, MsgBoxStyle.Critical)
                End Try
            End Using
        End If
        Return value
    End Function

and this is a script to update the data

code:

Private Sub do_update()
        Using ikon = New SqlConnection(server_connection.connection_string)
            'check connection string, if connection string already open then close it
            If ikon.State = ConnectionState.Open Then ikon.Close()
            'open new connection
            ikon.Open()
            'using sql transaction 
            Dim trans = ikon.BeginTransaction
            Try
                'in this line you'll create command to insert the date into database using parameter @
                Using iCmd = New SqlCommand("update cars set car_merk = @merk, car_type = @type, car_year = @year where code = @code", ikon)
                    'declare transaction for this sql command 
                    iCmd.Transaction = trans
                    'declare sqlparameter and fillit with object (textbox,combobox, or data collection like list(of t)
                    With iCmd.Parameters
                        .Add(New SqlParameter("@code", tcode.Text))
                        .Add(New SqlParameter("@merk", tmerek.Text))
                        .Add(New SqlParameter("@type", ttype.Text))
                        .Add(New SqlParameter("@year", tyear.Text))
                    End With
                    iCmd.ExecuteNonQuery()
                End Using
                'if all command has been transfered to server than commit it
                trans.Commit()
            Catch ex As Exception
                'if found error, cancel all transaction
                trans.Rollback()
                'give error message
                MsgBox("Error " & ex.Message, MsgBoxStyle.Critical)
            End Try
        End Using
    End Sub

and here code to execute, validate code and decision to insert (entry new data) or update (save changes of data)

Code :

Private Sub Btn_save_Click(sender As Object, e As EventArgs) Handles Btn_save.Click
        'first of all check the existence of the code that we input.
        If is_code_exist() = 1 Then
            'if code exist then update
            do_update()
            MsgBox("Save Update Succeed")
        Else
            'if code not exist then insert
            save()
            MsgBox("Save New Entry Succeed")
        End If
    End Sub

for script save (new entry) you can found in here.

CONGRATULATIONS!!!

read to :

Post #7 : Programming " CRUD Part 1 - How to Insert or Create data in SQL Server using VB.net"

Post #8 : Programming " CRUD Part 2 - How to Select and Find data in SQL Server using VB.net"


Sample Project : Download Here