vb.net sql update problem

hello

I have a vb.net app that needs to update a table from another table withing the database. The statement im having problems with is as follows:
"Update Transactions set Kcatagory = Category " & _
                            "from transactions T inner join Kkp K " & _
                            "on T.Kprefix = K.prefix and T.Knumb = k.suffix"

ITs a access database

I can get the statement to execute when I upsize the database  and run the database thru MSDE but I cannot get it to execute in vb.net and access?
stephenz43Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
Try this:

Imports System.Data.OleDb

...

Private Sub UpdateTransactions()

  Dim connectString As String = "..."
  Dim conn As New OleDbConnection(connectString)

  Dim queryString As String = Update Transactions set Kcatagory = Category " & _
                            "from transactions T inner join Kkp K " & _
                            "on T.Kprefix = K.prefix and T.Knumb = k.suffix"

  Dim cmd As New OleDbCommand(queryString, conn)

  cmd.ExecuteNonQuery()

End Sub


Bob
stephenz43Author Commented:
Still get the error "Syntax error(missing operator) in query expression 'Catagegory from transactions t inner join on t.prefix and t.numb = k.suffiix"

Same error I have been getting all morning
Bob LearnedCommented:
Try using the 'AS' keyword, and brackets:

            Dim queryString As String = Update Transactions set [Kcatagory] = [Category] " & _
                            "from transactions As T inner join Kkp As K " & _
                            "on T.Kprefix = K.prefix and T.Knumb = k.suffix"


Bob
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

stephenz43Author Commented:
same error ...... im starting to thing a limitation to access updating thru a join statement with ado.net. Like I said earlier, the original sqlstatement worked when using it with mdse
Éric MoreauSenior .Net ConsultantCommented:
Try another method:

            Dim queryString As String = Update Transactions set Kcatagory = (SELECT Category " & _
                            "from Kkp As K " & _
                            "on Transactions .Kprefix = K.prefix and Transactions .Knumb = k.suffix) "
stephenz43Author Commented:
I tried the update query and still get a systax error.....

im stumped
Éric MoreauSenior .Net ConsultantCommented:
Can you run this query directly in Access. This way we will get a more meaningful error message and you will also be sure that the query is working there.
Bob LearnedCommented:
Try this one:

            Dim queryString As String = _
                "UPDATE Transactions AS T" & _
                "   INNER JOIN Kkp As K " & _
                "      ON T.Kprefix = K.prefix and T.Knumb = k.suffix" & _
                "  SET [Kcatagory] = [Category]"

Bob


stephenz43Author Commented:
Learned One

Error = No Value given on or more required parameters
Bob LearnedCommented:
Should 'Kcatagory' be 'Kcategory'?

Bob
stephenz43Author Commented:
again the following works perfect using mdse with the exact same database that was upsized to sql

"Update Transactions set Kcatagory = Category " & _
                            "from transactions T inner join Kkp K " & _
                            "on T.Kprefix = K.prefix and T.Knumb = k.suffix"

works like a charm....

So the problem has to be something to do with oledb syntax or that a update cant fire with a join in oledb.....
Bob LearnedCommented:
Access syntax for INNER JOINS are different then MSDE.  It doesn't use the FROM keyword.

Bob
Éric MoreauSenior .Net ConsultantCommented:
have you tried your query directly into the Access IDE ?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stephenz43Author Commented:
the access ide claims the same syntax error in the select statement
Éric MoreauSenior .Net ConsultantCommented:
can you paste your query here ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.