Results 1 to 8 of 8

Thread: Access Database field not getting updated, no error?

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    28

    Access Database field not getting updated, no error?

    I am inserting a value into a field of a table and if same customer already exists in the table then update that field instead of having duplicate entries.
    Data being inserted successfully at first entry but not updating after more entries. Table only contains first inserted data.
    This is my code. I think problem would be in my update method but i couldn't figure out.

    Code:
      Public Sub checkClient(ByVal cName As String, ByVal total1 As String, ByVal total2 As String)
    
            Using connCheck As New OleDbConnection(myGlobals.getDBPath)
    
                connCheck.Open()
    
                Using cmdCheck As New OleDbCommand("SELECT * FROM " & bookTable & " WHERE CustomerName=@Name", connCheck)
    
                    cmdCheck.Parameters.AddWithValue("@Name", cName)
    
                    Dim reader As OleDbDataReader = cmdCheck.ExecuteReader
    
                    If reader.Read = True Then
    
                        updateClientBook(cName, total1, total2)
    
                        MsgBox("updated " + cName + " pos1: " + total1 + "pos2: " + total2)
    
                    Else
    
                        insertClientBook(cName, total1, total2)
    
                    End If
    
                End Using
    
            End Using
    
    
        End Sub
    
        Public Sub insertClientBook(ByVal cName As String, ByVal total1 As Integer, ByVal total2 As Integer)
    
            Using connInsert As New OleDbConnection(myGlobals.getDBPath)
    
                connInsert.Open()
    
                Using cmdInsert As New OleDbCommand("INSERT INTO " & bookTable & " (CustomerName, posTeam1, posTeam2) VALUES (@Name, @p1, @p2)", connInsert)
    
                    cmdInsert.Parameters.AddWithValue("@Name", cName)
    
                    cmdInsert.Parameters.AddWithValue("@p1", total1)
    
                    cmdInsert.Parameters.AddWithValue("@p2", total2)
    
                    cmdInsert.ExecuteNonQuery()
    
                End Using
    
            End Using
        End Sub
    
    
    
        Public Sub updateClientBook(ByVal cName As String, ByVal total1 As Integer, ByVal total2 As Integer)
    
            Using conn As New OleDbConnection(myGlobals.getDBPath)
    
                Using cmd As New OleDbCommand("UPDATE " & bookTable & " SET posTeam1 = @p1, posTeam2 = @p2 WHERE CustomerName = @Name", conn)
    
                    conn.Open()
    
                    cmd.Parameters.AddWithValue("@Name", cName)
    
                    cmd.Parameters.AddWithValue("@p1", total1)
    
                    cmd.Parameters.AddWithValue("@p2", total2)
    
                    cmd.ExecuteNonQuery()
    
                End Using
    
            End Using
    
        End Sub

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Access Database field not getting updated, no error?

    You can do that faster, but that's not the issue, so I'll just mention it.

    The UpdateNonQuery method returns an integer, which is the number of rows affected. The first thing to do is to get that return and have a look at it. If it is returning 0, then you know that no rows match the criteria. I think you'd be pretty surprised by that, but you have to start somewhere. After all, what if it returned 1? In that case, then the DB IS being updated, so you'd have to ask why you aren't seeing it. Thus you see, whether it returns 0 or 1, you have a pretty good mystery either way, so that's the first thing to look at. You need to know what mystery you are really trying to solve.
    My usual boring signature: Nothing

  3. #3
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Access Database field not getting updated, no error?

    Binding is your friend, he wants to help you.

    Code:
    Imports System.Data.OleDb
    Public Class AccessInsertUpdate
        Dim dt As New DataTable
        Dim bs As New BindingSource
        Private Sub AccessInsertUpdate_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                            Data Source=C:\DATA\Access\AccessDB.accdb;")
                Using DA As New OleDbDataAdapter("SELECT * FROM Customers", conn)
                    DA.MissingSchemaAction = MissingSchemaAction.AddWithKey
                    DA.FillSchema(dt, SchemaType.Source)
                    DA.Fill(dt)
                    bs.DataSource = dt
                    DataGridView1.DataSource = bs
                End Using
            End Using
        End Sub
        Private Sub ButtonInsertUpdate_Click(sender As Object, e As EventArgs) Handles ButtonInsertUpdate.Click
            Dim FindIdx As Integer = bs.Find("CustName", TextBoxCustName.Text)
            If FindIdx >= 0 Then
                'You has this customer lets update him
                CType(bs(FindIdx), DataRowView)("CustInfos") = TextBoxInfos.Text
                bs.EndEdit()
            Else
                'You dont has this customer lets insert
                bs.AddNew()
                CType(bs.Current, DataRowView)("CustName") = TextBoxCustName.Text
                CType(bs.Current, DataRowView)("CustInfos") = TextBoxInfos.Text
                bs.EndEdit()
            End If
    
            Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                            Data Source=C:\DATA\Access\AccessDB.accdb;")
                Using DA As New OleDbDataAdapter("SELECT * FROM Customers", conn)
                    Dim cb As New OleDbCommandBuilder(DA)
                    cb.QuotePrefix = "["
                    cb.QuoteSuffix = "]"
                    DA.Update(dt)
                    dt.Rows.Clear()
                    DA.Fill(dt)
                End Using
            End Using
        End Sub
    End Class

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    28

    Re: Access Database field not getting updated, no error?

    Quote Originally Posted by Shaggy Hiker View Post
    You can do that faster, but that's not the issue, so I'll just mention it.

    The UpdateNonQuery method returns an integer, which is the number of rows affected. The first thing to do is to get that return and have a look at it. If it is returning 0, then you know that no rows match the criteria. I think you'd be pretty surprised by that, but you have to start somewhere. After all, what if it returned 1? In that case, then the DB IS being updated, so you'd have to ask why you aren't seeing it. Thus you see, whether it returns 0 or 1, you have a pretty good mystery either way, so that's the first thing to look at. You need to know what mystery you are really trying to solve.
    Shaggy Hiker, I got that returned number and that is 0. It means no row is being updated. Can you suggest what would be wrong with my update method? Anything wrong in my sql update query command or some other stuff? Because only sql update query command is different than insert method and insert method is perfectly working. Any help would be greatly appreciated

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Access Database field not getting updated, no error?

    Well, it looks fairly good, but there is one issue: OleDB suggests an Access database. If that is the case, then the problem may well be the fact that named parameters mean nothing to that database. It doesn't care what name you give to the parameters, it puts them into the query in the order that you supply them, without regard for the name. In that case, you are passing the parameters in the order Name, total1, total2, but the query uses them in the order total1, total2, Name.

    Try changing the order that you supply the parameters to the order in which the query would insert them. Ignore the parameter names. I always use ? for parameters in Access queries, because the name is meaningless. This would not be the case for other DB engines, such as SQL Server, but it is the case for Access, and possibly some others.
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    28

    Re: Access Database field not getting updated, no error?

    Shaggy Hiker, I changed the order as total1, total2 and name. Its working now. Thanx. BTW, I Wonder why naming parameters doesn't work as it suppose to be.

  7. #7
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Access Database field not getting updated, no error?

    Quote Originally Posted by Terrybogard911 View Post
    Shaggy Hiker, I changed the order as total1, total2 and name. Its working now. Thanx. BTW, I Wonder why naming parameters doesn't work as it suppose to be.
    OleDb/ms-access only has ordinal positioned parameters unlike SQL-Server which has named parameters. As Shaggy indicated he uses ? as the name does not matter.

    If you look at this code sample I used named parameters but note they are in the exact order of the Values in the INSERT. Also it returns the newly add records key.

    Code:
    Public Function AddNewRow( 
        ByVal pName As String, 
        ByVal pContact As String, 
        ByVal pContactTitle As String, 
        ByRef pIdentfier As Integer) As Boolean 
    
        Dim Success As Boolean = True 
    
        Try 
            Using cn As New OleDbConnection(Builder.ConnectionString) 
                Using cmd As New OleDbCommand With {.Connection = cn} 
                    cmd.CommandText = 
                        <SQL> 
                            INSERT INTO Customer  
                                ( 
                                    CompanyName, 
                                    ContactName, 
                                    ContactTitle 
                                )  
                            Values 
                                ( 
                                    @CompanyName, 
                                    @ContactName, 
                                    @ContactTitle 
                                ) 
                        </SQL>.Value 
    
                    cmd.Parameters.AddWithValue("@CompanyName", pName) 
                    cmd.Parameters.AddWithValue("@ContactName", pContact) 
                    cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle) 
    
                    cn.Open() 
    
                    cmd.ExecuteNonQuery() 
    
                    cmd.CommandText = "Select @@Identity" 
                    pIdentfier = CInt(cmd.ExecuteScalar) 
    
                End Using 
            End Using 
    
        Catch ex As Exception 
            mHasException = True 
            mLastException = ex 
            Success = False 
        End Try 
    
        Return Success 
    
    End Function

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Access Database field not getting updated, no error?

    Quote Originally Posted by Terrybogard911 View Post
    I Wonder why naming parameters doesn't work as it suppose to be.
    It is working as it's supposed to. Parameters names are for your reference only. They are supposed to be ignored by the database engine and they were.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width