saving and updating in one command button

[vbshad]

i have here separated code for inserting new data, and updating changes in existing data in access using vb.net....when i run the program, both run sucessfully...now, i want to use if statement to combine the code for inserting new and updating changes in the data in one command button 'save'..but when i run the program, the program reads the code for update only, not the saving code..it is encloses in the if statement...

Code for saving new data:

Private Sub menu_save_Click(sender As Object, e As EventArgs) Handles menu_save.Click

    access = "Insert into tbl_location(locid, locname, locadd) Values ('" & txt_locid.Text & "', '" & txt_locname.Text & "', '" & txt_locadd.Text & "')"

                        con.Open()
                        cmd = New OleDbCommand(access, con)
                        cmd.ExecuteReader(CommandBehavior.CloseConnection)
                        MsgBox("Successfully Saved....")
                        con.Close()

End Sub

Code for updating existing data:

Private Sub menu_update_Click(sender As Object, e As EventArgs) Handles menu_update.Click

access = "UPDATE tbl_location SET [locname] = '" & Me.txt_locname.Text & "', [locadd] ='" & Me.txt_locadd.Text & "' WHERE [locid] = '" & Me.txt_locid.Text & "'"

                    con.Open()
                    cmd = New OleDbCommand(access, con)
                    cmd.ExecuteNonQuery()
                    MsgBox("Successfully Updated...")
                    con.Close()

End Sub

When i combine the two, enclosing in an if statement:

Private Sub timer_save_Tick(sender As Object, e As EventArgs) Handles timer_save.Tick

        If Not Me.strip_progress.Value = Me.strip_progress.Maximum Then
            Me.strip_progress.Increment(5)
            Me.strip_progress.Visible = True
        Else
            If Me.strip_progress.Value = Me.strip_progress.Maximum Then
                Me.timer_save.Stop()
                Me.strip_progress.Value = 0

                If Me.txt_locid.Text & "" = "" Then
                    access = "Insert into tbl_location(locid, locname, locadd) Values ('" & txt_locid.Text & "', '" & txt_locname.Text & "', '" & txt_locadd.Text & "')"

                    con.Open()
                    cmd = New OleDbCommand(access, con)
                    cmd.ExecuteReader(CommandBehavior.CloseConnection)
                    MsgBox("Successfully Saved....")
                    con.Close()

                    Me.menu_update.Enabled = False
                    Me.menu_save.Enabled = False

                    Me.txt_locid.Enabled = False
                    Me.txt_locname.Enabled = False
                    Me.txt_locadd.Enabled = False

                    Me.strip_progress.Visible = False
                Else
                    access = "UPDATE tbl_location SET [locname] = '" & Me.txt_locname.Text & "', [locadd] ='" & Me.txt_locadd.Text & "' WHERE [locid] = '" & Me.txt_locid.Text & "'"

                    con.Open()
                    cmd = New OleDbCommand(access, con)
                    cmd.ExecuteNonQuery()
                    MsgBox("Successfully Updated...")
                    con.Close()

                    Me.menu_update.Enabled = False
                    Me.menu_save.Enabled = False
                    Me.menu_new.Enabled = True
                    Me.menu_edit.Enabled = True
                    Me.menu_search.Enabled = True


                    Me.txt_locname.Enabled = False
                    Me.txt_locadd.Enabled = False

                End If
            End If
        End If

    End Sub

Can anyone help me to this problem??thank you in advance...

[Begginnerdev]

You will first need to return the ID of the inserted row and store that in a variable.

Next you will need to issue the update the row using that variable.

For example:

Private Sub InsertAndUpDate()
    Dim con As New OleDBConnection("MyConnectionStringHere")
    Try
        con.Open()
        Dim cmd As OleDBCommand("INSERT INTO tblTest(Col1,Col2) OUTPUT Inserted.MyID VALUES ('Col1 vals', Col2 vals')",con)
        Dim iInsertedID  As Integer = - 1
        iInsertedID = Cint(cmd.ExecuteScalar)

        If iInsertedID > -1 Then

            cmd.CommandText = "UPDATE tblTest SET Col1='MYVAL 1' Col2='MYVAL 2' WHERE MyID=" & iInsertedID

            cmd.ExecuteNonQuery()

            MsgBox("Record inserted and updated!")
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    Finally
        con.Close()
    End Try
End Sub

[Reverend Jim]

You do the INSERT when there is no value entered for locid. Is this really what you want? Also, why are you using ExecuteReader for the INSERT? Use ExecuteNonQuery instead. You use ExecuteScalar when you are returning one value or ExecuteReader when you are returning more than one value. In your case you are not returning any values.

[vbshad]

I already get the logic on how to use only one command button for saving and updating...here's the code...i hope it can help to others... :)

Private Sub timer_save_Tick(sender As Object, e As EventArgs) Handles timer_save.Tick

        Dim check As Integer

        If Not Me.strip_progress.Value = Me.strip_progress.Maximum Then
            Me.strip_progress.Increment(5)
            Me.strip_progress.Visible = True
        Else
            If Me.strip_progress.Value = Me.strip_progress.Maximum Then
                Me.timer_save.Stop()
                Me.strip_progress.Value = 0


                access = "Select * From tbl_location Where locid='" & Trim(txt_locid.Text) & "'"
                con.Open()
                cmd = New OleDbCommand(access, con)
                dtr = cmd.ExecuteReader()

                If dtr.HasRows Then
                    access = "UPDATE tbl_location SET [locname] = '" & Trim(Me.txt_locname.Text) & "', [locadd] ='" & Trim(Me.txt_locadd.Text) & "' WHERE [locid] = '" & Trim(Me.txt_locid.Text) & "'"

                    cmd = New OleDbCommand(access, con)

                    check = cmd.ExecuteReader.RecordsAffected()
                    con.Close()
                    Me.strip_progress.Visible = False
                    If check > 0 Then
                        MsgBox("Successfully updated data from " & Trim(txt_locid.Text) & "")
                    Else
                        MsgBox("Updating Failed....")
                    End If

                Else
                    If con.State = ConnectionState.Closed Then
                        con.Open()

                    End If
                    access = "Insert into tbl_location(locid, locname, locadd) Values ('" & txt_locid.Text & "', '" & txt_locname.Text & "', '" & txt_locadd.Text & "')"

                    cmd = New OleDbCommand(access, con)
                    cmd.ExecuteNonQuery()
                    MsgBox("Successfully Saved....")
                    con.Close()
                    Me.strip_progress.Visible = False

                End If
            End If
        End If

        Me.menu_save.Enabled = False
        Me.menu_new.Enabled = True
        Me.menu_edit.Enabled = True
        Me.menu_search.Enabled = True

        Me.txt_locid.Enabled = False
        Me.txt_locname.Enabled = False
        Me.txt_locadd.Enabled = False

    End Sub

[vbshad]

thank you Reverend Jim and Beginnerdev for suggesting and answering.. :)

[Mr.M]

If your question has been answered please mark it answered. Thank you.