Help with update query in vb.net

Question

4.00/5 (1 vote)

See more:

Hi everyone I have the following query, this query works fine in the query builder in MS Access:

VB

UPDATE PAYTEST SET PAYTEST.ImageName = "C:\Ezc\Files\" & [bates] & " *.TIF";

Howerver when I try to execute in from vb.net, It does not work.

Here is my vb.net code:

VB

Public Shared Function UpdateImagePathSQL(ByVal newPath As String) As Integer
       Dim cn As OleDbConnection
       Dim intNumberOfRecordsUpdated As Integer
       Try
           cn = New OleDbConnection
           cn.Open()
           Dim selectStatement As String
           selectStatement = _
                "UPDATE Paytest SET Imagename = '" & newPath & "'" & "\[bates].TIF"

           Dim cmd = New OleDbCommand(selectStatement, cn)
           intNumberOfRecordsUpdated = cmd.ExecuteNonQuery()
           cn.Close()
           MessageBox.Show("Records updated sucessfully", "Timesheet imagepath update")
           Return intNumberOfRecordsUpdated
       Catch ex As Exception
           MessageBox.Show(ex.Message, "Error updating image path")
           cn.Close()
       End Try

[bates] is a field in my access table that contains the image name without the extension.

What Am I doing wrong?

Thanks

Ed

Posted 10-Feb-13 4:09am

elchalateco

Add a Solution

Comments

bbirajdar 11-Feb-13 0:07am

I can see that you have not set up the connection string..The path to access database

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Muthuraja Irullandi · Answer 1 · 2013-02-10T05:04:00

Solution 1

Hi,
I think, you problem with the update query, check the below.

VB

selectStatement = _
                 "UPDATE Paytest SET Imagename = '" & newPath & "\[bates].TIF'"

Best Regards
Muthuraja

Posted 10-Feb-13 5:04am

Muthuraja Irullandi

Comments

bbirajdar 10-Feb-13 11:47am

Nice catch .Missing the apostrophe.... +5.. And that too for a vague question which just says -'does not work'...

elchalateco 10-Feb-13 13:34pm

I am sorry if the question is 'vague' however the solution provided did not work. [bates] is not a variable is the name of a field in the access table.

Thanks

bbirajdar 11-Feb-13 0:06am

Same again...You need to provide the exact error message so that we can help you. 'Does not work' does not help us in anyway to understand your problem...

elchalateco 11-Feb-13 10:06am

Thanks for the input. I will try to be more specific when posting new questions.

Thomas Daniels · Answer 2 · 2013-02-10T05:09:00

Solution 2

Hi,

This query works fine for you in the query builder in MS Access:

SQL

UPDATE PAYTEST SET PAYTEST.ImageName = "C:\Ezc\Files\" & [bates] & " *.TIF";

And this is selectStatement:

VB

Dim selectStatement As String
           selectStatement = _
                "UPDATE Paytest SET Imagename = '" & newPath & "'" & "\[bates].TIF"

In selectStatement, change Imagename into PAYTEST.Imagename, and Paytest into PAYTEST.
Also, you need to put the second apostrophe AFTER the file path:

VB

Dim selectStatement As String
            selectStatement = _
                 "UPDATE PAYTEST SET PAYTEST.Imagename = '" & newPath & "\[bates].TIF'"

Hope this helps.

Posted 10-Feb-13 5:09am

Thomas Daniels

Updated 10-Feb-13 7:34am

v4

Comments

elchalateco 10-Feb-13 12:12pm

Hi ProgramFox,
Unfortunatelly it did not work, I tryed to re-post the question however I realized that I posted a solution. Please see what I posted.

Thomas Daniels 10-Feb-13 12:14pm

I tryed to re-post the questin however I realized that I posted a solution
You can improve your question using the "Improve question button".
I will update my answer.

Thomas Daniels 10-Feb-13 12:15pm

I updated my answer.

elchalateco 10-Feb-13 13:05pm

Hi ProgramFOX.
Still No luck.

When I i run the query I get "C:\images\[bates].tif" in the imagename field when I should see:
"C:\images\img0001.tif".
'img0001' is the value stored in the bates field in the access table.

And obviously this value is different for every record in the table

Thomas Daniels 10-Feb-13 13:06pm

Change [bates] into the name of your image.

elchalateco 10-Feb-13 13:30pm

Please understand tha 'bates' is a field in the database. It contains a different value for each record in the table. I can't hard code the value of it.

I want to update the imagename field to the value of newPath + "\ + bates + ".TIF"

Thanks for trying to help me.

Ed

Thomas Daniels 10-Feb-13 13:34pm

I'm trying to update my answer.

elchalateco 10-Feb-13 13:44pm

I had already tryed that, the program won't compile, I get an error:
"Name bates is not declared". The error point to bates in the selectStatement line.

Would there be another way of updating this field, that I am not aware of?

Thanks

Thomas Daniels 10-Feb-13 13:46pm

You can try

<small>Dim selectStatement As String
            selectStatement = _
                 "UPDATE PAYTEST SET PAYTEST.Imagename = '" & newPath & "\' + PAYTEST.bates + '.TIF'"</small>

But I don't think it will work.

elchalateco 10-Feb-13 13:53pm

Tryed before, same error "PAYTES.bates" name is not declared.

The other way I can think of is, first run a query that return all the records, then run an update query in a loop passing the bates to the update query as a parameter. Though I don't think this would be an efficient way of doing it. I have about 500K records to update.

What do you think?

Thomas Daniels 10-Feb-13 13:56pm

Tryed before, same error "PAYTES.bates" name is not declared.
You can also try [PAYTEST.bates] instead of PAYTEST.bates
The other way I can think of is, first run a query that return all the records, then run an update query in a loop passing the bates to the update query as a parameter. Though I don't think this would be an efficient way of doing it. I have about 500K records to update. What do you think?
I've also been thinking about that. But I think that's not an efficient way.

elchalateco 10-Feb-13 14:03pm

Same error as before.
I'm lost. I don't know what else to try.

Thanks

elchalateco 10-Feb-13 15:04pm

Hi ProgramFox. I solved the issue by running the access query from vb.
witht the following function.

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 again.

elchalateco · Answer 3 · 2013-02-10T05:58:00

I tryed both sugestion and unfortunatelly none of them work.
I get an exception:

VB

System.Data.OleDb.OleDbException was caught
  ErrorCode=-2147217904
  Message="No value given for one or more required parameters."
  Source="Microsoft JET Database Engine"

For example if bates contains "IMG0002" and the value of new path is "C:\ImgPath\" The final result of ImageName should be "C:\ImgPath\IMG0002.TIF" however when I run the query the result is "C:\ImgPath\[bates].TIF" which is wrong because [bates] is a field in my MS Access table.

Any other sugestions?

Thanks very much.

Ed

CHill60 · Answer 4 · 2013-02-10T09:15:00

Solution 4

Step through the program in your debugger and stop it immediately after the assignment to selectStatement. Display selectStatement in the watch window or Immediate window.

You want it to look exactly like this ...

UPDATE Paytest SET Imagename = 'C:\Ezc\Files\' + [bates] + '.TIF'

Which I expected ProgramFOXs statement suggestion would have achieved ...

VB

selectStatement = "UPDATE Paytest SET Imagename = '" + newPath + "' + [bates] + '.TIF'"

NB this is assuming newPath = @"C:\Ezc\Files\"

Posted 10-Feb-13 9:15am

CHill60

Updated 10-Feb-13 9:17am

v2

Comments

elchalateco 11-Feb-13 10:31am

Hi Clill60,
Even though I workedout a solution, it doesn't seem fast to me.

When I use: UPDATE Paytest SET Imagename = 'C:\ezc\files\' + [bates] + '.TIF'
This line won't compile at all, VB complains with the following error:
Name [bates] is not declared.

[bates] is a field in my access database, however VB does not know that, I believe VB thinks this should be a variable declared somewhere.

This query when run from MS access query builder works fine:
UPDATE PAYTEST SET PAYTEST.ImageName = "C:\Ezc\Files\" & [Bates] & ".TIF";
Access some how knows that [bates] is a field within the database.

I need the user to be able to select a path where the images are going to reside in the user's hard drive and perform the update from vg program.

I apreciate your help.

Thanks

Ed.

CHill60 11-Feb-13 10:51am

In the VB code I supplied [bates] would be enclosed within the double quotes so VB does not throw an error. "UPDATE Paytest SET Imagename = 'C:\Ezc\Files\' + [bates] + '.TIF'" is the contents of the variable selectStatement, not what you type into VB.

elchalateco · Answer 5 · 2013-02-10T09:18:00

I run the update query stored in msaccess from vb.net
Using the following fucntion.

VB

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

Help with update query in vb.net

5 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0