Help Using an update query

[egstatus]

Hi everyone,
I have the following query

UPDATE PAYTEST SET PAYTEST.ImageName = "C:\Client\AppName\Images\" & [imagename] & " *.TIF";

This query works fine from access query designer.

Now I want to use in vb.net and I can't seem to get it to work.

[highlight #EF2929]newPath contains the new path of the images[/highlight]

 Public Shared Function UpdateImagePathMSAccess(ByVal newPath As String)

        Try
            Dim connection As New OleDbConnection(TimesheetsDB.GetConnection)
            connection.Open()
            Dim strQuery As String = _
            "Update PayTest Set Imagename = @newPath"

            Dim cmd As New OleDbCommand(strQuery, connection)
            cmd.Parameters.AddWithValue("@newPath", newPath [highlight #EDD400]& "\& [imagename] & "".TIF"" ")[/highlight]
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error updating image path")
        End Try

    End Function

I know what my problem is, I just don't know how to fix it. I highlighted the error in yellow.

How do I incorporate the [imagename] field in to the update query?

Thanks

Ed

 

[Andrzejek]

Try:

cmd.Parameters.AddWithValue("@newPath", newPath & "\" & [imagename] & ".TIF")

Have fun.

---- Andy

 

[egstatus]

Hi Andy,
I tryed that and I get the following Error: Name 'imagename' is not declared.
It happens at:

cmd.Parameters.AddWithValue("@newPath", newPath & "\" & [highlight #EF2929][imagename][/highlight] & ".TIF")

[imagename] is a field in my table.

The program won't compile.

Any Idea?

Thanks

Ed

 

[Andrzejek]

Then retreive this information from your table, something like:
"Select imagename from MyTable Where ...", or DlookUp, or something...

Have fun.

---- Andy

 

[MajP]

I do not think that is a valid update command without a where clause that references the primary key, unless you really want each record to get the same value. I assume you have a primary key? The update command needs to know which record to update.

http://msdn.microsoft.com/en-us/library/33y2221y(v=vs.71).aspx

Also do not think there is a way to pass a field value as part of the parameter, but maybe.

I would think the easy way do do this would be to simply update the datacolumn of the dataset by looping the rows of the datatable

http://msdn.microsoft.com/en-us/library/tat996zc(v=vs.80).aspx

or add a datacolumn to your dataset based on a expression.

http://msdn.microsoft.com/en-us/library/3df0c8ey(v=vs.71).aspx

Then simply update the database off the updated datacolumn or the calculated datacolumn.

Also AFAIK there is no dlookup in vb.net, and not sure what is actually being suggested there by Andy.

 

[egstatus]

Thanks MajP,
I thought about reading all the records via a select statement, then using a loop and passing the imagename to the update query as a parameter. [Which I believe is what you sugested]

Though this to me does not look like an efficient way of accomplishing this. And yes, you can pass a field vaue as a parameter.

Another alternative that I am working on right now is to run the access query from vb.net using an OledbCommand and passing the query name to be executed. I know this is possible.

I have the function to be executed, I just need to finish up few thing and will try to test it.

 Public Shared Function RunAccessQuery() As Int32
        Dim intRecords As Int32
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Try
            con = New OleDbConnection(TimesheetsDB.GetConnection)
            cmd = New OleDbCommand("quImagePath", con)
            cmd.CommandType = CommandType.StoredProcedure
            con.Open()
            intRecords = cmd.ExecuteNonQuery()
            con.Close()
            Return intRecords
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error running access query")
        End Try

    End Function

Thanks for the tips. I really apreciate. I will let everyone know if it works.

Ed

 

[MajP]

I thought about reading all the records via a select statement, then using a loop and passing the imagename to the update query as a parameter. [Which I believe is what you sugested]

Not really. I would populate a datatable with the records that need to be updated using a select statement. Then I could simply add a calculated field to the datatable to change the path, or instead of adding a column to the datable I would simply update the rows in the datatable. Then simply update the database with the updated values in the datatable. I imagine that would be pretty efficient.
I am definately not suggesting to then use a "loop and passing the imagename to the update query as a parameter."

 

[egstatus]

Hi MajP, thanks for the clarification, I apoligize for mis-interpreting your sugestions.

I like your Idea, just couple of questions for you.
First, how do you add a calculated field to a datable?
Second, how do you save the datatable back to your database?

I am sorry but I don't have a lot of experience with them, I've read about them. I have an idea as how to populate them.

Thanks again.

Ed

 

[MajP]

Here is the general idea. I am succesful in filling my datatable and the new calculated field. However, not sure what I am
doing wrong on my update command of the dataadapter. It runs without error, but my values are not updated back in the db.

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        'I am using a strong typed dataset, but you can use a non-typed
        dim ds As PicPathDataSet
        dim dt As PicPathDataSet.tblImagesDataTable
        dim da As OleDb.OleDbDataAdapter
        Dim conn As New OleDb.OleDbConnection
        Dim strconn As String
        Dim strSql As String
        Dim comm As OleDb.OleDbCommand
        
        
        strSql = "SELECT ID, [Image] FROM tblImages"
        strconn = My.Settings.PicPathConnectionString

        'create a connection with a connection string
        conn = New OleDb.OleDbConnection(strconn)

        ds = New PicPathDataSet
        dt = ds.tblImages

        'Add a column to the datatable. This column uses an expression
        dt.Columns.Add("NewPath", Type.GetType("System.String"), "'C:\newpath\' + Image")
        
        'Use the datadapter to fill the datatable
        da = New OleDb.OleDbDataAdapter(strSql, conn)  
        
        'You have to then make an update command for the dataadapter
        
        comm = New OleDb.OleDbCommand()
        comm.CommandText = "UPDATE tblImages SET [Image] = ? WHERE ID = ?"
        'Add your parameters to the update command
        comm.Parameters.Add("@NewImage", System.Data.OleDb.OleDbType.VarWChar, 250, "NewPath")
        comm.Parameters.Add("@ID", System.Data.OleDb.OleDbType.[Integer], 0, "ID")
        'Update the datadapter with the update command
        da.UpdateCommand = comm
        'Fill your table
        da.Fill(dt)
        'use the datadapter to update the database
        da.Update(dt)
    End Sub

 

[MajP]

This way works by enumerating the datarows

        Dim conn As New OleDb.OleDbConnection
        Dim strconn As String
        Dim strSql As String
        Dim comm As OleDb.OleDbCommand
        Dim row As DataRow
        strSql = "SELECT ID, [Image] FROM tblImages"
        strconn = My.Settings.PicPathConnectionString
        conn = New OleDb.OleDbConnection(strconn)

        ds = New PicPathDataSet
        dt = ds.tblImages
      
        da = New OleDb.OleDbDataAdapter(strSql, conn)
        comm = New OleDb.OleDbCommand()
        comm.CommandText = "UPDATE tblImages SET [Image] = ? WHERE ID = ?"
        comm.Connection = conn
        comm.Parameters.Add("@Image", System.Data.OleDb.OleDbType.VarWChar, 250, "Image")
        comm.Parameters.Add("@ID", System.Data.OleDb.OleDbType.[Integer], 8, "ID")
        da.UpdateCommand = comm

        da.Fill(dt)
        
        'Update the values in the datarows
        For Each row In dt.Rows
            row.Item("Image") = "c:\newPath" & row.Item("Image")
        Next

        da.Update(dt)

 

[MajP]

Sorry sent you down the wrong path. I am so used to working with an active dataset. So this works for me.

Public Shared Function UpdateImagePathMSAccess(ByVal newPath As String)
        Try
            Dim connection As New OleDbConnection(TimesheetsDB.GetConnection)
            connection.Open()
            Dim strQuery As String = "Update PayTest Set Imagename = '" & newpath & "' & [image]"
            Dim cmd As New OleDbCommand(strQuery, connection)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error updating image path")
        End Try
End Function

 

[egstatus]

Hi MajP,
I couldn't get any of the code samples you provided me with to update the access database.
I get the following error:
"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

Yes it does update it in the datatable. I did some research on why this happens and this is what I found.

[link]

http://social.msdn.microsoft.com/Fo.../thread/666835a2-50ec-415f-ba30-bb80ca51c91b/

[/url]

I tryed some of the solutions offered but none of them work. I will keep researching till I solve this.

I thank you for all the great help.

 

[MajP]

The last example should not give you that error because that uses an executenonquery without a dataadapter. If you use a dataadapter that you generate in code then you have to define the fill, update, delete, and insert commands before you attempt to use it to update the database. Can you provide the code that does not work. The simplest solution is basically the one you started with using the executenonquery, but you do not need to define parameters as I show.

 

[egstatus]

MajP,
I was using the second example.
I have switched to the last one and that works.

Thanks again.