How to get the SQL code generate and return code

Question

I am executing a Non query SQL. I will like to know where we go in Visual studio 2013 to see the SQL generated and the error code.

    string cmdText = "UPDATE RendezVous SET  
          nomClientEssai=@nomClientEssai,prenomClientEssai=@prenomClientEssai,
          IsAvailable=@IsAvailable WHERE rdvId=@rdvId";

     SqlCommand cmd = new SqlCommand(cmdText, con);

    cmd.Parameters.AddWithValue("@nomClientEssai", Convert.ToString(Session["nom"]));
    cmd.Parameters.AddWithValue("@prenomClientEssai", Convert.ToString(Session["prenom"]));
    cmd.Parameters.AddWithValue("@IsAvailable", "False");

    cmd.Parameters.AddWithValue("@rdvId", cleRdvId);

    if (con.State == ConnectionState.Closed)
    {
        con.Open();
    }

    cmd.ExecuteNonQuery();    <===  I want to see the sql return code here !!!
    con.Close();

This SQL is not updating my table and I want to know why and know how I can verify the SQL statement and also the SQL return code. I don't have any error generated but nothing is getting updated.

If I understand you correctly, you are trying to get the final SQL command that is sent to the SQL Server. Is this what you're looking for? — Katie Kilian
– Katie Kilian, Commented Jan 13, 2014 at 22:40
since I don't have an error from the execution, I need to verify which SQL was generated and the return code. Maybe it will give me a hint of what is not working properly here. Can you explain me how to check the SQL created and where to find the SQL return code. If you have any link or example, it will be very appreciated. Thanks — user3127986
– user3127986, Commented Jan 13, 2014 at 22:42
The final SQL command that is sent to the SQL Server is cmdText. Parameters make it to the SQL server intact. — Alon Gubkin
– Alon Gubkin, Commented Jan 13, 2014 at 22:42
Maybe simply rdvId=@rdvId matches nothing, so nothing is updated. Can you verify this is correct? — MikeSmithDev
– MikeSmithDev, Commented Jan 13, 2014 at 22:43
I checked the rvdId and it is equal to 14 which is the row I wanted to update. — user3127986
– user3127986, Commented Jan 13, 2014 at 22:44

sh1rts · Accepted Answer · 2014-01-13 22:47:26Z

2

If you want to see what's hitting the database, use SQL Profiler

answered Jan 13, 2014 at 22:47

sh1rts

1,8841 gold badge13 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shiva · Accepted Answer · 2014-01-13 22:41:45Z

1

ExecuteNonQuery returns the number of rows affected.

So update your code as follows and see what is in numberOfRecordsUpdated for your UPDATE statement.

int numberOfRecordsUpdated = comm.ExecuteNonQuery();

answered Jan 13, 2014 at 22:41

Shiva

21.1k15 gold badges88 silver badges120 bronze badges

Comments

Cosmin Vană · Accepted Answer · 2014-01-13 22:46:03Z

1

To get information about errors or simple output from the database (anything with severity below or equal to 10 from sql server) you can subscribe to events of the SqlConnection. This is a simple example:

con.InfoMessage += new SqlInfoMessageEventHandler((sender, a) => { Console.WriteLine(a.Message);});

answered Jan 13, 2014 at 22:46

Cosmin Vană

1,62214 silver badges28 bronze badges

Comments

D Stanley · Accepted Answer · 2014-01-13 22:48:00Z

0

UPDATE does not have a return code. As mentioned ExecuteNonQuery returns the number of records affected but it does not tell you why that number of records is affected.

I would run it in the debugger to verify the proper value of cleRdvId is being used.

answered Jan 13, 2014 at 22:48

D Stanley

153k12 gold badges189 silver badges257 bronze badges

1 Comment

user3127986 Over a year ago

the cleRdvId is set to 14 which correspond to the exact row in the table. The numberOfRecordsUpdated = 1. The table has been updated !! but the Session[clientName] seems to be empty. I will investigate why.

Collectives™ on Stack Overflow

How to get the SQL code generate and return code

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related