0

I have this code. There is nothing wrong with this code as it can execute without error and get the result I want. However, I am wandering whether there is a way to check a query is executable without error before actually calling the "cmd.ExecuteNonQuery" and "transaction.commit()". 

    '....Database connection code....
    '....transacation start.....

    try
       '....Query string population.....
       'what I want to do... A check of query whether ExecuteNonQuery will success
       cmd.ExecuteNonQuery()

       'Anything goes wrong with ExecuteNonQuery, it will catch exception
       'transaction end and dispose database connection

    catch ex As exception
        'transaction rollback, end and dispose database connection
        Console.WriteLine("ERROR DETECTED : " & ex.Message)
    end try
Ping
  • 103
  • 2
  • 3
  • 17
  • The code may be *syntactically correct* but reference objects that do not exist. Do you want to catch those errors? The code may be correct and only refer to objects that exist but you may not have the correct permissions to access those objects. Do you want to catch those errors? The code may be correct, you may have the right permissions, but your query may be rolled back because its a deadlock victim or the network drops out or the administrator kicks you off - do you see where I'm going with this? You'll write a lot less code if you just keep the existing code and catch the error. – Damien_The_Unbeliever Dec 17 '14 at 08:04
  • Damien, I know that there are other problems that might cause a database operation to fail to commit. However, I am finding an alternative to the above problem. I do not want to stuck myself in just one option. – Ping Dec 17 '14 at 08:14
  • Being able to execute a query once does not mean you'll be able to execute it a second time. – the_lotus Dec 17 '14 at 15:16
  • Even if you could do something like that, it won't be reliable e.g. when you validate the query it says it will run, but right after that another process modifies the data and when you run the query throws an error... Maybe it could be possible but I don't think it's worth the effort. – Josh Part Dec 17 '14 at 18:55
  • Well, I do think that it's better than not checking it. – Ping Dec 18 '14 at 01:12

0 Answers0