Update Access DB with Passed in values using VB.NET

[kckrns]

Hello all,

I am currently working on a project that requires an update within an Access Database. There are photo folders, and the user defines the path name via a dialog box. That path name is stored to a string value, and through OleDBReader, the actual name of the photo (from another column in the database) is stored to another string value. Anyway, I have tried a number of methods to update, and nothing is working, My try catch keeps getting a weird error, something to do with a missing operater in query expression. Hope someone can help!! Thanks. here is my code from the OleDBReader on, and the procedure designed to do the update. (note that TargetID represents the primary key of the access DB.. this is why I pass that value in because I can use the Rows.Find(PK) method.. When I use a simpler updating method, I get an error saying I do not have a valid update command). OK Here is the code:

.....
        Dim photoSQL As String = "SELECT * FROM PotentialTarget"
        Dim oleDbCommand As OleDbCommand = New OleDbCommand(photoSQL, tableConn)
        Dim oleDbDataReader As OleDbDataReader = oleDbCommand.ExecuteReader()

        da = New OleDbDataAdapter(photoSQL, tableConn)
        da.Fill(rs, "Photos")

        Dim ctr As Integer = 0
        Dim photoID As String
        Dim targetID As String

        Do While oleDbDataReader.Read()
            photoID = rs.Tables("Photos").Rows(ctr).Item("RowColumn").ToString()
            targetID = rs.Tables("Photos").Rows(ctr).Item("TargetID").ToString()
            Call UpdatePerspective(photoID, ctr, targetID)
            ctr = ctr + 1
        Loop

        MsgBox("Perspective Photo information updated in the Database")
        tableConn.Close()
    End Sub

    Public Sub UpdatePerspective(ByVal photo As String, ByVal count As Integer, ByVal target As String)

        Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & dbLoc
        Dim conn As New OleDbConnection(ConnectionString)
        conn.Open()
        Dim ds As New DataSet()
        Dim update As String
        update = "SELECT * FROM PotentialTarget"
        
        Try            
            
            Dim adapter As New OleDbDataAdapter(update, conn)
            Dim cmdBuilder As New OleDbCommandBuilder(adapter)
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
            adapter.Fill(ds, "Photos")            
            row As DataRow = ds.Tables("Photos").Rows.Find(target)             
            row("PerspectivePhoto") = (persPhoto & "\" & photo & ".jpg")
            adapter.Update(ds, "Photos")
            ds.AcceptChanges()
        Catch exp As OleDbException
            MessageBox.Show(exp.Message.ToString())
        End Try

        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
    End Sub

[kckrns]

I got it!

Turns out the database had some relationships that were getting in the way.. instead of Selecting all, I had to modify my query to work around that aspect..

SELECT TargetID, PerspectivePhoto FROM PotentialTarget"

Thanks for reading

[finito]

update = "SELECT * FROM PotentialTarget"

??

Update looks like this.

UPDATE Table Set Column = value Where Condition.

[kckrns]

yes I just used update as a generic string term, perhaps update wasn't the best word to use, I really didn't even need to dim that to define my select statement.

Thanks !

[Niranjan003]

how to update one mdb column values based on another mdb column values in Msaccess

[Niranjan003]

how to update one mdb table column values based on another mdb table column values in Msaccess