How To Check if update statement is executed successfully (qry.ExecuteScalar)

[mayhem_rules]

Hi, I am using the following code to update records in an access table using vb.net. Dim dr As OleDbDataReader Dim qry As New OleDbCommand Dim ucon As New OleDbConnection Dim inssql As String inssql = "Update PriceTable set Price = " & newprice & " where ID = '" & id1 & "' and Period = '" & Trim(period) & "'" qry = New OleDbCommand(inssql, ucon) dr = qry.ExecuteScalar What I want to check is if the query has been executed correctly. i.e. if id1 (id1 matches with ID from PriceTable) exists in PriceTable, then it will be executed successfully. However, if the id1 is not present in PriceTable (id1 does not match with ID in PriceTable), then how do I know that the qry has not been executed. Please provide assistance ASAP. With Best Regards, Mayur

[Owner drawn]

mayhem_rules wrote:

qry.ExecuteScalar

shouldn't this be ExecuteNonQuery()... This is the statement that does Update, Insert and Delete. It returns the number of rows affected.

Jesus Loves:rose:

--Owner Drawn:rose: --Nothing special --Defeat is temporary but surrender is permanent --Never say quits --Jesus is Lord:rose:

[Muhammad Javed Khan]

ExecuteNonQuery it returns no of rows effected by the query. use this: Dim dr As OleDbDataReader Dim qry As New OleDbCommand Dim ucon As New OleDbConnection Dim inssql As String dim iNoofRows as interger inssql = "Update PriceTable set Price = " & newprice & " where ID = '" & id1 & "' and Period = '" & Trim(period) & "'" qry = New OleDbCommand(inssql, ucon) **iNoofRows = qry.ExecuteNonQuery 'Instead of qry.ExecuteScalar**

[mayhem_rules]

Thnx guys for your reply. I will try out your solution. However, what I need to know is whether every execute statement is successfully completed. If not, then I need to maintain a log of the records (ID's, Price) which have not been executed successfully for the user to analyze. How can this be done. Thnx again. With Best Regards, Mayur -- modified at 1:43 Friday 20th January, 2006

[Owner drawn]

mayhem_rules wrote:

However, what I need to know is whether every execute statement is successfully completed.

That is what ExecuteNonQuery returns. It returns the number of rows affected by the said query. So if it returns 0 it means no rows affected.

Jesus Loves:rose:

--Owner Drawn:rose: --Nothing special --Defeat is temporary but surrender is permanent --Never say quits --Jesus is Lord:rose:

[mayhem_rules]

Thnx, I will try it out and let you know. Another query that I have is how do I maintain a log file to record all the transactions. With Best Regards, Mayur

[Owner drawn]

I suggest you write a custom class for that. Now use that class to execute all sorts of query in your application. You must have a function that acts as a common entry point for executing these queries. Probably an enum will help you determining the type of query. Now in this function you can make log entries for all kinds of queries since you are now sure that nothing in your application can interact with database without using this function. So it acts as a common entry point.

Jesus Loves:rose:

--Owner Drawn:rose: --Nothing special --Defeat is temporary but surrender is permanent --Never say quits --Jesus is Lord:rose:

[mayhem_rules]

Thnx. But I am a real fresher in .NET. The instructions that you have provided have gone way above my head:confused:. I wil be glad if you could explain it to me in some simpler terms. With Best Regards, Mayur

[Colin Angus Mackay]

mayhem_rules wrote:

inssql = "Update PriceTable set Price = " & newprice & " where ID = '" & id1 & "' and Period = '" & Trim(period) & "'"

Please learn about SQL Injection Attacks and tips on how to prevent them[^] otherwise this code is going to get you 0wn3d!!!!111 ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

[mayhem_rules]

Sorry, did not get you. Neway, I m using MS Access. some usefull tips will be helpful. With Best Regards, Mayur

[Colin Angus Mackay]

mayhem_rules wrote:

Sorry, did not get you.

Your code is succeptable to a SQL Injection Attack. A mallicious attacker could gain control of your database through your web application if you don't defend it. The article I linked to will explain what a SQL Injection Attack is and it will give some advice on how to defend yourself from it.

mayhem_rules wrote:

Neway, I m using MS Access

Dismissive attitudes like this cause security problems. Regardless of the database platform you are using you need to at least understand what security threats exist in order for you to put together even a basic plan of defence. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

[Dave Kreskowiak]

This can be done is the database's SQL code. Table triggers are commonly used to log information such as this. Though, this technique is not supported in Access! Real SQL databases only! RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome