locked
Updating a table RRS feed

  • Question

  • I have been trying every way I can think of, and many of the ways got very weird, to replace a table value with a different value.  This table consists of a single column, single row that holds a part number (the column is vchar).  All I want to do is open the connection, retrieve the value (already got that part done), then later replace that value with an updated value (this is where the problem occurs).

    Anyway, I have opened the connection, filled the table and tried various methods of replacing the value in the table, all of which failed.  Can anyone tell me what I should be doing to make this work? 

    Below is one of the many, many, many methods I have tried to get this to work.  Everything appears to work, but the column value is NOT updated.

            Public Function IncrementFileMasterID(ByVal IncrementID As String) As Object
                Dim strFileID As String = Nothing
                'Increment ID number and Save to the FileMaster ID table
                strFileID = CStr(_strFileMasterID)
                Dim intFileID As Integer = CInt(Mid(strFileID, 3, 9))
                intFileID += 1
                strFileID = "FM" + CStr(intFileID)
                'Open Connection and establish Command object
                FileIDConn.MasterBaseOpen()
                Dim FileIDTable As DataTable = New DataTable
                FileIDCommand = New SqlCommand("Select chrFileMasterID From setFileMasterID", FileIDConn.MasterBaseConnection)
                FileIDAdapter = New SqlDataAdapter()
                FileIDAdapter.SelectCommand = FileIDCommand
                Dim FileIDBuild As SqlCommandBuilder = New SqlCommandBuilder(FileIDAdapter)
                FileIDAdapter.Fill(FileIDTable)
                FileIDBindingSource.DataSource = FileIDTable
                Dim FileIDDataRow As DataRow = FileIDTable.NewRow()
                FileIDDataRow("") = CStr(strFileID)
                FileIDTable.Rows.Add(FileIDDataRow)
                FileIDAdapter.Update(FileIDTable)
                FileIDConn.MasterBaseClose()
                Return IncrementID
            End Function


    gwboolean

    Saturday, April 13, 2019 3:41 PM

Answers

  • Hi,
    check your database table (setFileMasterID). If you have no primary key the DataAdapter cannot identify the row for updating (the CommandBuilder will not generate commands).


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Marked as answer by gwboolean Tuesday, April 16, 2019 3:58 PM
    Saturday, April 13, 2019 4:25 PM

All replies

  • Hi,
    check your database table (setFileMasterID). If you have no primary key the DataAdapter cannot identify the row for updating (the CommandBuilder will not generate commands).


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Marked as answer by gwboolean Tuesday, April 16, 2019 3:58 PM
    Saturday, April 13, 2019 4:25 PM
  • You don't have an update command same as you did for Select. The method DataAdapter.Update simply "Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable named "Table.""

    Here is an example from msdn:

    Public Shared Function CreateCustomerAdapter(ByVal connection As SqlConnection) As SqlDataAdapter
        Dim adapter As SqlDataAdapter = New SqlDataAdapter()
        Dim command As SqlCommand = New SqlCommand("SELECT * FROM Customers " & "WHERE Country = @Country AND City = @City", connection)
        command.Parameters.Add("@Country", SqlDbType.NVarChar, 15)
        command.Parameters.Add("@City", SqlDbType.NVarChar, 15)
        adapter.SelectCommand = command
        command = New SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " & "VALUES (@CustomerID, @CompanyName)", connection)
        command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
        command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")
        adapter.InsertCommand = command
        command = New SqlCommand("UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " & "WHERE CustomerID = @oldCustomerID", connection)
        command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
        command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")
        Dim parameter As SqlParameter = command.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID")
        parameter.SourceVersion = DataRowVersion.Original
        adapter.UpdateCommand = command
        command = New SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", connection)
        parameter = command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
        parameter.SourceVersion = DataRowVersion.Original
        adapter.DeleteCommand = command
        Return adapter
    End Function


    Fouad Roumieh

    Saturday, April 13, 2019 4:30 PM
  • Hello,

    In the code below will not match exactly what you showed for several reasons.

    • I'm showing a different path.
    • The code uses less code and is lighter weight for what you are after

    Notes:

    • I did not use a parameter for the Command as in this case it's overkill and some may challenge this, so be it.
    • You are returning an Object, less than one percent of the time this is okay, the remaining times in short slows things down and can (but not here) make code more difficult to maintain.

    Got questions?

        Public Function IncrementFileMasterID(incrementId As String) As Integer
            Dim idValue As Integer
            Using cn As New OleDbConnection(ConnectionString)
                Using cmd As New OleDbCommand With {.Connection = cn}
                    Dim selectStatement = "SELECT chrFileMasterID FROM setFileMasterID"
                    cmd.CommandText = selectStatement
                    cn.Open()
                    ' get the value (assuming integer, if not cast as needed then increment)
                    idValue = CInt(cmd.ExecuteScalar()) + 1
    
                    ' usually would use a command parameter but in this case we are good.
                    Dim insertStatement = $"INSERT INTO setFileMasterID VALUES ({idValue})"
                    cmd.CommandText = insertStatement
                    cmd.ExecuteNonQuery()
                End Using
            End Using
    
            Return idValue
    
        End Function


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, April 13, 2019 6:25 PM
  • It took me awhile to get back on this (my leg has crapped out on me and I am not currently getting anything done very quickly).  Anyway, This is where I am at so far.

    I checked back at the table and the field (column) using SSMS and found that no primary key was set, as suggested by Peter.  I made the correction then set the code as below.  Additionally, I changed it from a function to a method, since I do not need to return anything for this.  Anyway, this works exactly as needed.

            Public Sub IncrementFileMasterID()
                Dim strFileID As String = Nothing
                'Increment ID number and assign to control.
                strFileID = CStr(_strFileMasterID)
                Dim intFileID As Integer = CInt(Mid(strFileID, 3, 9))
                intFileID += 1
                strFileID = "FM" + CStr(intFileID)
    
                'Open Connection and establish Command object
                FileIDConn.MasterBaseOpen()
                Dim FileIDTable As DataTable = New DataTable
                FileIDCommand = New SqlCommand("Select chrFileMasterID From setFileMasterID", FileIDConn.MasterBaseConnection)
                FileIDAdapter = New SqlDataAdapter()
                FileIDAdapter.SelectCommand = FileIDCommand
                Dim FileIDBuild As SqlCommandBuilder = New SqlCommandBuilder(FileIDAdapter)
                FileIDAdapter.Fill(FileIDTable)
                'Replace FileID value in table
                FileIDBindingSource.DataSource = FileIDTable
                FileIDTable.Rows(0).Item(0) = CStr(strFileID)
                frmFileMaster.FileMasterIDBindingSource.EndEdit()
                FileIDAdapter.Update(FileIDTable)
                FileIDConn.MasterBaseClose()
            End Sub

    Fouad and Karen,

    I am very interested in what the two of you have suggested, but I will have to study the hell out of what you did to understand how it all works, so I will be spending some time on the two approaches.

    Thanks all.


    gwboolean


    • Edited by gwboolean Tuesday, April 16, 2019 4:00 PM
    Tuesday, April 16, 2019 3:58 PM